Formula in column title of a list

[chs4]

Hi,
I have a list with a number of columns. Each column currently has a static title but I would like to be able to make the titles refer to cells in a separate worksheet so I won't have to change the name in two places. When I type into the title cell ='Sheet1'!F33, it returns a 0 and erases the formula. Is there a way to keep a formula in the title cells or some other way to make the column names variable?
Thanks,
Chs4

 

[Smitty]

Welcome to the Board!

You can use named ranges, but it might be tedious if you have a lot.

Or let's say you have your column headers in a list in Sheet 2 from A1 to Axxx.

You can use the following formula in A1:xx1 to pull the formula across the columns event though they're listed in rows in the reference sheet:

=INDIRECT("Sheet2!A"&COLUMN())

Hope that helps,

 

[chs4]

Hi Smitty,
Thanks for replying. That does return the correct title but the formula does not remain in the cell so if I want to change the title again from sheet2, I'd have to put in the indirect formula again. Is there any way to avoid having to do this? I also tried the name thing and when I type in =nameofcell in the column title cell, it just returns a 0, regardless of what value is in the named cell.
Thanks,
Chs4

 

[Smitty]

That does return the correct title but the formula does not remain in the cell so if I want to change the title again from sheet2, I'd have to put in the indirect formula again. Is there any way to avoid having to do this?

Do you maybe have calculation set to Manual? That's about all I can think that would keep the formula from updating, INDIRECT or not, noting that in testing just now it works fine for me.

 

[chs4]

The calculations aren't set to manual because all of the other calculations update normally. Plus, the formula goes away completely, not just keeps the old value. Thanks for your input Smitty. I'll keep looking.
-Chs4

 

[Aladin Akyurek]

The calculations aren't set to manual because all of the other calculations update normally. Plus, the formula goes away completely, not just keeps the old value. Thanks for your input Smitty. I'll keep looking.
-Chs4

If the list under question is a list constructed with Data|List|Create List, the field names probably just by design cannot be calculations.

 

[chs4]

That is how the list was created. Is there another way?

 

[Aladin Akyurek]

That is how the list was created. Is there another way?

The other way is just not to use Data|List|Create List. You'll have to simulate any List functionality that you need, such (a) as automatic copying down of the formulas, (b)automatic adjusting of the ranges the formulas refer to, etc. The former requires VBA, the latter dynamic named ranges.

 

[chs4]

Aladin Akyurek,
I think that will work fine. Thanks for your help.
chs4