Using sheet name in formula

[Brisbane Rob]

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!

 

[Bob Phillips]

=INDIRECT(A2&"!A1)

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

--
HTH

Bob Phillips

"Brisbane Rob" <[email protected]>
wrote in message
news:[email protected]...

 

[Brisbane Rob]

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

 

[JE McGimpsey]

Try

=INDIRECT(A2&"!A1")

Brisbane Rob

 

[Dave Peterson]

I'd use:

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

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