Sep 21 2020 02:04 PM
I copied and paste 2 formulas related to 1) IFERROR and VLOOKUP, and 2) IF/Month from a known good spreadsheet into a new spreadsheet, and they stopped working. I spent hours debugging then and found no success. Can someone provide some guidance?
=IFERROR(VLOOKUP(W2,'FOP Table'!A:D,3,0), IFERROR(VLOOKUP(W2,'FOP Table'!B:D,2,0),"TBD"))
=IF(MONTH(Y2)<=3,YEAR(Y2)&"_FQ4",IF(MONTH(Y2)<=6,(YEAR(Y2)+1)&"_FQ1",IF(MONTH(Y2)<=9,(YEAR(Y2)+1)&"_FQ2",(YEAR(Y2)+1)&"_FQ3")))
I appreciate your help.
Sep 21 2020 02:25 PM
If you apply General format (instead if Text) to these cells
and re-enter formulas (F2, Enter), they calculate something. But I'm not sure what you'd like to calculate. For example, formula in C2 refers on Y2 as on date, but that's text field.
Sep 21 2020 02:31 PM
Cells A2 to C2 are formatted as Text, so they display the formulas as text instead of evaluating them.
Select A2:C2.
Set the number format to General.
Select the cells one by one, press F2 then Enter.
The formulas in A2 and B2 will return a value, but the one in C2 will return #VALUE!. This is because the formula refers to MONTH(Y2) and YEAR(Y2), but Y2 does not contain a date.
Sep 21 2020 02:32 PM
Hi @Sergei Baklan,
Thank you much for your help. It worked after I followed your instructions. Why would a General Format (instead of text) cause the problem? What does F2/Enter do to the existing formula?
Also, The formula in C2 should refer to Q2 (instead of Y2) as on date. Thanks for pointing it out.
Lee
Sep 21 2020 02:34 PM
Sep 21 2020 03:07 PM
If cell is formatted as text, whatever you enter into it will be taken literally as you typed it. Even if such text is in form of date, formula, number - that's just a text.
If you format the cell as General when Excel recognizes you enter something else. If this else start from = Excel interprets what was entered as formula.
If you press F2 you shift on cell edit mode (you may edit cell content directly within it, not in formula bar). Pressing Enter you finish the editing.
Above are main topics, something could be done by another way.
Sep 21 2020 03:51 PM