Index Match First Adjacent Worksheet to the Right

[KolKon]

Hello! I'm hoping anyone can help me on my problem.

Every two weeks I add a worksheet to a workbook (sample attached). I need to transfer any notes from the Notes column from the first right most adjacent only worksheet (this worksheet name will always vary) to the current worksheet (this worksheet name will always be 'Open') where the P# matches.

I need either a formula for each row where I don't have to change the name of the first right-most adjacent worksheet or VBA. If possible, the formula or VBA would need to function where the workbook would be a Google sheet. This is not a requirement but would be ideal.

Notice:
1-None of the worksheets can be a table
2-The right-most adjacent worksheet could be named anything
3-The number of rows in adjacent worksheet could vary
4-The number of rows in 'Open' worksheet could vary
5-Row one in every worksheet will always have the same header
6-There will always be the same number of columns in the same order in every worksheet
7-Not all P#s will be in every worksheet (would like the note to show blank instead of 'N/A#' for these)
8-Not all P#s will have a note (would like the note to show blank instead of '0' for these)

Please let me know if more information is needed. I appreciate any assistance.

[Glenn Kennedy]

Maybe, put the target worksheet name in a cell and then use:

=IFERROR(VLOOKUP(A2,INDIRECT("'"&$E$1&"'!A1:C100"),3,FALSE)&"","")

copied down

[AlphaFrog]

You could use a User Defined Function (UDF) that looks up your notes on the 2nd sheet. This code would not work on Google sheets, but it could be adapted to work (it's beyond me though).

Put this code in a standard code module e.g. Module1

Please Login or Register  to view this content.

Put a formula like this in C2 and copy down
=Sheet2Notes(A2)

[KolKon]

Thank you both. Both options worked. Unfortunately, I have since found out I must get it working in Google Sheets.