Hi,
I have a workbook that contains two sheets.
Sheet 1. PONumbers
Sheet 2. PORequest
I am trying to find a way to look up a reference in Sheet 2 (Column A), find a match in Sheet 1 (Column A), then return the value of the last populated cell in the row.
I have tried Vlookup, but as the column index number will vary with each look up, it isn't sufficient for the task at hand.
I have also changed the layout of Sheet 1 and tried Hlookup, but again the variable index number is making me hit a wall.
Can you please advise if there is a way to obtain my data using a lookup function? If not, please can you advise of an alternative method to obtain the data?
Example Information
Sheet 1 contains a list of Supplier/Supplier Codes with corresponding PO Numbers.
Sheet 2 is populated automatically by either Data Validation List or Vlookup function.
In this example, I would like Sheet 2 J3, to display the data from Sheet 1 (highlighted in orange) depending on which supplier is selected in Sheet 1 Column A.
Selecting Supplier 1 would return data from Sheet 1 J2.
Selecting Supplier 2 would return data from Sheet 1 G3.
So on and so forth for future suppliers/po numbers.
Further Notes
The layout of the data in Sheet 1 can be adjusted. Sheet 2 needs to remain the same.
The data in Sheet 1 will eventually extend down Column A and across the related rows to an unknown range.
I am manually creating PO numbers in Sheet 1 by using the drag function to increase the number by 1 each time.
I then copy and paste this number into the relevant cell in Sheet 2, Column J.
This method is temporary, my next challenge is to learn how to automate this - I am not sure if this impacts my query.
Thanks
I have a workbook that contains two sheets.
Sheet 1. PONumbers
Sheet 2. PORequest
I am trying to find a way to look up a reference in Sheet 2 (Column A), find a match in Sheet 1 (Column A), then return the value of the last populated cell in the row.
I have tried Vlookup, but as the column index number will vary with each look up, it isn't sufficient for the task at hand.
I have also changed the layout of Sheet 1 and tried Hlookup, but again the variable index number is making me hit a wall.
Can you please advise if there is a way to obtain my data using a lookup function? If not, please can you advise of an alternative method to obtain the data?
Example Information
Sheet 1 contains a list of Supplier/Supplier Codes with corresponding PO Numbers.
Sheet 2 is populated automatically by either Data Validation List or Vlookup function.
In this example, I would like Sheet 2 J3, to display the data from Sheet 1 (highlighted in orange) depending on which supplier is selected in Sheet 1 Column A.
Selecting Supplier 1 would return data from Sheet 1 J2.
Selecting Supplier 2 would return data from Sheet 1 G3.
So on and so forth for future suppliers/po numbers.
Further Notes
The layout of the data in Sheet 1 can be adjusted. Sheet 2 needs to remain the same.
The data in Sheet 1 will eventually extend down Column A and across the related rows to an unknown range.
I am manually creating PO numbers in Sheet 1 by using the drag function to increase the number by 1 each time.
I then copy and paste this number into the relevant cell in Sheet 2, Column J.
This method is temporary, my next challenge is to learn how to automate this - I am not sure if this impacts my query.
Thanks