Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
=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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable Sheet Name in Formula | Excel Discussion (Misc queries) | |||
Can't get a formula to work in each row of an Excel sheet. | Excel Worksheet Functions | |||
Sheet name in formula | Excel Worksheet Functions | |||
Sheet name in formula | Excel Worksheet Functions | |||
Need formula for sheet & cell reference | Excel Worksheet Functions |