Using Excel 2016.
I have attached a sample of the spreadsheet and workbook. The spreadsheet formatting cannot be altered.
1. In spreadsheet A in 1st merged cell (B3-B16)
2. Use only number data for VLOOKUP in Workbook B with multiple tabs
3. B3 number data found in Workbook B, Team 411, B4
4. Go back to Spreadsheet A
5. Copy data in C3-K16 – (includes hidden columns, keep hidden)
6. Back to Workbook B
7. Paste data as value into Workbook B, Team 411, C4 – (includes hidden columns, keep hidden)
8. Back to Spreadsheet A
9. Find next merged cell (B18-26)
10. Use only number data for VLOOKUP in Workbook B with multiple tabs
11. If not found, go back to Spreadsheet A
12. Find next merged cell (B28-32)
13. Use only number data for VLOOKUP in Workbook B with multiple tabs
14. B28 number data found in Workbook B, Team 425, B31
15. Go back to Spreadsheet A
16. Copy data in C28-K32 - (includes hidden columns, keep hidden)
17. Back to Workbook B
18. Paste data as value into Workbook B, Team 425, C31 – (includes hidden columns, keep hidden)
19. Back to Spreadsheet A
20. Find next merged cell etc.
The Spreadsheet A relative merged cell issue cannot be resolved; however, it is possible to find the same data one row down. (B3-B16 = B17 or B28-32 = B33)
Spreadsheet A’s relative cell data can be matched only to Workbook B’s absolute cell data in B4, B31, B58, B85 thru B490. Is VLOOKUP the best function for this? The whole text string in Spreadsheet A merged cell could be used if needed, but is not ideal.
The merged cell’s upper and lower Row range is not consistent but is critical for the copy and paste of the Columns C-K data, which cannot be determined any other way.
Thanks.
Bookmarks