Reference a cell from a another sheet that is always 24 rows away

[rhhince]

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.

 

[Chip Pearson]

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 (").

 

[Chip Pearson]

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)

 

[T. Valko]

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

 

[Arvi Laanemets]

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.

 

[Chip Pearson]

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)