SUMIFS #REF error once macro executed

[davidhall]

I have a sumifs formula in sheet 2 where the sum range and criteria ranges are in sheet1. The criteria are on sheet 2. When I initially input the formula on sheet 2, it works fine, however, when I run a certain macro, the formula messes up.

The purpose of the macro is to format things correctly on sheet1 when I import new data. So, I import new data on sheet1, execute the macro, the data is formatted correctly so the referenced material in the sumifs formula can refer to the correct data.

Not sure why its giving me an error.

It looks like this originally = sumifs(Sheet1!G:G,Sheet1!,Indirect("Sheet2!A1"),Sheet1!A:A,Indirect("Sheet2!A2"))

The columns highlighted in red are showing the #REF once the macro is run. The reason why I put the entire column as the reference area is because the data I import varies on how far down it is.

Any suggestions?

 

[VoG]

Please post your macro code.

 

[wigi]

Does you macro remov certain rows and/or columns? Those you use in the SUMIFS?

 

[davidhall]

Yes, it does remove columns on sheet1 which are referenced by the sumifs formula.

 

[wigi]

Yes, it does remove columns on sheet1 which are referenced by the sumifs formula.

Then you know the source of you REF errors...

Can you set the SUMIFS function again in code, AFTER the macro has run?

 

[davidhall]

Yeah, I guess I could do that. Are there no ways to prevent the #REF with the Sumifs formula when columns get deleted. I have made the columns an absolute reference and that doesn't really make a difference after the macro executes.

 

[davidhall]

I'm able to do this formula with a vlookup and a named range prior to the macro running. However, the same issue occurs. Is there no workaround with these formulas when columns get deleted?

 

[wigi]

Which columns do you delete in the macro?

 

[davidhall]

I delete several including A and G which the sumifs formula originally references. After the macro executes, the proper data is columns A and G.

 

[wigi]

Sorry to be thick on this, but I do not understand why you delete columns on that formulas reference. What is the logic in fact?

Probably we need to know this to present a workaround, because if you do not want to set the same SUMIFS formula after the macro runs (my previous suggestion) then it will always give you the REF errors. Unless you use other formulas; try an INDEX function with a number for the column. The big disadvantage is that INDEX is a volatile function. But it might work.