Need Workaround Help- New Table Features Mess Up Formula-Named Columns

[DDRA Steampunk]

Hi I usually use a formula that automatically updates the column header to a new number when I insert new columns into my insanely huge spreadsheet. With the new table autoformats I can no longer get this to work. Rather than hand number 50 columns, does anyone know how I might get around this? I could "untable" the data, but I actually use the sort feature quite frequently so I prefer to keep it. The issue really is that I need column A sortable. It's the item name with the list of ingredients in the rows. Column B is headed with recipe number (just 1, 2, 3, etc) and the costs per weight used of each ingredient (it looks up from another table) in the rows. The A B pattern repeats for 50 recipes in one table so I can get a total number of ingredients and costs row per recipe with a grand total at the end of the page. Any suggestions very much appreciated.

 

[Yongle]

Have you tried "untabling", selecting any cell in the range, and then using Data \Filter ?
Data Filter essentially achieves much the same thing without using a structured table
It would free your column headings from the constraints of a structured table

 

[DDRA Steampunk]

Have you tried "untabling", selecting any cell in the range, and then using Data \Filter ?
Data Filter essentially achieves much the same thing without using a structured table
It would free your column headings from the constraints of a structured table

That's the only solution I found so far, but it takes a lot more work to get the results and the other people who use the sheets are shall we say "not technically inclined" so I'm trying to get the least amount of work for all of us. Usually I make crazy huge very automated sheets that others only have to click radial buttons or do very basic data entry in. These are the kind of people you have to lock cell data because of XD

 

[Yongle]

What are the header values for columns A to H ?

 

[DDRA Steampunk]

The 1st column type is headed the name of the recipe alphabetically left to right, the second column type is just a number (1 to 50). The reason I prefer just a number for 2nd column type is so the header is as narrow as possible while still displaying the price data. The recipe name is so it can pull the individual ingredient names and prices used from other columns and auto generate the menu listings in the Word doc. The price column generates my cost report and the sell prices with the markups and labor. It also let's me quickly orient to where I am in the sheet since "things that start with M" is like 25 percent of the page. The names of the recipes actually get changed sometimes (for theme events and such). I can say "6 of number 10" and everyone is on the same page. The idea of the sortable table appeals because an assistant can go get the cost, pull the money, get it for me very quickly without knowing Excel.

 

[DDRA Steampunk]

In short:
Ingredient Name
Ingredient MOQ
Cost per MOQ
ML per MOQ
Cost per ML
Recipe Name 1 (data is ML used)
1 (data is Cost/ML * ML used)

 

[Yongle]

So ...
A Ingredient Name
B Ingredient MOQ
C Cost per MOQ
D ML per MOQ
E Cost per ML
F Recipe Name 1 (data is ML used)
G 1 (data is Cost/ML * ML used)

Are the next few columns as follows?
H Recipe Name 2 (data is ML used)
I 2
J Recipe Name 3 (data is ML used)
K 3
etc

If that is correct, the easiest way would be VBA that would simply relabel every other column from columns F (run from a button whenever required)
Let me know if that is of interest. Will update thread tomorrow

 

[DDRA Steampunk]

So ...
A Ingredient Name
B Ingredient MOQ
C Cost per MOQ
D ML per MOQ
E Cost per ML
F Recipe Name 1 (data is ML used)
G 1 (data is Cost/ML * ML used)

Are the next few columns as follows?
H Recipe Name 2 (data is ML used)
I 2
J Recipe Name 3 (data is ML used)
K 3
etc

If that is correct, the easiest way would be VBA that would simply relabel every other column from columns F (run from a button whenever required)
Let me know if that is of interest. Will update thread tomorrow

VBA should work fine, thanks so much I'm pretty new to VBA, I just adapt codes from books and web forums. I was finally getting ready to go take the actual classes when, oops, zombie apocalypse scenario.

 

[Yongle]

Test this on a COPY of your workbook!
It is likely the code will not do what you want if your table layout is diifferent to the one below
The code refers to the first table in the active sheet and overwrites alternate headers beginning with the 7th column in the table
To avoid the risk of duplicate values being rejected by Excel, those headers are initially replaced by the current value of Timer
Timer is the time in seconds since midnight which is (microscopically) later when each column is updated
The values are then overwitten with desired values 1 , 2 , 3 etc

Before

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Ingredient Name	Ingredient MOQ	Cost per MOQ	ML per MOQ	Cost per ML	Recipe Name 1	25	Recipe Name 2	1	Recipe Name 3	2	Recipe Name 4	3
Big Table

After

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Ingredient Name	Ingredient MOQ	Cost per MOQ	ML per MOQ	Cost per ML	Recipe Name 1	1	Recipe Name 2	2	Recipe Name 3	3	Recipe Name 4	4
Big Table

Sub ReplaceTableHeaders()
    Dim tbl As ListObject, c As Long, x As Long, z As Long
    Set tbl = ActiveSheet.ListObjects(1)
    z = tbl.ListColumns.Count
'replace headers with value that cannot repeat
    For c = 7 To z Step 2
        tbl.Range.Cells(1, c).Value = Timer
    Next c
'replace headers with desired value
    For c = 7 To z Step 2
        x = x + 1
        tbl.Range.Cells(1, c).Value = x
    Next c
End Sub

 

[DDRA Steampunk]

Thanks so much, I will test it out and let you know ?