0

In a sheet I have, in column A I have a formula referencing values in a separate sheet. In column G of the same sheet, I have a formula that references the cells in column A. I essentially want the formula in column G to see if the cell in column A has a value in it, if it does put a 1 in G column cell, if it doesn't have a value in the column A cell, leave it blank. The formula I tried for this is: =IF(ISBLANK(A1), 1, "") & IF(A1<>"",1,"")

That formula returns a "1" for all cells in the G column no matter if it has a value in the A column cell or not. In other words, I'd like the formula in column G to look at the cells in column A, if the cells in column A have fulfilled the formula that is in them, put a 1 in the G column cell, of not, keep it blank. But withe the column A cells already having a formula, this is showing that the column A cells have values inside of them, when they really don't.

Is there a work around for this?

1

The first part of your formula checks for blank cells in column A and puts a 1 in column G if it finds them.

The second part checks for non-blank cells and puts a 1 in column G if it finds them. So you always get a 1 in column G.

You don't need both parts. Keep just the first part, switching the 1 and the "". Or keep just the second part.

EDIT: After modeling your data, I was able to reproduce your problem. In the picture below, Column A has a calculated result based on Column B. And the ISBLANK() function in Column C is saying everything in Column A is not blank. The =A<>"" test in Column D works correctly, as does the formula in Column E based on that test. This formula

=IF(A1<>"",1,"")

is what you should to use in your Column G.

enter image description here

I'll dig into ISBLANK() further to see if there's an explanation for this.

EDIT2: It looks like this is expected behavior for ISBLANK(). It tests whether a cell is completely empty, and will return FALSE if a cell contains a formula, even if the result of the formula is blank. The linked site suggests we should think of the function as ISEMPTY(). Good to know, although irritating as hell. Maybe MS could change the name of the function. @teylyn? :-)

  • When I keep one of the formulas, the cells in the G column place a 1 in a cell that doesn't actually have a value in column A. That is because column A has a formula in it so it shows it has something in the cell, when it actually doesn't. Is there a way I can work around this? – anve Jul 31 '17 at 12:50
  • This is due to an idiosyncrasy in ISBLANK(). But you can use the other logical test and get everything to work correctly. See my revised answer. Good luck. – Bandersnatch Jul 31 '17 at 15:05

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.