adding to a cell reference

[binaryfingers]

Hi

Is there a way to add a number to a cell reference.

I have the following reference on a summary sheet that I would like to increment by 20

='Total Sheet'!D858/1000000

I would like to increment the D858 to D878 then D898

Is there a way to do this?

Thanks,

 

[xlbo]

have a look at the INDIRECT function

=INDIRECT("'Total Sheet'!D" & 858 + 20) /1000000


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[binaryfingers]

Thanks Xlbo

I had been playing around with the indirect function, but I didnt quite get the structure right, which is why it wouldnt return the right result..

Thanks,

 

[GlennUK]

Which cell do you want the formula for
='Total Sheet'!D858/1000000
to appear?
And the next cells should be the ones following that one, downwards?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[macropod]

Hi binaryfingers,

If what you want to achieve is have a formula automatically retrieve every 20th row from the source range, you could try something like:
=OFFSET('Total Sheet'!$D$858,(ROW()-1)*20,0)/1000000
and copy down as far as needed for a vertical display, or
=OFFSET('Total Sheet'!$D$858,0,(COLUMN()-1)*20)/1000000
and copy right as far as needed for a horizontal display.

In both cases, change the '-1' to your output's starting row or column number.



[MS MVP - Word]