software

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: www.excelforum.com/member.php...oamp;userid=32255
View this thread: www.excelforum.com/showthread...hreadid=530903I 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 quot;quot; if the other data isn't there (yet).

=if(counta(a1:b1)=0,quot;quot;,vlookup(a1amp;b1,sheet2!a:e,5, false))
or
=if(counta(a1:b1)lt;2,quot;quot;,vlookup(a1amp;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:
gt;
gt; Hello,
gt;
gt; I'm having a problem concatenating two columns.
gt; I have 2 empty columns, which I then want to concatenate once
gt; information is populated into these fields. I then want to create a
gt; list from these two concatenated columns and have this list as a lookup
gt; on a different worksheet.
gt; When I create the list I highlight the entire two columns as I don't
gt; have a predefined number of entries for the two columns.
gt;
gt; I've done this already but when I try and lookup any of the information
gt; I get a blank list.
gt;
gt; Can someone please advise me on any course of action
gt; Do I need to use an isblank method?
gt; Many many thanks
gt;
gt; --
gt; Max_power
gt; ------------------------------------------------------------------------
gt; Max_power's Profile: www.excelforum.com/member.php...oamp;userid=32255
gt; View this thread: www.excelforum.com/showthread...hreadid=530903

--

Dave Peterson


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,\quot;:\quot;,F2,\quot;;\quot;,E2,\quot;;\quot;,H2,\quot;;\quot;,K 2,\quot; - \quot;,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: www.excelforum.com/member.php...oamp;userid=32255
View this thread: www.excelforum.com/showthread...hreadid=530903Maybe you could use something like:

=if(c2=quot;quot;,quot;quot;,c2amp;quot;:quot;) amp; if(f2=quot;quot;,quot;quot;,f2amp;quot;;quot;) amp; ......

Just break it into smaller pieces.

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

--

Dave Peterson


Thank you very much for your assistance--
Max_power
------------------------------------------------------------------------
Max_power's Profile: www.excelforum.com/member.php...oamp;userid=32255
View this thread: www.excelforum.com/showthread...hreadid=530903