=ROUND((ROW()-10)!G$66,L$3) ... what's the error?
 

Okay so an older spreadsheet i have had the code as this:

=ROUND('2'!G$66, L$3)

Basically the 2 is referencing the sheets because they are numbered 1-100.
Anyways, i want to try and get the number automatically instead of having to
enter each one. So i figured the title one would work but i can't seem to
figure out how to integrate all those together.

Because in an easy way to explain it '2' in the old code could also be
represented as ROW()-10, and so on for every other cell, but when i try and
put it into the code instead of the '2' it doesn't work.

Maybe i'm just missing something here...

Thanks a ton guys!!

=ROUND((ROW()-10)!G$66,L$3) ... what's the error?
 

You'll need to use the INDIRECT function for this:

=ROUND(INDIRECT(ROW()-10&"!G$66"),L$3)

HTH
Elkar



"tripflex" wrote:

Okay so an older spreadsheet i have had the code as this:

=ROUND('2'!G$66, L$3)

Basically the 2 is referencing the sheets because they are numbered 1-100.
Anyways, i want to try and get the number automatically instead of having to
enter each one. So i figured the title one would work but i can't seem to
figure out how to integrate all those together.

Because in an easy way to explain it '2' in the old code could also be
represented as ROW()-10, and so on for every other cell, but when i try and
put it into the code instead of the '2' it doesn't work.

Maybe i'm just missing something here...

Thanks a ton guys!!

=ROUND((ROW()-10)!G$66,L$3) ... what's the error?
 

On Mon, 9 Mar 2009 11:59:03 -0700, tripflex
wrote:

Okay so an older spreadsheet i have had the code as this:

=ROUND('2'!G$66, L$3)

Basically the 2 is referencing the sheets because they are numbered 1-100.
Anyways, i want to try and get the number automatically instead of having to
enter each one. So i figured the title one would work but i can't seem to
figure out how to integrate all those together.

Because in an easy way to explain it '2' in the old code could also be
represented as ROW()-10, and so on for every other cell, but when i try and
put it into the code instead of the '2' it doesn't work.

Maybe i'm just missing something here...

Thanks a ton guys!!

You aren't properly forming the string

The easiest way is to use the Address function:

Something like:

=AVERAGE(INDIRECT(ADDRESS(66,COLUMN($G$1),,,ROW()-10)),INDIRECT(ADDRESS(3,COLUMN($L$1),,,ROW()-10)))

--ron

=ROUND((ROW()-10)!G$66,L$3) ... what's the error?
 

On Mon, 09 Mar 2009 15:19:15 -0400, Ron Rosenfeld
wrote:

On Mon, 9 Mar 2009 11:59:03 -0700, tripflex
wrote:

Okay so an older spreadsheet i have had the code as this:

=ROUND('2'!G$66, L$3)

Basically the 2 is referencing the sheets because they are numbered 1-100.
Anyways, i want to try and get the number automatically instead of having to
enter each one. So i figured the title one would work but i can't seem to
figure out how to integrate all those together.

Because in an easy way to explain it '2' in the old code could also be
represented as ROW()-10, and so on for every other cell, but when i try and
put it into the code instead of the '2' it doesn't work.

Maybe i'm just missing something here...

Thanks a ton guys!!

You aren't properly forming the string

The easiest way is to use the Address function:

Something like:

=AVERAGE(INDIRECT(ADDRESS(66,COLUMN($G$1),,,ROW ()-10)),INDIRECT(ADDRESS(3,COLUMN($L$1),,,ROW()-10)))

--ron

oops, that should have been

=AVERAGE(INDIRECT(ADDRESS(66,COLUMN($G$1),,,ROW()-10)),L$3)
--ron