Copying a formula down a summary sheet
I have a workbook with multiple spreadsheets from E73 to I91. On each sheet I
have a total that I would like to copy to a summary sheet. I have pasted the first formula into the summary sheet which is ='E64'!$K23$. I now want to copy this formula down with the sheet reference incrementing by one e.g the next one needs to be ='E65'!$K23$ and the next ='E66'!$K23$ and so on. What is the best and easiest way to do this??? Many thanks in advance |
Copying a formula down a summary sheet
You can use indirect
In cell B1 =INDIRECT(A1&"!$K23$") where A1 = E64. then copy down the column. Now in column A you need the sheet names. If you put E64 in A1 and E65 in A2. then highlight A1 and A2 and pull down the little square box down the column A you will get a series of sheet names E64, E65, E66, ...... "Blade370" wrote: I have a workbook with multiple spreadsheets from E73 to I91. On each sheet I have a total that I would like to copy to a summary sheet. I have pasted the first formula into the summary sheet which is ='E64'!$K23$. I now want to copy this formula down with the sheet reference incrementing by one e.g the next one needs to be ='E65'!$K23$ and the next ='E66'!$K23$ and so on. What is the best and easiest way to do this??? Many thanks in advance |
Copying a formula down a summary sheet
Did you mean sheets E73 to E91?
Why are you referencing sheet E64 and E65 and E66? Maybe this may be easiest method if you do have typos in your original post. =INDIRECT("E"&(ROW(64:64)&"!K23")) Drag/copy down. Gord Dibben MS Excel MVP On Fri, 12 Sep 2008 03:07:07 -0700, Blade370 wrote: I have a workbook with multiple spreadsheets from E73 to I91. On each sheet I have a total that I would like to copy to a summary sheet. I have pasted the first formula into the summary sheet which is ='E64'!$K23$. I now want to copy this formula down with the sheet reference incrementing by one e.g the next one needs to be ='E65'!$K23$ and the next ='E66'!$K23$ and so on. What is the best and easiest way to do this??? Many thanks in advance |
Copying a formula down a summary sheet
Hi,
No I did mean E73 to I91 as they are rooms in a student accomodation block. cheers "Gord Dibben" wrote: Did you mean sheets E73 to E91? Why are you referencing sheet E64 and E65 and E66? Maybe this may be easiest method if you do have typos in your original post. =INDIRECT("E"&(ROW(64:64)&"!K23")) Drag/copy down. Gord Dibben MS Excel MVP On Fri, 12 Sep 2008 03:07:07 -0700, Blade370 wrote: I have a workbook with multiple spreadsheets from E73 to I91. On each sheet I have a total that I would like to copy to a summary sheet. I have pasted the first formula into the summary sheet which is ='E64'!$K23$. I now want to copy this formula down with the sheet reference incrementing by one e.g the next one needs to be ='E65'!$K23$ and the next ='E66'!$K23$ and so on. What is the best and easiest way to do this??? Many thanks in advance |
Copying a formula down a summary sheet
I still don't understand the cell and sheet referencing you are describing.
Which are sheets and which are cell references? Where do E64, E65 and E66 fit into it? Where do the rooms fit into the picture? How many sheets do you have? Gord On Tue, 23 Sep 2008 07:05:01 -0700, Blade370 wrote: Hi, No I did mean E73 to I91 as they are rooms in a student accomodation block. cheers "Gord Dibben" wrote: Did you mean sheets E73 to E91? Why are you referencing sheet E64 and E65 and E66? Maybe this may be easiest method if you do have typos in your original post. =INDIRECT("E"&(ROW(64:64)&"!K23")) Drag/copy down. Gord Dibben MS Excel MVP On Fri, 12 Sep 2008 03:07:07 -0700, Blade370 wrote: I have a workbook with multiple spreadsheets from E73 to I91. On each sheet I have a total that I would like to copy to a summary sheet. I have pasted the first formula into the summary sheet which is ='E64'!$K23$. I now want to copy this formula down with the sheet reference incrementing by one e.g the next one needs to be ='E65'!$K23$ and the next ='E66'!$K23$ and so on. What is the best and easiest way to do this??? Many thanks in advance |
All times are GMT +1. The time now is 10:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com