Excel Tables - Insert New Column Changes Table Formulas Instead Of Keeping Them

moxy85

Board Regular
Joined
Nov 25, 2009
Messages
57
Such a strange issue I find myself with this morning when working with tables

So I have two tables, connected and merged into one 'merged table' using a query to produce one full table of all the data.
In the merged table, I have appended columns to calculate fields within the same table.
If I add a field/column to either of the tables, the connected table elongates as expected, however the named formulas within the merged table change to reflect what is now sitting in the previous column position.

Sample Data

[Anticipated_Cashflow]
Project Name​
2020​
2021​
2022​
Project 1​
£50000​
£100000​
£50000​
Project 2​
£0​
£50000​
£100000​
Project 3​
£200000​
£200000​
£200000​


[ProjectData]
Project Name​
Apples​
Bananas​
Pears​
Project 1
10%​
50%​
40%​
Project 2
100%​
0%​
0%​
Project 3
20%​
30%​
50%​


The connected query producing the merged table then has all the above data in one table.

I've then appended column(s) to the right to calculate:
[Merged_Table]
Project NameMultiple columns
(Cashflow to/and Product Data Entries)
2020-Apples2020-Bananas2020-Pears
Project 1~=[@2020]*[@Apples] = £5000=[@2020]*[@Bananas] = £25000=[@2020]*[@Pears] = £20000
Project 2~=[@2020]*[@Apples] = £0=[@2020]*[@Bananas] = £0=[@2020]*[@Pears] = £0
Project 3~=[@2020]*[@Apples] = £40000=[@2020]*[@Bananas] = £60000=[@2020]*[@Pears] = £100000

At this point all is working well, exactly as exptected.

Now, If I insert a column into [ProjectData] table, the merged table extends to incorporate the new column of data, however the pre-existing table formulas are not preseverd and retain the cell locations where the data was before.

[Merged_Table]
Project NameStatusMultiple columns
(Cashflow to/and Product Data Entries)
2020-Apples2020-Bananas2020-Pears
Project 1Tendering~=[@2021]*[@Bananas] =[@2021]*[@Pears] =
Project 2Tendering~=[@2020]*[@Bananas] =[@2021]*[@Pears] =
Project 3Tendering~=[@2020]*[@Bananas] =[@2021]*[@Pears] =

Obvisouly the results are completely different, but its not noticeable at first glance as the formulas return results. Its only under inspection that the formulas are incorrect.

I don't understand why the formulas are changing and not retaining their formulas.
Its like I want to 'absolute' the table formula, but this isn't a possibility.

Perhaps I'm just going about this all wrong lol.

Let me know your thoughts please.

Moxy
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,483
Messages
5,770,361
Members
425,612
Latest member
martinijr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top