Lookup formula referring to other worksheets with indirect

[havardrye]

I have workbook with three sheets: Total, UK and FR. I want to build a forumula in "Total" where i use a lookup function that picks a value in the UK or the FR sheet, depending on what I type in cell A1 in "Total".

I have tried to use the "indirect" function, but haven't succeed so far. It seemes that the indirect formula doesen't accept that the string in the referred cell is a formula. Suggestions?

Thanks

Håvard

 

[BillXL]

Hi Håvard

You could try:

=INDIRECT(A1&"!B1"), this will return the value of B1 in either UK or Fr, depending on what has been entered in A1 on Total.

It sounds like you may have already tried this. And of course B1 is static here. You can work around this by entering this on cell B2 in Total:

=INDIRECT(A1&"!"&ADDRESS(ROW(),COLUMN()))

This will pick up B2 in either UK or Fr. And it is like a relative reference which you can copy to other cells.

Regards

Bill

 

[havardrye]

The problem is that I want to use a lookup function, that looks up in on of the two sheets, depending on wether I write UK og FR in A1.

Håvard

 

[BillXL]

Hi Håvard <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
You can use Indirect with a lookup.<o></o>
<o></o>
Something like this works:<o></o>
<o></o>
=VLOOKUP("LookupValue",INDIRECT(A1&"!"&"A1:B10"),2,0)<o></o>
<o></o>
This looks up the value in column A & retrieves the value in column B. The sheet is determined by your input in A1 on the Total sheet.<o></o>
<o></o>
<o></o>
Alternatively, you could use named ranges in your Fr & UK sheets – this will be more flexible and dymanic.<o></o>
<o> </o>
Does this help?<o></o>
<o> </o>
Bill