I am using the following formula: =IF('2009 Data'!M10<>"",'2009 Data'!M10,"M10 in '2009 Data'! Is blank")

which is great as it returns a date or a blank cell which is what I want, but I am having two problems.

The first is that when I copy it into another cell (and there are many I need to copy it into!) it shows as the formula - not the result of the formula.

The second is that when I try to amend the row number or cell number (as I am getting data from a very large spreadsheet) the cnetry reverts to the formula again not the result!

All help gratefully received.

Thank you.

Hi, Check that the show formulas option is not checked.

From the Menu - Tools - Options - View Tab

Clear the Formulas check box in the Window Options section.Regards

Hi, Apart from your problem with formulas I noticed that your 'blank cell' message contains the cell address e.g.,

"M10 in '2009 Data'! Is blank"The cell address in the error message will not change as you extend the formula to other cells - and you say you have many of them.

A simple formula to change the row address in the message is this:

=IF(M10<>"",M10,"M"&CELL("row",M10)&" in '2009 Data'! Is blank")

You will only have to change the column letter when you use the formula in a different column.If you want a formula that works across columns, at least A to Z, you can use this:

(I have split the formula onto several lines for ease of viewing)=IF(M10<>"",M10,CHOOSE(CELL("col",M10), "A","B","C","D","E","F","G","H","I","J","K","L","M", "N","O","P","Q","R","S","T","U","V","W","X","Y","Z") &CELL("row",M10)&" in '2009 Data'! Is blank")

The CELL("col" function returns the column number, so it has to be converted to a letter.Regards

Sometimes a cell that has been formatted as text will not let go of that format such that when you type in a formula it assumes that it is text. Even changing the format to something else usually doesn't help. One way to fix the problem is to put an single quote before the formula to

forceit to be text and then edit the cell again and remove the single quote. This should set it so that it accepts the formula as written.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History