How do you get the current column header name in an Excel table?

Mr. Snrub

Board Regular
Joined
May 22, 2005
Messages
141
Office Version
  1. 365
Platform
  1. Windows
In a row in an Excel table formatted as a table, how do I get the current cell's column name? I want to reference it in a STOCKHISTORY formula. My table headers are the ticker symbol I want to query.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,743
Office Version
  1. 365
Platform
  1. Windows
how do I get the current cell's column name?
Can you explain what you mean by "current cell" in this case?

Are you looking for a vba solution?

Where do you want the result (header name) to go?

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 

Mr. Snrub

Board Regular
Joined
May 22, 2005
Messages
141
Office Version
  1. 365
Platform
  1. Windows
Can you explain what you mean by "current cell" in this case?

Are you looking for a vba solution?

Where do you want the result (header name) to go?

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

This is what I want it to look like:

DateAAPLAMC
8/25/2021148.3643.96
8/26/2021147.5440.31
8/27/2021148.640.84

"=INDEX(STOCKHISTORY("aapl", [@Date]), 2, 2)" is the formula I have. I just want to reference the header cell instead of writing the actual stock ticker symbol.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,743
Office Version
  1. 365
Platform
  1. Windows
That did not answer any of my questions and we still do not know your excel version but taking a guess, it might be this in cell B2 for me then copied across and down the table. That would produce the formula shown for column C.

Mr. Snrub.xlsm
ABC
1DateAAPLAMC
225/08/2021$ 148.36$ 43.96
326/08/2021$ 147.54$ 40.31
427/08/2021$ 148.60$ 40.84
Sheet1
Cell Formulas
RangeFormula
B2:B4B2=INDEX(STOCKHISTORY(Table1[[#Headers],[AAPL]],Table1[@[Date]:[Date]]), 2, 2)
C2:C4C2=INDEX(STOCKHISTORY(Table1[[#Headers],[AMC]],Table1[@[Date]:[Date]]), 2, 2)
 

Mr. Snrub

Board Regular
Joined
May 22, 2005
Messages
141
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

“Can you explain what you mean by "current cell" in this case?”

The current cell is the cell that contains the STOCKHISTORY function. It will query the stock that is written in the cell that is the table header.

“Are you looking for a vba solution?”

No.

“Where do you want the result (header name) to go?”

It will be part of the STOCKHISTORY function. My goal is to be able to add more columns with stock tickers in the header and then be able to just autofill the formula. With the solution you provided above you still have to manually enter the ticker symbol.
 

Mr. Snrub

Board Regular
Joined
May 22, 2005
Messages
141
Office Version
  1. 365
Platform
  1. Windows
Eureka! I have found it!

=INDEX(SharePrices[#Headers],COLUMN()-MIN(COLUMN(SharePrices))+1)

...will return the table header text. And if we plug it in to the STOCKHISTORY function, we get:

=INDEX(STOCKHISTORY(INDEX(SharePrices[#Headers],COLUMN()-MIN(COLUMN(SharePrices))+1), [@Date]), 2, 2)

...which can go on the entire table.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,743
Office Version
  1. 365
Platform
  1. Windows
With the solution you provided above you still have to manually enter the ticker symbol.
No you don't. In cell B2 I typed =INDEX(STOCKHISTORY(
then I clicked on cell B1 and it auto-inserted the header details so then I completed the rest of the formula.
I then selected B2 and dragged the Fill Handle to the right and the formulas automatically changed to match the header of the columns it was dragged to. So no stock codes typed anywhere.

I did have to do some manual adjusting of this part of my formula Table1[@[Date]:[Date]] but only in the very first cell and this allowed the dragging of the formula to the right and still reference the Date column.
Your formula also needed a little bit on manual intervention too with COLUMN(SharePrices)

I guess it is just another case of "more than one way to skin a cat" and if you are happy, so am I. :)
 

Forum statistics

Threads
1,147,734
Messages
5,742,862
Members
423,760
Latest member
photogfrog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top