I am new and not use to forums... so if I am outside the remit just let me know.
My issue is how to use INDIRECT accessing information in one workbook to generate a table in another.
Within a single workbook
I use the formula =IFERROR(VLOOKUP($C7,INDIRECT("'"&D$6&"'!"&"Q19:R36"),2,0),0) to seek out the relevant worksheet found in D$6
To create unique lists from several columns an array formula such as =INDIRECT(TEXT(MIN(IF(($Y$7:$AN$26<>"")*(COUNTIF($AQ$6:AQ6,$Y$7:$AN$26)=0),ROW($7:$26)*100+COLUMN($Y:$AN),7^8)),"R0C00"),)&"".
I have several spreadsheets which use individual sheets for data based on standard template. Some of the spreadsheets have the same data sheets in them.
My idea is to build one workbook with all the data sheets in one place and for individual spreadsheets to call off the data as required.... which makes sense and should have been my first plan!
Basically this is to allow me to update the data sheets in one place and not have to copy the same data to replicated sheets.... or worst have different data in the same sheet.
I can build a dynamic list of sheet names using =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) but I am losing the thread on how to change my formulas to make it work across two workbooks {both open}.
Any suggestions or alternatives would be very welcome.
Bookmarks