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

[Mr. Snrub]

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.

 

[Peter_SSs]

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]

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:

Date	AAPL	AMC
8/25/2021	148.36	43.96
8/26/2021	147.54	40.31
8/27/2021	148.6	40.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]

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
	A	B	C
1	Date	AAPL	AMC
2	25/08/2021	$ 148.36	$ 43.96
3	26/08/2021	$ 147.54	$ 40.31
4	27/08/2021	$ 148.60	$ 40.84
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=INDEX(STOCKHISTORY(Table1[[#Headers],[AAPL]],Table1[@[Date]:[Date]]), 2, 2)
C2:C4	C2	=INDEX(STOCKHISTORY(Table1[[#Headers],[AMC]],Table1[@[Date]:[Date]]), 2, 2)

 

[Mr. Snrub]

“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]

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]

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.