Have I found an excel bug?

[kyrbi]

Hello all

I have the following urgent problem:

Cell A1: ='[logESM2.xls]per dag'!$D670
Cell A2: ='[logESM2.xls]per dag'!$D677
Cell A3: ='[logESM2.xls]per dag'!$D684

The formulas are pointing to another document with daily values in column
format.
I want to have all values from monday, tue... in another document (every 7th
row).
Very simple I guess(ed).....
When I copy the cells, excel doesn't count per 7 but makes very strange
jumps to other cells:

A4='[logESM2.xls]per dag'!$D673
A5='[logESM2.xls]per dag'!$D680
A6='[logESM2.xls]per dag'!$D687
A7='[logESM2.xls]per dag'!$D676

I need 691, 698 etc ....

Is this a bug?

thanks a lot for any help on this!

 

[Bernie Deitrick]

kyrbi,

Not a bug. That is how formulas are copied.

In cell A1, use the formula

=INDEX('[logESM2.xls]per dag'!$D:$D,670+(ROW()-1)*7)

and copy down as far as you need....

HTH,
Bernie
MS Excel MVP

 

[Niek Otten]

This is not a bug, this is specified behavior of Excel.
A4 is 3 lines below A1, do the references in the formula are increased by three.

Use the ROW() function multiplied by 3 and the INDIRECT() function to get this done.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello all
|
| I have the following urgent problem:
|
| Cell A1: ='[logESM2.xls]per dag'!$D670
| Cell A2: ='[logESM2.xls]per dag'!$D677
| Cell A3: ='[logESM2.xls]per dag'!$D684
|
| The formulas are pointing to another document with daily values in column
| format.
| I want to have all values from monday, tue... in another document (every 7th
| row).
| Very simple I guess(ed).....
| When I copy the cells, excel doesn't count per 7 but makes very strange
| jumps to other cells:
|
| A4='[logESM2.xls]per dag'!$D673
| A5='[logESM2.xls]per dag'!$D680
| A6='[logESM2.xls]per dag'!$D687
| A7='[logESM2.xls]per dag'!$D676
|
| I need 691, 698 etc ....
|
| Is this a bug?
|
| thanks a lot for any help on this!
|
|

 

[kyrbi]

Not a bug. That is how formulas are copied.

In cell A1, use the formula

=INDEX('[logESM2.xls]per dag'!$D:$D,670+(ROW()-1)*7)

and copy down as far as you need....

HTH,
Bernie
MS Excel MVP


Ok, i'll give it a try by using your formula but I don't understand the
logic in my results

 

[Bernie Deitrick]

ROW() returns the number of the row where the formula resides. ROW() in cell A1 returns 1 (1-1)* 7
is 0, so the INDEX function will return the 670th value in column D, or cell D670. In cell A2,
ROW() returns 2, so (2-1)*7 is 7, and the INDEX function will return the 677th value in column D, or
D677....and so on....

HTH,
Bernie
MS Excel MVP

 

[Guest]

You have your answers, but if you want to understand why it does this, then
look up 'absolute vs relative referencing'