Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
isblank problem
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
isblank problem
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
isblank problem
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,\";\",K 2,\" - \",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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
isblank problem
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,\";\",K 2,\" - \",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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
isblank problem
Thank you very much for your assistance -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ISBLANK Formula Help Request | Excel Worksheet Functions | |||
Problem With Reference Update | Excel Worksheet Functions | |||
Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
problem office assistant | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) |