Excel Tables: How to link a table column header name from another cell?

[EmmaFia]

Hi Mr Excel,

This is my first post so I hope I can give a clear description of the problem.

I am working with Excel Tables in Excel 2013. I have a sheet with several smaller tables for different categories. All tables have the same headers. As I will use the sheet for comparisons I would like to be able to change the column headers in a smart way instead of having to update all manually.

I tried to make an example below. Both Table 1 and Table 2 have the headers McDonalds and PizzaHut and these names are linked to cell A1 and A2 respectively. So if I want to compare with KFC instead I just want to enter KFC in eg. cell A1 and all tables should be updated with this column header instead.

Cell A1: McDonalds
Cell A2: PizzaHut

Table 1: Service
Column headers: Sub-questions, McDonalds (name linked from cell A1), PizzaHut (name linked from cell A2)

Table 2: Food quality
Column headers: Sub-questions, McDonalds (name linked from cell A1), PizzaHut (name linked from cell A2)

The question seems a bit silly because from any other normal cell it is just to write "=A1" to get the context from that cell. Excel table headers seem to work a bit differently. When I try to do this I just get "0", not McDonalds. Any ideas on this?

I appreciate some support,
Emma

 

[James006]

Hi,

Both solutions do work ... once the first table is created ...

1. =Table1[[#Headers],[McDonalds]]
or
2. =A1

HTH

 

[EmmaFia]

Hi,

Thanks for the quick reply but this does not work at all for me.

If I format as a table first and then try to make a cell reference, I only get a zero as column header.

If I create the reference first and then format as an excel table, I get a popup stating: "Formulas in the header row will be removed and converted to static text. Do you want to continue?" (And that is of course not what I want...)

Are there any settings that can enable/disable the use of formulas in table columns...?

 

[James006]

Re,

In order to get a zero ... either cell A1 is empty or your tables are not located in the same worksheet ...

 

[EmmaFia]

Cell A1 is not empty and table is in the same worksheet...

What happens is that when I enter "=A1" in a table column header cell and press enter a text string zero is shown. As soon as I press enter, the formula does not exist in the column header cell any more.

I found the comment "Tables treat all column headers, including dates and numbers, as text strings." Maybe this is why I cannot enter a formula in the header row.
Working with Column Headers in Excel Tables

James006, hope I am not impolite for asking again. So you have a table, formatted as a Table and you can use formulas in the column headers? I cannot seem to be able to work this out...

 

[James006]

Hello Emma,

There is nothing impolite about asking ... !!!

Just dropped you a private message ...