Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Multipart formula needed
I have a spreadsheet with two worksheets (A and B).
(A) has information regarding stock ordered and in transit, and (B) is a complete list, updated weekly, of stock on hand. Both worksheets contain a column of identifying SKU numbers I would like to create some kind of formula that will: Look at the first cell of the column containing the SKU number in (A) and search the SKU number column for a match in (B) If/when it finds the identical SKU number in (B), it would find the current stock on hand qty from that row Then it would return that value (stock on hand) into an empty cell in worksheet (A) Then I could copy and paste this formula down through the whole spreadsheet - Thanks to anyone who can help! |
#2
|
|||
|
|||
Hi Barb,
As long as the table in sheet B has the SKU number before the value to be returned you can use VLOOKUP. Worksheet Function http://www.mvps.org/dmcritchie/excel/vlookup.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Barb" wrote in message ... I have a spreadsheet with two worksheets (A and B). (A) has information regarding stock ordered and in transit, and (B) is a complete list, updated weekly, of stock on hand. Both worksheets contain a column of identifying SKU numbers I would like to create some kind of formula that will: Look at the first cell of the column containing the SKU number in (A) and search the SKU number column for a match in (B) If/when it finds the identical SKU number in (B), it would find the current stock on hand qty from that row Then it would return that value (stock on hand) into an empty cell in worksheet (A) Then I could copy and paste this formula down through the whole spreadsheet - Thanks to anyone who can help! |
#3
|
|||
|
|||
Barb,
Try = SUMIF(Sheet B!$A$2:$A$500 , Sheet1!$A2 , Sheet2!$B$2:$B$500) and Ctrl+Shift+Enter to execute in a blank cell on sheet A. Column A in both cases is the SKU number, and column B refers to the stock on hand list. (It is assumed that the list runs from row 2 to 500 - these can be altered) If I'm interpreting your request correctly, this formula will look for the SKU number in the stock on hand list and match it to the SKU number in the stock in transit list and show the stock on hand. This formula avoids having to sort the data into alphanumeric order, and if the SKU numbers appear more than once in random places in the list on sheet B, the total for that SKU number will be shown in sheet A. However, the SKU list in sheet A should only have each SKU number listed once to avoid duplication. hope this helps. "Barb" wrote: I have a spreadsheet with two worksheets (A and B). (A) has information regarding stock ordered and in transit, and (B) is a complete list, updated weekly, of stock on hand. Both worksheets contain a column of identifying SKU numbers I would like to create some kind of formula that will: Look at the first cell of the column containing the SKU number in (A) and search the SKU number column for a match in (B) If/when it finds the identical SKU number in (B), it would find the current stock on hand qty from that row Then it would return that value (stock on hand) into an empty cell in worksheet (A) Then I could copy and paste this formula down through the whole spreadsheet - Thanks to anyone who can help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! Formula needed. | Excel Discussion (Misc queries) | |||
Formula needed | Excel Worksheet Functions | |||
Look Up and Cell Reference - Formula Help Needed | Excel Worksheet Functions | |||
Formula needed | Excel Worksheet Functions | |||
Complex formula help needed | Excel Worksheet Functions |