Need a formula for the column letter to use in INDIRECT

[Phillycheese5]

Hi,
I have a formula that looks at a monthly series of returns and always calculates a year-to-date return. I want to copy the formula in F28 across columns so I need the letter "F" to change:
=PRODUCT(1+(INDIRECT("F"&MATCH($E$34,$C$1:$C$25,0)&":F"&MATCH($E$34,$C$1:$C$25,0)+MONTH(EOMONTH(TODAY(),-1)))))-1

I am able to substitute "F" for a cell reference in F35 which has the formula =CHAR(COLUMN()+64) but when I substitute "F" with the formula itself it doesn't work. I also need to substitute ":F" in the long formula.

Any suggestions would be appreciated,

Phillycheese

[TMS]

Try:

Please Login or Register  to view this content.

[Phillycheese5]

Hi There!
Thank you for the formula which works great for columns A to Z, but will show "A" for the AA to AZ columns. Apologies that I didn't mention it, but the file I plan to use it is is quite large and has many columns.

Sincerely,
Phillycheese

[TMS]

Not pretty, but try this:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[TMS]

If, in Name Manager, you create a Named Formula:
vAddr=

Please Login or Register  to view this content.

You can then shorten your formula to:

Please Login or Register  to view this content.

[Phillycheese5]

Thanks TMS! I can get the named formula to work by itself but not when I pop it in the formula. I'm going to dig in because it may be something on my side. I appreciate the help and I'll mark it solved when I resolve the issue.
Much appreciated,
Phillycheese

[TMS]

Can't you just copy it?

[Phillycheese5]

Hi TMS, for some reason the SUBSTITUTE just doesn't work (I get #VALUE) when I copy/paste it into the formula, or when I take the entire formula that you noted. I created the named range exactly as you had it and both that and the SUBSTITUTE works fine in a cell by itself.

In the updated file I created a tab called COPY and you can see that if I use vADDr in the long formula it doesn't work. But if I put the =vADDr as a formula in a separate cell and then reference that cell in the long formula, it does work. Any ideas on why I can't use them interchangeably?

Thanks,
Phillycheese

[menem]

please try

SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

in your formula, or test in any blank cell.

Regards.

[TMS]

See these examples

[TMS]

@menem: wish I’d thought of that. Much shorter and simpler. And just the 1 to eliminate

[Bo_Ry]

Add T

=PRODUCT(1+(INDIRECT(T(vADDr&MATCH($E$34,$C$1:$C$25,0)&":"&vADDr&MATCH($E$34,$C$1:$C$25,0)+MONTH(EOMONTH(TODAY(),-1))))))-1

or R1C1
=PRODUCT(1+(INDIRECT("R"&MATCH($E$34,$C$1:$C$25,0)&"C:R"&MATCH($E$34,$C$1:$C$25,0)+MONTH(EOMONTH(TODAY(),-1))&"C",)))-1

or non-volatile

=PRODUCT(1+INDEX(F$5:F$25,MATCH(E34,$C$5:$C$25,)):INDEX(F$5:F$25,MATCH(TODAY(),$C$5:$C$25)))-1

[Phillycheese5]

Hi TMS/menem/Bo_Ry, thank you all for the help with this. I thought it might be the version of Excel I have (2007) but I sent it to my work also where there is version 2016. In both cases I'm experiencing the same thing. When I open the TMS file, I get #VALUE for the examples you have in F57 and F60--but F58 and F59 display the correct result. In Bo_Ry's file the "T" formula shows #VALUE but the non-volatile formula does work in so I will use that or a helper row with either the named range or one of the SUBSTITUTE formulas which seems to do the trick.
Many thanks for the help and different solutions!
Phillycheese

[TMS]

You're welcome. Thanks for the rep.


Please update your profile. That makes a difference in terms of if/how solutions work.