Using sheet name in formula
 

I have a workbook with 51 sheets. The sheets are named Master, and then
1 - 50. In Master, I paste two columns of figures daily. Colum A has
account numbers between 1 and 50 (but crucially, not always every
number between 1 and 50), and column B has values. Sheets named 1 to 50
do a look up on Master, and then perform a financial calculation. In
column C on Master, there are formulae from lines 1 thru 50 which do a
look up on sheets 1 thru 50 to get the result of the financial
calculation.

Because the daily paste into Master does not always include all the
numbers in the range 1 thru 50, I need the formulae in Master column C
to pick up the number in column A as part of its look-up formula. I'm
sure I have to use Indirect but I can't figure out how!


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=386078

=INDIRECT(A2&"!A1)

assuming A1 in sheets 1-50, change to suit.

--
HTH

Bob Phillips

"Brisbane Rob"
wrote in message
news:Brisbane.Rob.1s056f_1121079916.0352@excelforu m-nospam.com...

I have a workbook with 51 sheets. The sheets are named Master, and then
1 - 50. In Master, I paste two columns of figures daily. Colum A has
account numbers between 1 and 50 (but crucially, not always every
number between 1 and 50), and column B has values. Sheets named 1 to 50
do a look up on Master, and then perform a financial calculation. In
column C on Master, there are formulae from lines 1 thru 50 which do a
look up on sheets 1 thru 50 to get the result of the financial
calculation.

Because the daily paste into Master does not always include all the
numbers in the range 1 thru 50, I need the formulae in Master column C
to pick up the number in column A as part of its look-up formula. I'm
sure I have to use Indirect but I can't figure out how!


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile:
http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=386078

Thanks Bob but all I get is an "error in formula" when I tried
=INDIRECT(A2&"!A1).
Help!


--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=386078

Try

=INDIRECT(A2&"!A1")

In article ,
Brisbane Rob
wrote:

Thanks Bob but all I get is an "error in formula" when I tried
=INDIRECT(A2&"!A1).
Help!

I'd use:

=INDIRECT("'" & A2 & "'!" & A1)

just in case the worksheet would require single quotes around it.



Brisbane Rob wrote:

Thanks Bob but all I get is an "error in formula" when I tried
=INDIRECT(A2&"!A1).
Help!

--
Brisbane Rob
------------------------------------------------------------------------
Brisbane Rob's Profile: http://www.excelforum.com/member.php...o&userid=25096
View this thread: http://www.excelforum.com/showthread...hreadid=386078

--

Dave Peterson