Hi All,
I have been banging my head against a brick wall all day on this one, any help would be greatfully appreciated
I have been trying to create a database sheet that looks up values on >200 sheets and formats them into a flat dataset so I can use them for lookups in the database sheet. The source sheets are not identical and are price builds for projects which are slightly different to each other, the positions of the values I need are -+10 rows. I found the PULL UDF function which works great for the values whose position is always the same;
Code:
=IFERROR(PULL($D2&"$H$4"),"N/A")
However, I need to do the find the correct row for the other items I need for which I have used the INDIRECT function to create the Workbook!$H$ part and a MATCH (with an INDIRECT argument) to find the correct row;
Code:
=IFERROR(INDIRECT($D2&"$H$"&MATCH(I$1,INDIRECT(($D2&"$A$1:$A$1000")),0)),"N/A")
I need whatever formula to be able to have the source work book changed since I need to collate 100+ workbooks in several different folders. This is why I tried indirect.
D2 is a text string which creates the 'serverdatasharefolder[workbook.xls]Sheet'! part of the address.
I have seen the INDIRECT.EXT function but this is in an add in, if I install it at home and use it in a sheet will the add-in need to be installed at work?
Are there any alternatives to the INDIRECT.EXT function?
Thanks in adance
Any ideas?
I have been banging my head against a brick wall all day on this one, any help would be greatfully appreciated
I have been trying to create a database sheet that looks up values on >200 sheets and formats them into a flat dataset so I can use them for lookups in the database sheet. The source sheets are not identical and are price builds for projects which are slightly different to each other, the positions of the values I need are -+10 rows. I found the PULL UDF function which works great for the values whose position is always the same;
Code:
=IFERROR(PULL($D2&"$H$4"),"N/A")
However, I need to do the find the correct row for the other items I need for which I have used the INDIRECT function to create the Workbook!$H$ part and a MATCH (with an INDIRECT argument) to find the correct row;
Code:
=IFERROR(INDIRECT($D2&"$H$"&MATCH(I$1,INDIRECT(($D2&"$A$1:$A$1000")),0)),"N/A")
I need whatever formula to be able to have the source work book changed since I need to collate 100+ workbooks in several different folders. This is why I tried indirect.
D2 is a text string which creates the 'serverdatasharefolder[workbook.xls]Sheet'! part of the address.
I have seen the INDIRECT.EXT function but this is in an add in, if I install it at home and use it in a sheet will the add-in need to be installed at work?
Are there any alternatives to the INDIRECT.EXT function?
Thanks in adance
Any ideas?
