Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Worksheets
Hi there, I am only a basic user of Excel so am hoping someone can help me
with my question as it will save me so much time. In my workbook I have 2 work sheets. Sheet 1 is my Location List for our stock we store in our store room Column A is my Store Room Location Column B is our Stock Number Sheet 2 is our Items list exported from our software programme Column V is headed up CUSTFLD1 but it contains our stock Number Column Y is headed up CUSTFLD4-and is where I need the Store Room Location from Sheet 1 to go. My problem is not all of the stock numbers have a store room location. I have been manually cutting & pasting which is taking me forever & I keep getting it all mixed up. Is there someway Excel can match the Stock Number from Sheet 1 to Sheet 2 & Enter the corresponding Store Room Location from Sheet 1 to Sheet 2? Oh, I hope this makes sense to somebody out there & I would really love to hear from someone. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Worksheets
One way is index/match
In Sheet2, In Y2: =INDEX(Sheet1!A:A,MATCH(V2,Sheet1!B:B,0)) Copy down. This will return #N/As for unmatched cases If you need an error trap to return blanks: "" instead of ugly #N/As, put in Y2: =IF(ISNA(MATCH(V2,Sheet1!B:B,0)),"",INDEX(Sheet1!A :A,MATCH(V2,Sheet1!B:B,0))) Copy down voila? click YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "gipsynic" wrote: Hi there, I am only a basic user of Excel so am hoping someone can help me with my question as it will save me so much time. In my workbook I have 2 work sheets. Sheet 1 is my Location List for our stock we store in our store room Column A is my Store Room Location Column B is our Stock Number Sheet 2 is our Items list exported from our software programme Column V is headed up CUSTFLD1 but it contains our stock Number Column Y is headed up CUSTFLD4-and is where I need the Store Room Location from Sheet 1 to go. My problem is not all of the stock numbers have a store room location. I have been manually cutting & pasting which is taking me forever & I keep getting it all mixed up. Is there someway Excel can match the Stock Number from Sheet 1 to Sheet 2 & Enter the corresponding Store Room Location from Sheet 1 to Sheet 2? Oh, I hope this makes sense to somebody out there & I would really love to hear from someone. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merge multiple worksheets from multiple excel files into oneworksheet | Excel Discussion (Misc queries) | |||
SUMPRODUCT / SUMIF on Multiple Worksheets with Multiple Criteria | Excel Discussion (Misc queries) | |||
how to make a macro to clear multiple cells from multiple worksheets? | Excel Worksheet Functions | |||
Delete Blank Rows Code - Multiple Worksheets - Multiple Documents | Excel Discussion (Misc queries) | |||
adding certain cells in multiple worksheets in multiple workbooks | Excel Worksheet Functions |