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

[tripflex]

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!!

 

[Elkar]

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

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

HTH
Elkar

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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