Hello,

This hopefully should be easy :) :)

I want to reference a value in a different sheet, but I want the sheet to be
choosen by the contents of another cell.

So in other words, if I have a drop down menu of my Sheet names in A1 - I
want to be able to use this 'kind' of formula in B1, '=A1!B3'. The end result
would be to return the value of B3 from the sheet choosen in A1.

This obviously doesn't work, which is why I need help :)

Any Ideas Please?

Many Thanks

Mark

Try =INDIRECT("'" & A1 & "'!B3")


--
Regards
Dave Hawley
www.ozgrid.com
"Mark Holland" wrote in message
...
Hello,

This hopefully should be easy :) :)

I want to reference a value in a different sheet, but I want the sheet to
be
choosen by the contents of another cell.

So in other words, if I have a drop down menu of my Sheet names in A1 - I
want to be able to use this 'kind' of formula in B1, '=A1!B3'. The end
result
would be to return the value of B3 from the sheet choosen in A1.

This obviously doesn't work, which is why I need help :)

Any Ideas Please?

Many Thanks

Mark

Hi

you want the 'Indirect' function

Example:

Put "Hello" in Sheet 2, A1
Put "World" in Sheet3, A1

in Sheet1 B1 put "Sheet2!"
in sheet1 B2 put "Sheet3!"
(dont forget the exclamaion mark!!!)

in sheet1 A1
=indirect(b1&"A1")&" "&indirect(b2&"A1)


Ta da

RegMigrant


"Mark Holland" wrote:

Hello,

This hopefully should be easy :) :)

I want to reference a value in a different sheet, but I want the sheet to be
choosen by the contents of another cell.

So in other words, if I have a drop down menu of my Sheet names in A1 - I
want to be able to use this 'kind' of formula in B1, '=A1!B3'. The end result
would be to return the value of B3 from the sheet choosen in A1.

This obviously doesn't work, which is why I need help :)

Any Ideas Please?

Many Thanks

Mark

Copy and paste the below formula in B1 cell
=INDIRECT(INDIRECT("A"&1)&"!"&"B"&3)
--
Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Mark Holland" wrote:

Hello,

This hopefully should be easy :) :)

I want to reference a value in a different sheet, but I want the sheet to be
choosen by the contents of another cell.

So in other words, if I have a drop down menu of my Sheet names in A1 - I
want to be able to use this 'kind' of formula in B1, '=A1!B3'. The end result
would be to return the value of B3 from the sheet choosen in A1.

This obviously doesn't work, which is why I need help :)

Any Ideas Please?

Many Thanks

Mark

Still more perfect to suit your anticipated needs: If you have a drop down
list of worksheet names in the column A from A2 downwards, and addresses of
sevaral referenced cells in the worksheets in the row 1 from B1 to the right,
you could use formula with anchored references
= INDIRECT("'" & $A2 & "'!" & B$1)
and copy it downwards and to the right.

--
Petr Bezucha


"Ms-Exl-Learner" wrote:

Copy and paste the below formula in B1 cell
=INDIRECT(INDIRECT("A"&1)&"!"&"B"&3)
--
Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Mark Holland" wrote:

Hello,

This hopefully should be easy :) :)

I want to reference a value in a different sheet, but I want the sheet to be
choosen by the contents of another cell.

So in other words, if I have a drop down menu of my Sheet names in A1 - I
want to be able to use this 'kind' of formula in B1, '=A1!B3'. The end result
would be to return the value of B3 from the sheet choosen in A1.

This obviously doesn't work, which is why I need help :)

Any Ideas Please?

Many Thanks

Mark