Thread: HOW TO ADD INDIRECT TO SUMIF - INDIRECT Required due to the insertion of new columns changing the SUMIF formua Thanks: 0 Likes:  1 Post #5303939 (1)

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

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.  Reply With Quote

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

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?  Reply With Quote

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

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

Code:
=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  Reply With Quote

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

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.

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  Reply With Quote

5. Re: HOW TO ADD INDIRECT TO SUMIF - INDIRECT Required due to the insertion of new columns changing the SUMIF formua Originally Posted by FormR Hi, here is an option that is impervious to column insertions.

Code:
=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  Reply With Quote

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

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.  Reply With Quote

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

[QUOTE=Ellie456;5303979]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  Reply With Quote

8. Re: HOW TO ADD INDIRECT TO SUMIF - INDIRECT Required due to the insertion of new columns changing the SUMIF formua Originally Posted by FormR Hi, here is an option that is impervious to column insertions.

Code:
=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  Reply With Quote

9. Re: HOW TO ADD INDIRECT TO SUMIF - INDIRECT Required due to the insertion of new columns changing the SUMIF formua Originally Posted by 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)  Reply With Quote

User Tag List

Tags for this Thread

due, excel, formulas, indirect, worksheet  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•