Excel question -- Formulas in footnotes

Factual Questions
1

I have a standard spreadsheet format that I use repeatedly throughout the year. I input an effective month and year for updates and annual update percentage in designated cells; the spreadsheet uses this information to update data that I input. I footnote exhibits printed from this spreadsheet that the rates shown have been updated by x% to MM/YY.

When I first designed this spreadsheet many years ago, I was using Lotus. I was able to set up a cell that used the relevant cells to create the footnote [as in {simplified}, +"Rates have been updated by "&text(A1)& “to “&text(EffMonth)&”/”&text(EffYr)] This would result in a cell with the statement I wanted in my footnote. I could then refer to this cell for the footnote, and the footnote would print as I wanted it to.

This was very handy – when a client decided they wanted a different update effective date (for example), I had to just change the cells for the date and not only would the spreadsheet results reflect the new date, but the footnote would too.

When I ported the spreadsheet to Excel, it did not allow me to do this. Is there any way to do this? (I wish I could say that after many years of using the Excel spreadsheet I would always remember to change the footnote when needed, but I often forget.)

2

Are EffMonth and EffYr just cells? If so, you should be able to use:

=“Rates have been updated by “&A1&” to “&B1&”/”&C1

to generate your footer where B1 and C1 are the respective cells. Otherwise, if you define the name of the cell as EffMonth or EffYr, you can leave those names in the formula rather than using the cell reference.

3

Yes they are just cells, but they are numbers, rather than text. I tried

="* Rates updated to "&text(effmn,“0”)&txt(effyr(,“0”)&t(updtpct*100,"0.0)

What it gave me was:

="* Rates updated to "&[Time]ext(effmn,“0”)&[Time]ext(effyr(,“0”)&[Time]xt(updtpct*100,"0.0)

4

Check the format of the cell to make sure it’s General and not Text.

5

If that’s copied exactly from your spreadsheet, you have some syntax errors in it.

But your general approach is fine. You don’t need to convert the values to text unless you want extra formatting (e.g., the decimal point on the percentage). I suspect the problem is with your cell references as Inner Stickler noted. Did you name the cells “EffMonth” and “EffYr”? Click on the cell for the month, and in the upper left where it usually shows the column/row (like B7), does it now show EffMonth?