Change source tables in not harmful way for formulas

[jaryszek]

hi Guys,

i have model where i am importing csv with power query from java bundle service (some kind of server).

And now i have formulas in workbook with index, match and refering to specific columns.

But when i will change the TableName i will get N/A. Because reference changed.
Looking for a way to change source table name and update all formulas referencing to.

than you for help,
hope somebody figure this out better while designing workbook

Best,
Jacek

 

[Alex Blakenburg]

But when i will change the TableName i will get N/A

Did you actually try it ?
I tried formulas on the same sheet and also on another sheet, referencing columns in the table.
Neither had any problem with changing the table name, the formulas simply updated to the new name.

Do you want to provide some specific examples, ideally using XL2BB ?

 

[jaryszek]

thank you very much,

sorry i investigated once again and only thing i have with indirects formulas.

So some of indirects i hid in named manager (they are not harmful there) so what i need now is to go and change formulas.
Sometimes i have code which deleting table first and recreating from scracth and because of this i had to use indirects.

But the downtown is that indirect is not updatable automatically. And generally i do not know how to replace it.
Only loop and replace with text functions will do the job?

Best,
Jacek

 

[jaryszek]

So generally topic should be: "How to replace indirects formulas to new tables and column names".

Best,
Jacek

 

[Alex Blakenburg]

Can you show us an example of the formulas that are not updating.

 

[jaryszek]

How can i add here there is no place for attachments.

 

[Alex Blakenburg]

Sample data can be included by using the XL2BB AddIn Tool. The standard instruction links are below.
If you want to share a workbook it will need to be done through one of the sharing platforms eg Dropbox, OneDrive, GoogleDrive. The permissions would need to be anyone with the link and then post the link here

XL2BB
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.