Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-30-2017, 11:45 PM
JacquesW JacquesW is offline Is it possible to put a formula in a table column header to define the name of the column? Windows 7 32bit Is it possible to put a formula in a table column header to define the name of the column? Office 2010 32bit
Novice
Is it possible to put a formula in a table column header to define the name of the column?
 
Join Date: Jan 2012
Posts: 21
JacquesW is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 05-03-2017, 09:05 AM
ferocious12 ferocious12 is offline Is it possible to put a formula in a table column header to define the name of the column? Windows 7 64bit Is it possible to put a formula in a table column header to define the name of the column? Office 2016
Novice
 
Join Date: Jan 2017
Posts: 6
ferocious12 is on a distinguished road
Default

Hi

Can you upload a sample file ?
Reply With Quote
  #3  
Old 05-07-2017, 09:16 AM
JacquesW JacquesW is offline Is it possible to put a formula in a table column header to define the name of the column? Windows 10 Is it possible to put a formula in a table column header to define the name of the column? Office 2013
Novice
Is it possible to put a formula in a table column header to define the name of the column?
 
Join Date: Jan 2012
Posts: 21
JacquesW is on a distinguished road
Default

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
Attached Files
File Type: xlsx Vraag over formules in kolomkoppen van een tabel.xlsx (72.2 KB, 14 views)
Reply With Quote
  #4  
Old 05-08-2017, 08:00 AM
gebobs gebobs is offline Is it possible to put a formula in a table column header to define the name of the column? Windows 7 64bit Is it possible to put a formula in a table column header to define the name of the column? Office 2010 64bit
Expert
 
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default

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.
Reply With Quote
Reply

Tags
formula, table, table headers

Thread Tools
Display Modes


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
Is it possible to put a formula in a table column header to define the name of the column? 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

Other Forums: Access Forums

All times are GMT -7. The time now is 07:43 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft