isblank problem

[Max_power]

Hello,

I'm having a problem concatenating two columns.
I have 2 empty columns, which I then want to concatenate once information is populated into these fields. I then want to create a list from these two concatenated columns and have this list as a lookup on a different worksheet.
When I create the list I highlight the entire two columns as I don't have a predefined number of entries for the two columns.

I've done this already but when I try and lookup any of the information I get a blank list.


Can someone please advise me on any course of action
Do I need to use an isblank method?
Many many thanks

[Dave Peterson]

I would only put the formula in rows that have entries--I'd copy the formula
down when I added more data.

But maybe you could just copy the formula down a bit more than you need and make
the formula return "" if the other data isn't there (yet).

=if(counta(a1:b1)=0,"",vlookup(a1&b1,sheet2!a:e,5,false))
or
=if(counta(a1:b1)<2,"",vlookup(a1&b1,sheet2!a:e,5,false))

(depending if filling one of those cells is sufficient.)

========
Another alternative to using concatenate...

You may be able to use something like:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))

(still an array formula)

Max_power wrote:
>
> Hello,
>
> I'm having a problem concatenating two columns.
> I have 2 empty columns, which I then want to concatenate once
> information is populated into these fields. I then want to create a
> list from these two concatenated columns and have this list as a lookup
> on a different worksheet.
> When I create the list I highlight the entire two columns as I don't
> have a predefined number of entries for the two columns.
>
> I've done this already but when I try and lookup any of the information
> I get a blank list.
>
> Can someone please advise me on any course of action
> Do I need to use an isblank method?
> Many many thanks
>
> --
> Max_power
> ------------------------------------------------------------------------
> Max_power's Profile: http://www.excelforum.com/member.php...o&userid=32255
> View this thread: http://www.excelforum.com/showthread...hreadid=530903

--

Dave Peterson

[Max_power]

Many thanks for your detailed response.

I've got a new query now that is similar to the previous question.

I have a forumula in Column B2 of my worksheet

=CONCATENATE(C2,":",F2,";",E2,";",H2,";",K2," - ",P2)

When I copy this formula down through a number of cells I get the output

:;;; -


What I'm looking for is a formula to say if the cell is blank(empty) don't concatenate the cells just return a null value ie leave it empty and don't return :;;; -


Thanks for any help

[Dave Peterson]

Maybe you could use something like:

=if(c2="","",c2&":") & if(f2="","",f2&";") & ......

Just break it into smaller pieces.

Max_power wrote:
>
> Many thanks for your detailed response.
>
> I've got a new query now that is similar to the previous question.
>
> I have a forumula in Column B2 of my worksheet
>
> =CONCATENATE(C2,\":\",F2,\";\",E2,\";\",H2,\";\",K2,\" - \",P2)
>
> When I copy this formula down through a number of cells I get the
> output
>
> :;;; -
>
> What I'm looking for is a formula to say if the cell is blank(empty)
> don't concatenate the cells just return a null value ie leave it empty
> and don't return *:;;; -
> *
>
> Thanks for any help
>
> --
> Max_power
> ------------------------------------------------------------------------
> Max_power's Profile: http://www.excelforum.com/member.php...o&userid=32255
> View this thread: http://www.excelforum.com/showthread...hreadid=530903

--

Dave Peterson

[Max_power]

Thank you very much for your assistance