Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sheet names used in formulas
I have a question about using sheet names in a formula. I would like the
sheet name to go up by one page for each row I put the formula in...(ie) 13A reads (="S40'!$W$5)... I need to have row 14 read (='S41'!$W$5) There are many many worksheets...260 in all that need this on them. Each worksheet is about 300 rows long. How can I do this without having to change the sheet name by hand??? Any help is greatly appreciated!!! |
#2
|
|||
|
|||
Hi!
Here's one way: =INDIRECT("S"&ROW(A40)&"!$W$5") Which evaluates to: =S40!$W$5 As you drag copy down: =S40!$W$5 =S41!$W$5 =S42!$W$5 =S43!$W$5 etc.. Biff -----Original Message----- I have a question about using sheet names in a formula. I would like the sheet name to go up by one page for each row I put the formula in...(ie) 13A reads (="S40'!$W$5)... I need to have row 14 read (='S41'!$W$5) There are many many worksheets...260 in all that need this on them. Each worksheet is about 300 rows long. How can I do this without having to change the sheet name by hand??? Any help is greatly appreciated!!! . |
#3
|
|||
|
|||
frustrated
In A13 enter this and drag/copy down =INDIRECT("S" & (ROW()+27) & "!W5") Gord Dibben Excel MVP On Thu, 24 Feb 2005 11:51:04 -0800, frustratedwthis wrote: I have a question about using sheet names in a formula. I would like the sheet name to go up by one page for each row I put the formula in...(ie) 13A reads (="S40'!$W$5)... I need to have row 14 read (='S41'!$W$5) There are many many worksheets...260 in all that need this on them. Each worksheet is about 300 rows long. How can I do this without having to change the sheet name by hand??? Any help is greatly appreciated!!! |
#4
|
|||
|
|||
Isn't it easier to use
ROW(40:40) instead of ROW()+27 ? -- Regards, Peo Sjoblom "Gord Dibben" <gorddibbATshawDOTca wrote in message ... frustrated In A13 enter this and drag/copy down =INDIRECT("S" & (ROW()+27) & "!W5") Gord Dibben Excel MVP On Thu, 24 Feb 2005 11:51:04 -0800, frustratedwthis wrote: I have a question about using sheet names in a formula. I would like the sheet name to go up by one page for each row I put the formula in...(ie) 13A reads (="S40'!$W$5)... I need to have row 14 read (='S41'!$W$5) There are many many worksheets...260 in all that need this on them. Each worksheet is about 300 rows long. How can I do this without having to change the sheet name by hand??? Any help is greatly appreciated!!! |
#5
|
|||
|
|||
Yes
But I not so smart like you so use calculator first to figure out how many to add<g Now, if it was GAA or GF/A I wouldn't need a calculator(at least this year) Gord On Thu, 24 Feb 2005 16:33:41 -0700, "Peo Sjoblom" wrote: Isn't it easier to use ROW(40:40) instead of ROW()+27 ? |
#6
|
|||
|
|||
He guys,
I used =INDIRECT("S"&$B14&"!$F$6") Thanks so much for your help!!! "Gord Dibben" wrote: Yes But I not so smart like you so use calculator first to figure out how many to add<g Now, if it was GAA or GF/A I wouldn't need a calculator(at least this year) Gord On Thu, 24 Feb 2005 16:33:41 -0700, "Peo Sjoblom" wrote: Isn't it easier to use ROW(40:40) instead of ROW()+27 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
replacing just the sheet names within formulas | Excel Worksheet Functions | |||
Locking Sheet names in formulas | Excel Worksheet Functions | |||
How can I show sheet names in different colours | New Users to Excel | |||
Listing of Sheet names | Excel Worksheet Functions | |||
Naming & renaming a sheet tab | Excel Worksheet Functions |