Hi guys!

I need a formula that does this:

OK I'm going to draw a spreadsheet
......A..........B......
1..100.....dog....
2..150...<blank
3..200......cat.....

so all I want is a formula that looks in Column B and if there is ANY
text at all, returns the value in Column A

So I want it to look into B1 and it will see text there and return
"100", but if it looks in B2 and sees that it is blank, it just won't
return anything.

Can I do this? Seems like it would be a combination of IF and LOOKUP
or something.

Thank you.


--
Bran987
------------------------------------------------------------------------
Bran987's Profile: http://www.excelforum.com/member.php...fo&userid=7503
View this thread: http://www.excelforum.com/showthread...hreadid=375853

Hi Bran,

put the data you have in column A into column C and enter the following
formula in A1

=IF(B1="","",C1)

copy it down to A2 & A3

Stuart


--
wa284sj
------------------------------------------------------------------------
wa284sj's Profile: http://www.excelforum.com/member.php...o&userid=22941
View this thread: http://www.excelforum.com/showthread...hreadid=375853

In C1 put this formula and copy down........

=IF(B1="","",A1)

or

=IF(ISTEXT(B1),A1,"")

Vaya con Dios,
Chuck, CABGx3




"Bran987" wrote in
message ...

Hi guys!

I need a formula that does this:

OK I'm going to draw a spreadsheet
.....A..........B......
1..100.....dog....
2..150...<blank
3..200......cat.....

so all I want is a formula that looks in Column B and if there is ANY
text at all, returns the value in Column A

So I want it to look into B1 and it will see text there and return
"100", but if it looks in B2 and sees that it is blank, it just won't
return anything.

Can I do this? Seems like it would be a combination of IF and LOOKUP
or something.

Thank you.


--
Bran987
------------------------------------------------------------------------
Bran987's Profile:
http://www.excelforum.com/member.php...fo&userid=7503
View this thread: http://www.excelforum.com/showthread...hreadid=375853

I am confused as to why you wish to do this.

If you wish a list of numbers which have entries in column B would you
not sort over column B and block-delete all rows with a blank column B,
then delete column B, and re-sort (if required) over column A.

If, in fact, you wish to use this data as a lookup-range to update data
on a different sheet, then:
assuming you are using the numbers in column A as the lookup value, the
lookup would read something like
=IF(ISNA(VLOOKUP(a1,looktable,1,FALSE)),"Missing
"&a1,IF(VLOOKUP(B5,looktable,2)="","",a1))


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=375853