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

Babynod

Board Regular
Joined
Aug 10, 2022
Messages
56
Office Version
  1. 365
Platform
  1. Windows
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
1677552360123.png


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.
1677552236779.png



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

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi

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

1677562233233.png


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,

1677562360666.png



see the formulas below.
1677562485961.png


Kind regards

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

Excel Formula:
=INDEX('[1. Operations Dashboard.xlsx]Combine'!$IL20:$XFD20,(COLUMNS($ACR:ACR)+4)/5)&" - Lines Picked"
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
$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
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top