indirect not doing anything

[ammartino44]

Hello. I have the following formula ="["&INDIRECT("AA12")&".xls]Sheet1!C15" and it's intended to return that value in C15 in a different workbook. However, all it's doing is writing the text in the cell. How can I fix this?

[FDibbins]

Remove the "" around AA12

[ammartino44]

Now it gives me a ref error. The value that I have in AA12 is 41655 (date).

[martindwilson]

eveything meets to be in the indirect this works
=INDIRECT("["&AA12&"]Sheet1!C15")
what is the name of the workbook?

[ammartino44]

Oh nice, @martindwilson. Your formula worked. Can you explain what it is doing and why Fdibbins formula didn't work? Also, can I subtract 1 from the number within the indirect? So it will reference 41655 but return 41644? Thanks.

[martindwilson]

you need to build the whole reference inside the indirect
i tried it this way and it also works
=INDIRECT("'["&AA12&".xlsx]Sheet1'!C15")
if you try
="c"&a1 where a1= 5 and c5=100 you get text of "c5"
if you wrap it in indirect
=INDIRECT("c"&A1) you get what is in c5 ie 100
this is what i see

[ammartino44]

When I use xls it works, but not when I use your xlsx. Why is that?

[martindwilson]

probably because the file i'm looking at is a .xlsx?

[FDibbins]

If your file is .xlsx, then it will not be able to find .xls

[ammartino44]

Makes sense haha. Last question: If I wanted to use the indirect above but use this formula instead of the C15 reference, how could I do that? OFFSET($A$8,MATCH("COMPANY TOTAL",$A$8:$A$851,0)-1,4)

[ammartino44]

Maybe I'll start a new thread.

[martindwilson]

why are you using offset?
=INDEX($E$8:$E$851,MATCH("company total",$A$8:$A$851,0)) is more practical

[ammartino44]

I'm using offset because there are multiple instances of particular lookups. I could be wrong though. Why are you using E? I need to combine the indirect formula above with the lookup formula I created (or a better one) so that it does the lookup on that particular workbook.

[martindwilson]

because that is where the offset is looking 4 columns away from column A ie column E

[ammartino44]

Ok. So, how would you combine that formula with the indirect formula that you gave me above?

[martindwilson]

done in other thread