Need consistent column reference

[RodneyC]

I had a similar post yesterday, but it wasn't a SUMIFS statement and I can't get that solution to work here. I'm using this code to pull data from another tab

=SUMIFS(INDEX('App Starts'!$A$2:$S$490,,6), 'App Starts'!$A$2:$A$490, $A3)

It pulls from the 6th column, column F. Each month I add a new column between E and F pushing previous months data into G making room for the current months data. Doing so automatically changes the "6" in the formula above to a "7". I need it to always pull from the 6th column. How do I modify my formula to do so?

Thank you in advance

 

[dave3009]

Hi

I don't think what you've stated is correct. The hard coded 6 wouldn't change to 7 when you insert a single column between E and F, the formula would just point to the newly inserted column F.

Do you mean that you always want the formula to follow what was in column 6 so that effectively the INDEX points at column 7, then 8 etc...

Cheers, Dave

 

[RodneyC]

Hi

I don't think what you've stated is correct. The hard coded 6 wouldn't change to 7 when you insert a single column between E and F, the formula would just point to the newly inserted column F.

Do you mean that you always want the formula to follow what was in column 6 so that effectively the INDEX points at column 7, then 8 etc...

Cheers, Dave

This formula:

=SUMIFS(INDEX('App Starts'!$A$2:$S$490,,6), 'App Starts'!$A$2:$A$490, $A3)

is currently in column B on my destination tab. If I go to the App Starts (my source tab) tab and add a column between E and F, it adds a column on my destination tab throwing off other calculations. I need everything on my destination tab to stay static. Column B from my destination tab should always read column F from my source tab (App Starts) regardless of how many columns I add. Said another way, I don't want the column to follow the original data, I want it to report the new data. Column B will always read from column F and so on... C will always read from G, D will always read from H etc. It's a flowing 13 month reporting.

 

[dave3009]

If you have this formula in column B of your destination sheet:

=SUMIFS(INDEX('App Starts'!$A$2:$S$490,,6), 'App Starts'!$A$2:$A$490, $A3)

It will sum column F of App Start sheet based in column A of App Start sheet based on A3 of your destination sheet.

If you then add a column between column E and F your formula will be:

=SUMIFS(INDEX('App Starts'!$A$2:$T$490,,6), 'App Starts'!$A$2:$A$490, $A3)

The formula will still sum column F of App Start sheet based in column A of App Start sheet based on A3 of your destination sheet. You could add as many columns as you like up to the column limit (which is a lot) and the formula will still perform exactly that sum, even though the width of the reference will grow!

 

[RodneyC]

If you have this formula in column B of your destination sheet:

=SUMIFS(INDEX('App Starts'!$A$2:$S$490,,6), 'App Starts'!$A$2:$A$490, $A3)

It will sum column F of App Start sheet based in column A of App Start sheet based on A3 of your destination sheet.

If you then add a column between column E and F your formula will be:

=SUMIFS(INDEX('App Starts'!$A$2:$T$490,,6), 'App Starts'!$A$2:$A$490, $A3)

The formula will still sum column F of App Start sheet based in column A of App Start sheet based on A3 of your destination sheet. You could add as many columns as you like up to the column limit (which is a lot) and the formula will still perform exactly that sum, even though the width of the reference will grow!

Those two formulas appear to be identical, unless I'm missing something with my old eyes.

The formula should ALWAYS stay in column B. As it is now, if I add a column on the App Starts tab, Excel adds a column on my destination tab, moving the formula from column B to column C. I need it to stay in column C.

Thank you for your continued help.

 

[dave3009]

If you add a single column between E and F on your App Start sheet then the only change will be the reference which will have its right most column reference increment by one column, so S to T, next time T to U and so on. Adding a column between columns E and F will not affect the left most column of the reference (column A) in the formula you have provided as you are inserting and pushing to the right.

You need to provide a clearer example of whats happening as you have provided a formula which will not be affected by the actions you describe taking in your original post, now we're a few posts in you are talking about a completely separate set of columns and your description is inconsistent as you state it should always be in column B but you want it to stay in column C!?

 

[RodneyC]

If you add a single column between E and F on your App Start sheet then the only change will be the reference which will have its right most column reference increment by one column, so S to T, next time T to U and so on. Adding a column between columns E and F will not affect the left most column of the reference (column A) in the formula you have provided as you are inserting and pushing to the right.

You need to provide a clearer example of whats happening as you have provided a formula which will not be affected by the actions you describe taking in your original post, now we're a few posts in you are talking about a completely separate set of columns and your description is inconsistent as you state it should always be in column B but you want it to stay in column C!?

I truly appreciate your continued help. Perhaps the image below will help me explain it. In the App Starts tab, original state, Dec 21 is in column F. The formula in my Destination tab is in column B and it pulls the data correctly into that column. In the second set of screen shots, I have added a column between E and F, creating a new column F and pushing the old column F into column G. the new column F will contain Jan 22 data and I will continue this process every month. Inserting this new column F in the App Starts tab simultaneously created a new column B in the destination tab. This causes problems with other spreadsheets my destination tab writes to. I need column B from the destination tab to read from column F in the App Starts tab. I don't want it to create a new column B. THANK YOU AGAIN