Copying, pasting and amending formulas

Microsoft Excel 2003 (full product)
January 31, 2010 at 03:02:23
Specs: Windows XP
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.

See More: Copying, pasting and amending formulas

Report •

January 31, 2010 at 04:51:52

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.


Report •

January 31, 2010 at 05:22:41

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)

&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.


Report •

January 31, 2010 at 12:23:15
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 force it 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.

Report •

Related Solutions

Ask Question