Question description:
This user has given permission to use the problem statement for this
blog.
Hi, I'm trying to dynamically link a cell (specifically a column header) in Table B to a column header cell in Table A on another worksheet in Excel. I'm using structured referencing which is returning '0' as text when I complete the formula (and the formula disappears). I've tried both typing the formula and creating it by typing = and clicking on the cell I want to link to.
If the title of the column header in Table A changes, I need that change to be reflected in the relevant column header in Table B too. I've been searching forums for the solution but haven't found it yet!
Solved by S. D. in 16 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
11/09/2018 - 12:00
Hello, I understand that you would like to "mirror" a cell by direclty linking it to another, right?
User
11/09/2018 - 12:01
Yes, both cells are part of tables with one table one worksheet and the other table on a different worksheet
Excelchat Expert
11/09/2018 - 12:02
Okay, normally what you were doing should've been enough for this to work so I'm thinking that something else is preventing this. In order to help us identify what's causing this problem, are you able to share the actual file or even a sample file that shows the problem?
User
11/09/2018 - 12:02
Here is the file
[Uploaded an Excel file]
Excelchat Expert
11/09/2018 - 12:03
Thank you. PLease give me a few minutes to analyze this.
User
11/09/2018 - 12:03
I want cell K1 on sheet Validations to dynamically link to cell AA7 on Range Plan
Excelchat Expert
11/09/2018 - 12:04
Thank you. I'll need a few minutes.
Excelchat Expert
11/09/2018 - 12:05
May I know the reason why column K needs to be a data table?
User
11/09/2018 - 12:08
This is a master template that will be copied and used by multiple people with varying requirements. The entries in column K will be a data validation list for column AA in Range Plan and each group of people using the file will have a different number of entries in their column K lists (so it needs to expand automatically as additional options are listed).
Excelchat Expert
11/09/2018 - 12:10
I see. That makes sense.
Excelchat Expert
11/09/2018 - 12:10
The only option that I can think of to enable linking to the header is to turn off the header row.
Excelchat Expert
11/09/2018 - 12:11
To do this, click on K1, go to DESIGN then uncheck Header Row.
Excelchat Expert
11/09/2018 - 12:11
This will allow you to have a formula in that cell, otherwise, it will be considered as part of a data table and will be reserved as the header.
User
11/09/2018 - 12:13
I've just done that and it seems to be working
Excelchat Expert
11/09/2018 - 12:13
That's great!
User
11/09/2018 - 12:13
Are there any downsides to not having the header row?
Excelchat Expert
11/09/2018 - 12:14
It shouldn't affect your data at all but you won't be able to filter from that cell anymore since it is technically not part of the data table.
User
11/09/2018 - 12:14
That's ok, shouldn't need to filter on that list of data
User
11/09/2018 - 12:15
Thanks very much for your help
Excelchat Expert
11/09/2018 - 12:15
Would there be anything else that I can help you with regards to the original question?
User
11/09/2018 - 12:16
No thank you, I think that's sorted it
Excelchat Expert
11/09/2018 - 12:16
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert
11/09/2018 - 12:16
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert
11/09/2018 - 12:16
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User
11/09/2018 - 12:16
Will do, thanks very much
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.