If 2 cells have a value do nothing, if one cell has value and other cell has no value do this

[dappy]

Hi again folks.

I have a list of cells from lookups that result in this. these cells have ether equal too or vlookup results.

TCH_2	TCH_3	TCH_4		TRX2	TRX3	TRX4	TRX5
55				2	3
48				8	9
34				14	15
78				20	21
84				26	27

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

What i would like to do is compare tch_2 cells to trx2 cells and if both have data then do nothing but if as in the case of tch_3 and trx3 there are blanks in tch_3 then another cell has an output text. unfortnately i can get a result for these BUT if both cells are blank i still get an output. it sees the lookups or equals as a content. I've tried Value and iferror but cant get to the state where no ouptut is given when both are blank

Hope this is clear folks and thanks in advance.

Carl

 

[dappy]

Dude, that is the dogs mate, perfect, and you did that with me not answering your question clearly!

thanks so much, you chaps rock. that's the perfect solution to my issue. of which i have many

thanks again. so appreciated.

Carl

 

[jtakw]

You're welcome, glad it worked for you.

 

[dappy]

Hi,

Can i add just one more condition please? this works fantastically

=IF(AND(E60="",K60=""),"",IF(OR(E60="",K60=""),"delete","leave"))

So, currently if there is a value in Col E but no value in Col K then we get a "deleted" but if a value is in both then we get a "Leave". that all works perfectly but if the data that is being looked up is both blank i get a "delete". is it possible to have nothing? to get a blank?

I've formated the cells in custom using General;General;;@ to not show a value.

thanks again

Carl

 

[jtakw]

Hi,

Can i add just one more condition please? this works fantastically

=IF(AND(E60="",K60=""),"",IF(OR(E60="",K60=""),"delete","leave"))

So, currently if there is a value in Col E but no value in Col K then we get a "deleted" but if a value is in both then we get a "Leave". that all works perfectly but if the data that is being looked up is both blank i get a "delete". is it possible to have nothing? to get a blank?

I've formated the cells in custom using General;General;;@ to not show a value.

thanks again

Carl

Hi again dappy,

Actually, what you're asking is Already addressed in the formula as highlighted in red above, if both E cell and K cell are Blank, the formula will return Blank ( "" ), that's why I asked about the values in E and K, refer to Post #5 , #7 , and #9 .

So now my question is, how are the values in E and K entered? By formula or manually?
If by formula, can you please post the formula being used?

 

[dappy]

Hi,

Yes sorry, I wasnt clear enough initially.

So, E have >=Paste_data!O62<

K has >=VLOOKUP($C62,BTS_TRX!$C:I,3,0)<

thanks again

 

[jtakw]

This may seem tedious, but we need to know what's in O62, if O62 is result of formula, what's the formula?
What's in C62, and BTS_TRX!E?

By any chance, you're using " " (space) instead of "" (Blank) in your formulas?

Side question: Are you wrapping a number, say 3, with quotes, like "3", instead of just 3 , without quotes in any of your formulas? With quotes, the number becomes TEXT...

 

[dappy]

Hi,

i pm'd you with the link to the excel sheet in question, i hope it helps

not using " " (space) instead of ""

not wrapping a number

O62 =VLOOKUP($C62,BTS_TRX!$C:M,7,0) which results in the cell being blank

BTS_TRX E62 =IFERROR(INDEX(BSC_BTS_TRX!$C$2:$C$3000,AGGREGATE(15,6,(ROW(BSC_BTS_TRX!$C$2:$C$3000)-ROW(BSC_BTS_TRX!$C$2)+1)/(BSC_BTS_TRX!$B$2:$B$3000=$C62), COLUMNS($D62:E62))),"")
which again results in the cell being blank

I know its a mess but any chance you can work with this?

 

[jtakw]

OK, I've had a look at your file.

On "equals_data" Tab, my formula works fine up until Row 61, Columns U, V, and W produced "Blank" ( "" ) results as Expected when the 2 referenced Cells are Both Blank.

But the formula Failed starting at Row 62...

Using S62 as an example:
The referenced Cell E62 contains this formula, =Paste_data!O62; however Paste_data!O62 is a TRUE blank cell with No data or Formula.
Therefore, the TEST of E62="" within my formula returns FALSE, because E62 contains a formula.

It worked for Rows 2 to 61 because you have an apostrophe ( ' ) for referenced cells that are "empty".

So there are 2 ways to "fix" this:

1. Add the apostrophe ( ' ) to Referenced Empty Cells in the "Paste_data" Tab, or
2. Change the formula in E62 to this: =IF(Paste_data!O62="","",Paste_data!O62)

Let me know if my explanation is not clear.

 

[dappy]

Once again you have come through with flying colours. That worked a treat.

Again, thank you so much, you've lengthened my life by some order!

thank you so much for persevering with this problem and I apologies for having such difficulties describing what i was after.

kind regards

Carl

 

[jtakw]

You're welcome, glad to help.