I need to reference a cell from a another sheet that is always 24 rows
away and in same column. i.e.

B52='HOURLY ANALYSIS'!BI1507
B53='HOURLY ANALYSIS'!BI1531

How to I get 'HOURLY ANALYSIS'!BI1531 to change automatically without
changing it manually?
Thanks.

Use the following formula in cell B52

=INDIRECT("'Hourly Analysis'!BI"&507+(ROW()-52)*24)

and fill down as far as you need to go. Be careful with the mix of single
quotes (') and double quotes (").




"rhhince" wrote in message
oups.com...
I need to reference a cell from a another sheet that is always 24 rows
away and in same column. i.e.

B52='HOURLY ANALYSIS'!BI1507
B53='HOURLY ANALYSIS'!BI1531

How to I get 'HOURLY ANALYSIS'!BI1531 to change automatically without
changing it manually?
Thanks.

I misread your cell addresses. Use

=INDIRECT("'Hourly Analysis'!BI"&1507+(ROW()-52)*24)

in cell B52 and fill down as far as you need to go.

This formula can be adapted to read any series of data from colum BI. Change
the 1507 to the first row with source data, 52 to the start row of the
result formulas, and 24 to the interval between the returned values.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Chip Pearson" wrote in message
...
Use the following formula in cell B52

=INDIRECT("'Hourly Analysis'!BI"&507+(ROW()-52)*24)

and fill down as far as you need to go. Be careful with the mix of single
quotes (') and double quotes (").




"rhhince" wrote in message
oups.com...
I need to reference a cell from a another sheet that is always 24 rows
away and in same column. i.e.

B52='HOURLY ANALYSIS'!BI1507
B53='HOURLY ANALYSIS'!BI1531

How to I get 'HOURLY ANALYSIS'!BI1531 to change automatically without
changing it manually?
Thanks.

Here's another one:

=INDEX('Hourly Analysis'!BI$1507:BI$2500,(ROWS($1:1)-1)*24+1)

Copy down as needed. Also, adust for the correct end of the range: BI$2500

Biff

"rhhince" wrote in message
oups.com...
I need to reference a cell from a another sheet that is always 24 rows
away and in same column. i.e.

B52='HOURLY ANALYSIS'!BI1507
B53='HOURLY ANALYSIS'!BI1531

How to I get 'HOURLY ANALYSIS'!BI1531 to change automatically without
changing it manually?
Thanks.

Hi

I´m somewhat unsure, which sheet is the one you enter the values, and which
one has to refer to cells on another sheet.

When you are entering values into sheet 'HOURLY ANALYSIS', then on another
sheet enter just the same formula like the one you did give here, i.e. into
cell B52 you enter the formula
=IF('HOURLY ANALYSIS'!BI1507="","",'HOURLY ANALYSIS'!BI1507)
, and copy it down/up - cell references are adjusted automatically when
copied.

When you enter your values into another sheet, and want 'HOURLY ANALYSIS'
sheet to mirror them, then into 'HOURLY ANALYSIS'!BI1507 enter the formula
=IF('Sheet Name'!BI52="","",'Sheet Name'!BI52)
, and copy it down/up.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"rhhince" wrote in message
oups.com...
I need to reference a cell from a another sheet that is always 24 rows
away and in same column. i.e.

B52='HOURLY ANALYSIS'!BI1507
B53='HOURLY ANALYSIS'!BI1531

How to I get 'HOURLY ANALYSIS'!BI1531 to change automatically without
changing it manually?
Thanks.

Now that I think about it, I prefer the formula

=OFFSET($BI$1507,(ROW()-52)*24,0,1,1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Chip Pearson" wrote in message
...
I misread your cell addresses. Use

=INDIRECT("'Hourly Analysis'!BI"&1507+(ROW()-52)*24)

in cell B52 and fill down as far as you need to go.

This formula can be adapted to read any series of data from colum BI.
Change the 1507 to the first row with source data, 52 to the start row of
the result formulas, and 24 to the interval between the returned values.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Chip Pearson" wrote in message
...
Use the following formula in cell B52

=INDIRECT("'Hourly Analysis'!BI"&507+(ROW()-52)*24)

and fill down as far as you need to go. Be careful with the mix of single
quotes (') and double quotes (").




"rhhince" wrote in message
oups.com...
I need to reference a cell from a another sheet that is always 24 rows
away and in same column. i.e.

B52='HOURLY ANALYSIS'!BI1507
B53='HOURLY ANALYSIS'!BI1531

How to I get 'HOURLY ANALYSIS'!BI1531 to change automatically without
changing it manually?
Thanks.