VLOOKUP Challenge

love2clym

New member
Joined
Apr 30, 2014
Messages
5
Reaction score
0
Points
0
I'm fairly green to Excel and this task is proving very difficult (or impossible) for me, can you help?

So here's what I'm trying to do:
- Sheet 1 of my Workbook: Columns 1 - 10 contain item information extracted from a report, Columns 11 & 12 have date information entered manually.
> as time passes some items are received (thus falling off of the report) and some items are added (thus adding onto the report), so the report that the item information is extracted from is constantly changing.

- Sheet 2 of my Workbook is the most up-to-date version of the report: Columns 1 - 10 contain the same item information as Sheet 1, and Columns 11 & 12 still need date information.
> So, how can I get the manually entered date information from Sheet 1 to follow the item information to Sheet 2? Some items will have fallen off, some items will remain (these are the ones needing the dates to transfer...using VLOOKUP, and maybe IFERROR and/or MATCH???), and some items will be new (needing new date information to be manually entered) - I'm trying to only enter the date information once and avoid having to re-enter all of the date information every time I update the report (which is four times per day).

- One aspect of the challenge is that the item information on the spreadsheet is filtered from the report so what would usually be Row 2 might read Row 547 on Sheet 1, and the next time the report is updated the same item might still be where Row 2 normally is but now it might read Row 532 on Sheet 2...the Row that's 20 rows down from the top might read Row 1237 on Sheet 1, and the next time the report is updated that item might now be 29 rows down from the top and now read Row 1286 - so the Rows are always jumping around but I need the date information to stick with the item information, not the Row #.
> Every time I update the report I cut the spreadsheet from Sheet 2 and paste it over Sheet 1, next I paste the updated report information onto Sheet 2, then (if I can figure out how to get all of this to work) I'll just have a few blanks in Columns 11 & 12 to fill in and the report is complete and up-to-date.

See attachment for a sample.

Any help would be sooo much appreciated!

Thank you!
 

Attachments

  • SAMPLE REPORT.xlsx
    14.2 KB · Views: 13
Last edited:

Rizky

New member
Joined
Apr 28, 2014
Messages
44
Reaction score
0
Points
0
Not clear about what you need as a results, Is it you need to combine both data in Sheet 1 and Sheet 2?
 

love2clym

New member
Joined
Apr 30, 2014
Messages
5
Reaction score
0
Points
0
Correction to attachment

Sorry, the information on attachment was probably confusing - it is now corrected. What I've done is manually entered the date information for the new items on Sheet 2, and the 'VLOOKUP Challenge' would be to get the date information from Sheet 1 to automatically transfer, or auto populate, to Sheet 2 (for items that are on both Sheet 1 and Sheet 2), and this would fill in all of the blanks, completing the report.

Sorry for the confusion. Thanks again for you help!
 

Attachments

  • SAMPLE REPORT 2.xlsx
    14.2 KB · Views: 10
Last edited:

love2clym

New member
Joined
Apr 30, 2014
Messages
5
Reaction score
0
Points
0
Sorry, I fixed the attachment so hopefully that helps clear up the confusion - I need the date information from Sheet 1 to auto populate in Sheet 2 for the items that remain on the report. Sheet 2 already has the date information manually entered for the new items on the report. So, basically I'm trying to get all of the blanks on Sheet 2 to fill in automatically.

Thank you!
 

Rizky

New member
Joined
Apr 28, 2014
Messages
44
Reaction score
0
Points
0
Perhaps something like this? please see the file
 

Attachments

  • SAMPLE REPORT 2.xlsx
    15.6 KB · Views: 12

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,199
Reaction score
16
Points
38
Excel Version(s)
365
Would it be just the part no. that would ensure that the correct row is identified? If not, what combination of columns would you need to check to guarantee that the dates etc. are being fetched from the correct row?
 

love2clym

New member
Joined
Apr 30, 2014
Messages
5
Reaction score
0
Points
0
Would it be just the part no. that would ensure that the correct row is identified? If not, what combination of columns would you need to check to guarantee that the dates etc. are being fetched from the correct row?

Unfortunately the part number alone wouldn't be specific enough, as sometimes there is no part number generated from the report, and sometimes there are two or more of the same part numbers on order for different jobs. So, I would say at least the Vendor Name combined with the RO #. An exact match of Columns 1 - 9 would be great for maximum accuracy but if it's not possible then it is what is.

Any help is very much appreciated. Thank you so much!
 
Top