Dragging formula across multiple cells, but value only changing 1 at a time

[Babynod]

i have 5 columns for each day set up for my daily reporting. theres formulas in a few of the columns, i know if i drag a cell with a formula without locking it the value changes by 1. but when i try and drag 5 at once it changes the value by 5 places.

E.G in Cell ACR:9 it says 10-lines picked. this is pulling from another spreadsheet from IL:20

now when I highlight all 5 columns to drag them across, my ACW:9 cell I want to pull from IM20, but instead it goes to IQ20 because that's 5 columns across.

i need to copy this for everyday until the end of the financial year, and redoing it each time is out of the question

 

[Saba Sabaratnam]

Hi

You may have to use INDIRECT function to solve your problem.

I have the above data in Data tab,

I use this formula to get each of value in fifth columns using the following formula.

=INDIRECT(ADDRESS(1,INT((COLUMN(A:A)-1)/5)+1,,,"data"))

See the result below,

see the formulas below.

Kind regards

Saba

 

[Peter_SSs]

I would avoid the volatile function INDIRECT and use this in ACR9.

=INDEX('[1. Operations Dashboard.xlsx]Combine'!$IL20:$XFD20,(COLUMNS($ACR:ACR)+4)/5)&" - Lines Picked"

 

[Babynod]

I would avoid the volatile function INDIRECT and use this in ACR9.

=INDEX('[1. Operations Dashboard.xlsx]Combine'!$IL20:$XFD20,(COLUMNS($ACR:ACR)+4)/5)&" - Lines Picked"

Hi Peter, would you be able to break this down and explain how it works?

 

[Peter_SSs]

Hi Peter, would you be able to break this down and explain how it works?

Sure. It revolves around the fact that your columns are dragged in groups of 5.

The first formula is in column ACR where ..
(COLUMNS($ACR:ACR)+4)/5 = (1+4)/5 = 1
so the formula returns the value from cell no 1 of the $IL20:$XFD20 range. That is, it returns the value from IL20

When the 5 columns are dragged, the next occurrence of this formula is in column ACW where the blue part above will have changed to ..
(COLUMNS($ACR:ACW)+4)/5 = (6+4)/5 = 2
so the formula returns the value from cell no 2 of the $IL20:$XFD20 range. That is, it returns the value from IM20

The next time this formula appears it is in column ADB where the blue part has become
(COLUMNS($ACR:ADB)+4)/5 = (11+4)/5 = 3
so the formula returns the value from cell no 3 of the $IL20:$XFD20 range. That is, it returns the value from IN20

etc

 

[Babynod]

Sure. It revolves around the fact that your columns are dragged in groups of 5.

The first formula is in column ACR where ..
(COLUMNS($ACR:ACR)+4)/5 = (1+4)/5 = 1
so the formula returns the value from cell no 1 of the $IL20:$XFD20 range. That is, it returns the value from IL20

When the 5 columns are dragged, the next occurrence of this formula is in column ACW where the blue part above will have changed to ..
(COLUMNS($ACR:ACW)+4)/5 = (6+4)/5 = 2
so the formula returns the value from cell no 2 of the $IL20:$XFD20 range. That is, it returns the value from IM20

The next time this formula appears it is in column ADB where the blue part has become
(COLUMNS($ACR:ADB)+4)/5 = (11+4)/5 = 3
so the formula returns the value from cell no 3 of the $IL20:$XFD20 range. That is, it returns the value from IN20

etc

$IL20:$XFD20
XFD column, is that the very end? ive never seen it go that high before
so the range is from IL20 to XFD20 (or the last column of my range)
so with your ACR)+4)/5
ACR + 4 takes me to ACV not ACW
what is the /5 do?

 

[Peter_SSs]

$IL20:$XFD20
XFD column, is that the very end? ive never seen it go that high before

I used the last column because you gave no indication how far across your data goes in '[1. Operations Dashboard.xlsx]Combine'! or how many groups of 5 columns that you would end up dragging across in the formula sheet. You can use a column less than XFD so long as it is far enough to cover your actual data in '[1. Operations Dashboard.xlsx]Combine'!
Having said that, there is no real advantage in making it a smaller range.

so with your ACR)+4)/5
ACR + 4 takes me to ACV not ACW

No, I did not have ACR)+4)/5, I had (COLUMNS($ACR:ACR)+4)/5
The first part evaluated is the red part. How many columns are there from $ACR:ACR? The answer to that is 1 so the calculation becomes (1+4)/5 which gives 5/5 which gives 1

So the original formula
=INDEX('[1. Operations Dashboard.xlsx]Combine'!$IL20:$XFD20,(COLUMNS($ACR:ACR)+4)/5)&" - Lines Picked"
becomes
=INDEX('[1. Operations Dashboard.xlsx]Combine'!$IL20:$XFD20,1)&" - Lines Picked"

As mentioned earlier the bold part therefore returns the value from cell no 1 from the IL20:XFD20 range. That is, from IL20

 

[Babynod]

I used the last column because you gave no indication how far across your data goes in '[1. Operations Dashboard.xlsx]Combine'! or how many groups of 5 columns that you would end up dragging across in the formula sheet. You can use a column less than XFD so long as it is far enough to cover your actual data in '[1. Operations Dashboard.xlsx]Combine'!
Having said that, there is no real advantage in making it a smaller range.


No, I did not have ACR)+4)/5, I had (COLUMNS($ACR:ACR)+4)/5
The first part evaluated is the red part. How many columns are there from $ACR:ACR? The answer to that is 1 so the calculation becomes (1+4)/5 which gives 5/5 which gives 1

So the original formula
=INDEX('[1. Operations Dashboard.xlsx]Combine'!$IL20:$XFD20,(COLUMNS($ACR:ACR)+4)/5)&" - Lines Picked"
becomes
=INDEX('[1. Operations Dashboard.xlsx]Combine'!$IL20:$XFD20,1)&" - Lines Picked"

As mentioned earlier the bold part therefore returns the value from cell no 1 from the IL20:XFD20 range. That is, from IL20

ye ok i thought that was the reason for XFD

so if i wanted to go to 6 columns instead of 5 i would do (COLUMNS($ACR:ACR)+5)/6?

also some of my columns have have a SUM formula
e.g =SUM('[1. Operations Dashboard.xlsx]AM Shift'!IL111:IL119)

how would that one work?

 

[Peter_SSs]

so if i wanted to go to 6 columns instead of 5 i would do (COLUMNS($ACR:ACR)+5)/6?

Yes

also some of my columns have have a SUM formula
e.g =SUM('[1. Operations Dashboard.xlsx]AM Shift'!IL111:IL119)

1. What cell would that first formula be in?
2. Are we now on a 6 column step or still 5?
3. Exactly what cells do you want the next one of these formulas to sum when it has been dragged across?

 

[Babynod]

Yes


1. What cell would that first formula be in?

ACR38 has my SUM formula in it

2. Are we now on a 6 column step or still 5?

still 5 column

3. Exactly what cells do you want the next one of these formulas to sum when it has been dragged across?

when dragged across the SUM would go from IL111:IL119 to IM111:IM119