Advance formula 3 columns

[Kim B]

Hi all. I need help with adjusting every three columns for cell ref below. Everything else in the formula stays the same


Starting in column O, the formula is copied into R and then U and so on.
=SUMIFS('Paste CSV File Here 1-9'!$D:$D,'Paste CSV File Here 1-9'!$A:$A,'by Store Data Pull 1-9'!O$4,'Paste CSV File Here 1-9'!$B:$B,'by Store Data Pull 1-9'!$A6)

needs to read when copied to column R

=SUMIFS('Paste CSV File Here 1-9'!$D:$D,'Paste CSV File Here 1-9'!$A:$A,'by Store Data Pull 1-9'!R$4,'Paste CSV File Here 1-9'!$B:$B,'by Store Data Pull 1-9'!$A6)


Starting in column P, the formula is copied into S and so on.
=SUMIFS('Forecast 1-9'!$D:$D,'Forecast 1-9'!$A:$A,'by Store Data Pull 1-9'!P$4,'Forecast 1-9'!$B:$B,'by Store Data Pull 1-9'!$A6)

needs to read when copied to column S

=SUMIFS('Forecast 1-9'!$D:$D,'Forecast 1-9'!$A:$A,'by Store Data Pull 1-9'!S$4,'Forecast 1-9'!$B:$B,'by Store Data Pull 1-9'!$A6)

Thanks for any help

 

[Kim B]

What is the specific error message?

compile error

 

[dreid1011]

compile error

Can you take a screenshot of the error post it please?

 

[Kim B]

Can you take a screenshot of the error post it please?

 

[dreid1011]

When you copied the code, you missed the "End Sub" line at the bottom. Either recopy the entire code, or add that line in after line "Application.ScreenUpdating = True".

 

[Kim B]

When you copied the code, you missed the "End Sub" line at the bottom. Either recopy the entire code, or add that line in after line "Application.ScreenUpdating = True".

Sorry about that. I corrected the code but it takes really long to run and I can't tell if it bombed. Can we take forecast ref out and what if we tell it to start at column O. That is where the paste csv formula starts.

 

[dreid1011]

I'm surprised setting calculations to manual didn't help. I was also having issues with it running for quite a long time, sometimes even after it actually finished it just kept "running". When I added the change to calculations, it finished within seconds. Granted, I was only testing on 7 sheets instead of 30-31. I still expected it to finish much quicker for you. I really don't think the forecast sheet is making any difference, but we can try. And I am not sure what you want by starting at column O as it is already starting there.

Something else is going on for it to be not be getting any better with the changes made. Perhaps if you can upload your sheet to a file share, I could take a look at it when I get home. At this point, I am out of ideas to help, and the issue could entirely be on your side somewhere outside of the code.

 

[Kim B]

I'm surprised setting calculations to manual didn't help. I was also having issues with it running for quite a long time, sometimes even after it actually finished it just kept "running". When I added the change to calculations, it finished within seconds. Granted, I was only testing on 7 sheets instead of 30-31. I still expected it to finish much quicker for you. I really don't think the forecast sheet is making any difference, but we can try. And I am not sure what you want by starting at column O as it is already starting there.

Something else is going on for it to be not be getting any better with the changes made. Perhaps if you can upload your sheet to a file share, I could take a look at it when I get home. At this point, I am out of ideas to help, and the issue could entirely be on your side somewhere outside of the code.

I just closed out of all excell workbooks and walked away. I came back 9 minutes later and it worked! So watched pot never boils. thank you, thank you thank you

 

[dreid1011]

You're welcome, and happy to help.