|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Is it possible to put a formula in a table column header to define the name of the column?
I have data from an external source that I put in an Excel worksheet.
These raw data contain results for the previous 12 months and are updated every month. Those raw data also show the variables for the query. One cell contains the names of the selected months. With a formula I can extract the text I want and put it in the column headercells as column names. As I thereafter format the raw data as a table in Excel the column names are transformed to plain text. Because the column headers change every month, I want tot keep the formulas so I can extract the names from the cell as mentioned above. My question is: Is there a way I can put a formula in the header of a table in Excel, or put the result of a formula in the column header cells? Jacques Last edited by JacquesW; 05-01-2017 at 05:59 AM. |
#2
|
|||
|
|||
Hi
Can you upload a sample file ? |
#3
|
|||
|
|||
Below is a sample of the spreadsheet with the cells that matter.
It is the same columns per selected month. In this case for the months of May 2016 to April 2017 (previous 12 months). The file is generated as csv and mailed to me. The data I copy into de pre-defined table in this sheet. In this example, row 31 contains the variables that were queryed containing the months (highlighted in yellow). I quote this in cell C1 with the formula: = V.LOOKUP("Calendar Year / Month"; A:B; 2; FALSE) I have inserted rows 2 and 3 to generate the correct column header names. Row 2 contains solid text that refers to the type given in the column. Row 3 contains a formula that queries the month from cell C1 and merges with the value from row 2. For example, the formula in row 3 for cell C3 is: = PART($C$1; 1; 3) & C2 => result is MEI_% (its Dutch for MAI). This is the name I want to give to this column header for use in pivot tables. Because the values shift each month, cell C3 will result in JUN_% next month. One way to get the column headings right in the cells is the following: The formula results of the cells C3 through C?? Paste as values in cells D3 through D?? That works, but if I can, I'd rather work faster and prevent mistakes. For example, if you forget to copy the names to row 4, there are wrong names in the pivot tables and that is not the intention of course. Hope this helps to get a solution. If things are not clear, please let me know. cheers, Jacques |
#4
|
|||
|
|||
I'm pretty sure if you use Format As Table (as you have) that the column header has to be text. Even if you enter a date, it is stored as text rather than as a floating point decimal in a normal cell.
|
Tags |
formula, table, table headers |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need help with dragging a formula and changing a reference column as I drag the formula. | LupeB | Excel | 1 | 10-22-2015 03:02 PM |
documents saved with double column revert to single column when re-opened | danw | Word | 6 | 04-08-2015 06:24 AM |
How can I temporarily break a 3 column format in order to type a single column paragraph | William P | Word | 1 | 01-04-2015 06:40 PM |
VBA column header vs first row text | vthomeschoolmom | Excel Programming | 1 | 03-10-2012 02:05 AM |
Lost Column header | Policy Peddler | Outlook | 1 | 06-05-2009 07:57 AM |