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

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

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

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

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