CODEWITHSUNDEEP
excelexcel-formulavba
Alper
Alper

Reputation: 1105

Sync all excel cells values from other worksheets with reference (a cell value)

I have a full products list in Excel and this list has information about our products (name, SKU, category, stock amounts, etc... ) and I have 4 separate Excel files that have SKUs and Stock Amounts (Why 4 different?: Stocks are counting from different departments.)

What I want? : To combine all counted stocks amounts in a final file.

Suppose there is a structure like below.

Final File (final-stock-count.xls), stock-count1.xls file, stock-count2.xls file, stock-count3.xls file, stock-count4.xls file:

A Column = SKUs
B Column = Product Names
C Column = Stock Amounts

NOTE: I can put all these files into one file as WorkSheets

I want that, Excel looks at the SKU cell for each list (A2 cell of stock-count1 WorkSheet) and fill Stock Amounts cell for final list (C2 cell of final-stock-count WorkSheet) if SKU values are same and so on. Because I don't know the exact cell references (order of cells A2, C2, ...) it should done via formula or another way.

enter image description here

NOTE: I'm not familiar with VBA but if you simply explain to where should I edit, I can do it.

Upvotes: 3

Views: 1111

Answers (2)

Scott Holtzman
Scott Holtzman

Reputation: 27269

There is no need for a cumbersome VLOOKUP embedded into an IFERROR statement here, when a SUMIF will work just fine as it will ignore any sheets that do not contain the SKU. (It also has the advantage of adding any stock that may be in multiple sheets - if that is even the case).

This will work, if you enter in C1 of your final sheet and drag down.

=SUMIF(stock-count1!A:A,A2,stock-count1!C:C)+SUMIF(stock-count2!A:A,A2,stock-count2!C:C)+SUMIF(stock-count3!A:A,A2,stock-count3!C:C)

You can continue adding SUMIF statements as needed for all the sheets you have.

ADDITIONAL INFO

If you have many, many sheets, you can place your sheet names in row 1 of the final sheet (say, starting in column E) and then drag this formula across the columns (and down the rows for each SKU) to get the count of SKU for each sheet, then write a simple SUM formula in column C to get the total for each SKU.

=SUMIF(INDIRECT(E$1&"!A:A"),A2,INDIRECT(E$1&"!C:C"))

Upvotes: 2

Will F
Will F

Reputation: 191

Use VLOOKUP, one of the lookup and reference functions, when you need to find things in a table or a range by row.

You can combine VLOOKUP with nested IFERROR functions in order to sequentially look up values in all three tables.

For example, the following formula:

=IFERROR(VLOOKUP(A2,$F$2:$H$5,3,FALSE),IFERROR(VLOOKUP(A2,$I$2:$K$5,3,FALSE),VLOOKUP(A2,$L$2:$N$5,3,FALSE)))

The logic goes like this:

  1. VLOOKUP value A2 in the range F2:H5 (it will automatically look up in the first column to get F-something)

  2. VLOOKUP returns the 3rd column's value for that lookup (H-something), and

  3. IF an ERROR is returned (no value found), go to the next lookup: table I-K.

Your formula would look something like this, with the three ranges replaced with your external worksheet ranges.

=IFERROR(VLOOKUP(A2,[first range],3,FALSE),IFERROR(VLOOKUP(A2,[second range],3,FALSE),VLOOKUP(A2,[third range],3,FALSE)))

See here for more information on VLOOKUP.

Upvotes: 0

Related Questions