Cannot search for blank cells

[LightHeart7]

Please Login or Register  to view this content.

I have tried using this and it will not bring back my blank cells. I have 3 Cs in A and in J its two dates and one blank. I want only one C to come back that is blank. Why Is it not letting me?

[XOR LX]

Hi,

I'm afraid the answer to that will be quite difficult without seeing the actual spreadsheet in question, or, if that's not possible, then at least a mock-up, reduced version which exhibits the same issue.

Regards

[FDibbins]

Yes, thats like telling the guy over the phone...my car is making a funny noise, whats wrong with it?

[LightHeart7]

Sorry, here. I just need it to pull the C that has a blank cell in J

[XOR LX]

I don't understand.

The formula is returning FALSE since the first condition: IF($A$3="Not"... is FALSE in all four cases (A2 contains "Not" - was this what you meant?)

Regards

[LightHeart7]

Wow I messed up that formula... A1 should be not. And A2 be C. A1 is supposed to be something the person fills in and it reacts to start the formula.

Please Login or Register  to view this content.

I'm so sorry.

[XOR LX]

So what's the issue? I'm afraid I still don't understand. With "Not" in A1 and "C" in A2, that formula you posted returns "C", as you wish, correct?

Regards

[LightHeart7]

The problem is when the formula reacts on not "or no information". Its supposed to use that formula to bring up the middle C which has no information in column J. instead it wont do that. either its an error or it will bring all 3. Its only supposed to bring up anything that has a blank cell in J.

[XOR LX]

I'm afraid I'm completely lost.

Your formula, as given, will perform the INDEX on the Entered!A:A tab if A1 contains "Not", but there is no clause to say what to do if A1 does not contain "Not". What do you mean, precisely, by "when the formula reacts on not "or no information"."? Which bit of the formula is this?

Perhaps if I tell you how I see what the formula is doing: If A1 contains "Not", then look in the Entered tab, and return the first entry in column A which contains a "C" AND where the corresponding entry in column J is blank.

There is precisely one row which matches both these criteria. That will be returned in the first cell. However, since the formula is also designed in such a way that, when dragged down, it will return the 2nd, 3rd, 4th, etc. entries matching those criteria, and since there is in fact only one, dragging the formula down in this case will only produce errors.

Regards

[LightHeart7]

This is the start of a formula series, there will be other things that will be put in like Date in A1 and i will have another formula for that. I just didn't erase "Not" for this. But when I have tried to get this formula right it either
A. Doesn't bring anything up.
B. Brings in all 3.
I cannot get it to just bring the one line up that has the empty J Cell. This is my 5th time trying to reformulate the cell. what you're saying is what its supposed to do. Supposed to return the C and the second one over next to it which should be 11. I don't want the C 2 or C 19. The errors when dragged below just lets me know there is only one so I don't mind those.

[XOR LX]

Hang on - are you saying that you want to return the whole row? That the formula's not in fact designed to be dragged down, but in fact across? You want the whole thing (C, 10, 11, 12, 13, 14, 15, 13, 17) returned?

Regards

[LightHeart7]

The formula will be dragged down. but the 2 formulas int he cells are to bring back A column, to bring the C, and the C column which should bring back the 11. Its supposed to be dragged down because i will be adding to this and I could have over 100 entries, some with blank J cells.

[XOR LX]

Why 11? What's the C column got to do with this? Is this somehow related to the letter C in cell A2? Are you somehow trying to use that to determine which column to return from?

Regards

[LightHeart7]

The results in Entered. I called the those columns from Entered and brings them in. Because I don't want all the columns only a few. Right there int he formulas I have it calling Columns from Entered. I just can't get it to call what i need correctly its not picking up the blank cell in J.

[XOR LX]

So I was right, then? You are attempting to use the "C" to fetch entries from that column? Because I have to tell you that, if that's the case, I'm afraid your current formula is doing nothing of the sort - it certainly isn't "calling Columns from Entered".

I can help you on this, but I'd really appreciate if you try and be as clear as you can with your answers.

Regards

[LightHeart7]

I found the issue which was actually more of a glitch I was having. I adjusted a few space problems and now it is working. Thank you for working with me as confusing as it was. It is calling the columns I need and calling for the blanks now. Thanks!

[XOR LX]

Ok, glad you got it to work. So this is solved now?

Regards

[LightHeart7]

Yes. I went over a lot when you were trying to get me to narrow down the problem and that's when i realized it. Thank you though it made me realize there's more than I'm looking at a lot of the time.