Hi,
I have an excel work with three tables named TABLEONE,TABLETWO and TABLETHREE. The tables are shown in the TABLES sheet in the attached workbook.
The second sheet in the workbook is called BOARDS. In this sheet I wish to create a number of tables called BOARD1, BOARD2 and so on. In each of the BOARD tables I wish to use the VLOOKUP function to retrieve data from the tables on the TABLES sheet. I have a created column B which has the table names and then when using VLOOKUP I refer to column B for the second parameter. This creates a #N/A error. I have tried to use functions like TRIM,CLEAN etc. around the second parameter but to no avail.
If I use the actual names for the second parameter it works BUT..... for example, in BOARD1 , when I use TABLEONE on row 4, it returns the correct answer but also populates the subsequent rows with TABLEONE which is not what I want! I want to retrieve data from TABLETWO, TABLETHREE and so on.
I hope all this makes sense - can you help please?
many thanks,
Peter
Hi Peter,
Thanks for sharing your file. I'm not sure I follow what you want. I can see the formulas you've tried, but these don't show me what answers you were hoping to see in the boards. See if the formula below in cell D4 of the Boards sheet gives you the desired result:
=VLOOKUP($E$1,INDIRECT($B4&"[[Board]:[EW]]"),2,FALSE)
If not, can you please create a mockup of your desired result so we can follow the flow of data from the Tables sheet to the Boards sheet.
Thanks,
Mynda
Hi Mynda,
Many thanks for your response - and apologies for not making it clear what I was trying to achieve!
Using the INDIRECT statement has helped me get a solution which works - see attached. So thanks for that!
However, while this works I have a gut feeling that this is not particularly elegant and that I am not using the full features of tables correctly. I feel I might be able to use the Headers in my formula somehow? Maybe not but would welcome your advice.
What I am trying to do is transpose the values from the tables to the boards.
So that:
Line 1 of Table 1 is copied to Line 1 of Board 1
Line 2 of Table 1 is copied to Line 1 of Board 2
Line 3 of Table 1 is copied to Line 1 of Board 3
and so on until
Line 27 of Table 1 is copied to Line 1 of Board 27 (please note that I have not created Board 27 yet)
After this:
Line 1 of Table 2 is copied to Line 2 of Board 1
Line 2 of Table 2 is copied to Line 2 of Board 2
Line 3 of Table 2 is copied to Line 2 of Board 3
and so on.
I hope all this makes sense?
Once again many thanks.
Peter
Hi Peter,
I agree, this is very inefficient, and a lot of INDIRECT functions used like this can slow Excel down. I would use Power Query to gather the data from the 3 Tables and then use PivotTables to create the layout you want. See file attached.
Mynda
