HOW TO ADD INDIRECT TO SUMIF - INDIRECT Required due to the insertion of new columns changing the SUMIF formua

[Ellie456]

Hi,

I have formulas in worksheet 1 that pulls it's values in from worksheet 2. When instering a column into worksheet 2 my formulas change due to the insertion of the additional column so absolute referencing hasn't helped in my situation as I want to keep the formulas AS IS due to new data being added each day.

My formula is as follows:

SUMIF('worksheet2'!C3:L3,worksheet1'!C10,'worksheet2'!C18:L18)/2

Can someone please show me how I would add INDIRECT to this formula. I have lots of other formulas but if someone can help with this formula I am thinking I should be able to update the rest. I've tried to find a solution but haven't had any luck, mainly because I am not understanding the INDIRECT function but it seems it's the only solution to my problem due to the insertion of new columns in my dataset

Any help would be greatly appreciated.

 

[tyija1995]

1) If you insert a column in A,B or C then the formula will increment to D3:M3 (which makes sense)
2) If you insert a column between D & L then the formula will increment to C3:M3
3) If you insert a column after L then no change will be made to the formula.

What would you want the formula to look at if 1) and 2) occurred?

 

[FormR]

Hi, here is an option that is impervious to column insertions.

=SUMIF(INDEX('worksheet2'!3:3,0,3):INDEX('worksheet2'!3:3,0,12),'worksheet1'!C10,INDEX('worksheet2'!18:18,0,3):INDEX('worksheet2'!18:18,0,12))/2

 

[Ellie456]

Hi,

My workbook has some VBA, the VBA inserts a new column with the new daily values into column C each day hence the range shift within my current formula
My VBA also then deletes a column towards the end of the table which is column TT which doesn't show in this formula but does in others.

In answer to your question:

I will always need the formula to reference

C3:L3.
In C3:L3 I have the day's of the week, so if C10 equals let's say Friday then I want all Friday values from C18:L18 to be picked up​

 

[Ellie456]

Hi, here is an option that is impervious to column insertions.

=SUMIF(INDEX('worksheet2'!3:3,0,3):INDEX('worksheet2'!3:3,0,12),'worksheet1'!C10,INDEX('worksheet2'!18:18,0,3):INDEX('worksheet2'!18:18,0,12))/2

Amazing, this works perfectly thank you so much!!

How does it work, exactly? I'd like to try and change the rest of the formulas that also reference worksheet 2 but are slightly different to the current SUMIF, any additional help you can give is much appreciated

 

[Ellie456]

Have worked out the 0,3 and 0,12 which was what I wasn't sure about and once I understood that I could see how the rest of the formula was working.

Thanks again for your help! I spent almost all day trying to find way's to fix this insert column issue and you sorted this for me pretty much instantly.

 

[Ellie456]

Amazing, this works perfectly thank you so much!!

Can you tell me how I would be able to lock the below formula as well in my workbook so the insert column doesn't change the formula?

='worksheet2'!F17​

 

[Ellie456]

Hi, here is an option that is impervious to column insertions.

=SUMIF(INDEX('worksheet2'!3:3,0,3):INDEX('worksheet2'!3:3,0,12),'worksheet1'!C10,INDEX('worksheet2'!18:18,0,3):INDEX('worksheet2'!18:18,0,12))/2

Can you tell me how I would be able to lock the below formula as well in my workbook so the insert column doesn't change the formula?

='worksheet2'!F17​

 

[Ellie456]

Can you tell me how I would be able to lock the below formula as well in my workbook so the insert column doesn't change the formula?

='worksheet2'!F17​

Have worked it out

='INDEX(worksheet2'!17:17,0,3)​