Actually I've read a little about indirect and still not 100% sure on its use in practice in my worksheet. Sorry I'm not amazing on excel but can take things in when i put them into practice and look back at it and get the understanding from my working example I've been helped to create.
I've never touched indirect so not 100% sure how to use it sorry. but reading another guide I found explaining its uses and rethinking what I want to achieve to simplfy this task and I think I don't need to VLOOKUP the formulas and could use INDRECT to change which worksheet page and document it points part of the formula too. As the only differences in the formulas I wanted to rip with a VLOOKUP is that they point to a different sheet than the default choice.
Example:
i have say 2000 rows of data from row 2 to 2002. Formula's from Column A to K.
in column P is the name of the data type.
each data type looks up order numbers on a different worksheet in another document. If i could use indirect to change that part of the formula only I would get the same result I'm looking for.
below is a formula in cell A2 which is a formula i have to normally copy and paste over - based on what text value is in column P on the line of data. this is from row 2
basically in this cell (a2) it looks up at the end of the data in the row in P2. In here is a word which is "Data type 1" in this example on P2. This is the data type and name of the worksheet it needs to lookup in another document so currently it looks like this
Quote:
|
=VLOOKUP(P2,'[different workbook.xlsx]Data type 1'!$A:$I,5,FALSE)
|
I manually review it and if it was say Data Type 2 in cell column P I would copy and paste over it
Quote:
|
=VLOOKUP(P2,'[different workbook.xlsx]Data type 2'!$A:$I,5,FALSE)
|
now looking at indirect even though I dont properly understand it. I think it could check out column P for me and then use that to enter a different sheet name.
could you give me an example of how to do this based on the quote above for row 2 and using cell p2 as the cell which has the sheet name in it.
Only extra thing I need in the example formula, is what to do when there is no text or numbers in column P. I would then like it to then default to the sheet in the other document "
MD Lookup Table" instead of the "
Data Type 1" etc.
Think this will be much simpler to get the same result. Can you give me an example and then I can apply it to all the other cells and put it in practice. Thanks for your help again, this has been a helpful site