Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro/Formula Help?
A1 is any word A2 is blank A3 = A1 A4:A10 = random words C3:J3 = Reference words $C4-10:$J4-10=Random words What I need is when I type in a word from A4:A10 into the Cell A1, the cell A3 becomes that cell and it makes cells C3:J3 the corresponding values of the same row as the A4:A10 value For example IF I type Reference into A1, I need it to look like this Reference Test No HA DING (etc) Reference Test No HA DING (etc) Blank Moop Red Interesting Juggling Nine Ten Can anyone help me? -- fluci ------------------------------------------------------------------------ fluci's Profile: http://www.excelforum.com/member.php...o&userid=25896 View this thread: http://www.excelforum.com/showthread...hreadid=393190 |
#2
|
|||
|
|||
One way ..
Put in A3: =IF(A1="","",A1) (Revise the formula a little ..) Select C3:J3 Put in the formula bar: =IF(A3="","",VLOOKUP(A3,A4:$J$10,{3,4,5,6,7,8,9,10 },0)) Array-enter the formula, i.e press CTRL+SHIFT+ENTER C3:J3 will return the desired results -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "fluci" wrote in message ... A1 is any word A2 is blank A3 = A1 A4:A10 = random words C3:J3 = Reference words $C4-10:$J4-10=Random words What I need is when I type in a word from A4:A10 into the Cell A1, the cell A3 becomes that cell and it makes cells C3:J3 the corresponding values of the same row as the A4:A10 value For example IF I type Reference into A1, I need it to look like this Reference Test No HA DING (etc) Reference Test No HA DING (etc) Blank Moop Red Interesting Juggling Nine Ten Can anyone help me? -- fluci ------------------------------------------------------------------------ fluci's Profile: http://www.excelforum.com/member.php...o&userid=25896 View this thread: http://www.excelforum.com/showthread...hreadid=393190 |
#3
|
|||
|
|||
Put in the formula bar:
=IF(A3="","",VLOOKUP(A3,A4:$J$10,{3,4,5,6,7,8,9,10 },0)) A typo "correction" for the table array cell reference .. and a slight revision to add TRIM() for robustness .. Put instead in the formula bar with C3:J3 selected: =IF(A3="","",VLOOKUP(TRIM(A3),$A$4:$J$10,{3,4,5,6, 7,8,9,10},0)) and array-enter as before Another better but slightly longer alternative which returns say, : "-" for any unmatched cases instead of ugly #N/As [where the input in A1 doesn't match with what's in A4:A10] we could put in the formula bar with C3:J3 selected: =IF(A3="","",IF(ISNA(MATCH(TRIM(A3),$A$4:$A$10,0)) ,"-",VLOOKUP(TRIM(A3),$A$4 :$J$10,{3,4,5,6,7,8,9,10},0))) and array-enter as before -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#4
|
|||
|
|||
im really new at using excel what do i 'revise' the formula with? -- fluci ------------------------------------------------------------------------ fluci's Profile: http://www.excelforum.com/member.php...o&userid=25896 View this thread: http://www.excelforum.com/showthread...hreadid=393190 |
#5
|
|||
|
|||
max that is fantastic however... it only seems to work for C3 i dont understand it how do i change it to work for cells D3:J3? -- fluci ------------------------------------------------------------------------ fluci's Profile: http://www.excelforum.com/member.php...o&userid=25896 View this thread: http://www.excelforum.com/showthread...hreadid=393190 |
#6
|
|||
|
|||
Thank you very much max! I was playing around with the formula and figured out it works if I removed one of the columns in the VLOOKUP part of the code. For example, C3 says =IF(A$3="","",IF(ISNA(MATCH(TRIM(A$3),$A$4:$A$101, 0)),"-",VLOOKUP(TRIM(A$3),$A$4:$J$10,{*-3-,-*-4,5,6,7,8,9,10},0))) Then D3 says =IF($A$3="","",IF(ISNA(MATCH(TRIM($A$3),$A$4:$A$10 1,0)),"-",VLOOKUP(TRIM($A$3),$A$4:$J$10,{4,5,6,7,8,9,10},0 ))) And so on all the way to J3 Thanks alot max! -- fluci ------------------------------------------------------------------------ fluci's Profile: http://www.excelforum.com/member.php...o&userid=25896 View this thread: http://www.excelforum.com/showthread...hreadid=393190 |
#7
|
|||
|
|||
While I'm glad to hear you got it working, it wasn't supposed to be so
labour intensive ! Think you got hit with some implementation problems <g (Link to download a working sample file is provided below) Let's try it again .. Select C3:J3 (< the range selection part is important !) Put in the formula bar (with C3:J3 selected): =IF(A3="","",IF(ISNA(MATCH(TRIM(A3),$A$4:$A$10,0)) ,"-",VLOOKUP(TRIM(A3),$A$4 :$J$10,{3,4,5,6,7,8,9,10},0))) (The entire formula above has to be in a single line. You have to correct the inevitable line breaks/wraps which will be present when you directly copy the formula from this post and paste it into the formula bar) After you have corrected the line breaks in the formula, array-enter the formula, i.e press CTRL+SHIFT+ENTER (instead of just pressing ENTER) If you do this correctly, Excel will put curly braces { } around the formula Every cell within C3:J3 will be filled with the same formula when you array-enter, but the correct results will be returned within each cell Here's a link to a sample file with the working implementation above: http://www.savefile.com/files/5605128 File: Macro_Formula_Help_misc.xls -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#8
|
|||
|
|||
AHA! lol Control Shift Enter... That makes sense lol oh well Thanks max for your help :) I still dont understand what Match Trim Isna or Vlookup means but I managed to tweak it to work for me Thanks again :) -- fluci ------------------------------------------------------------------------ fluci's Profile: http://www.excelforum.com/member.php...o&userid=25896 View this thread: http://www.excelforum.com/showthread...hreadid=393190 |
#9
|
|||
|
|||
You're welcome !
-- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|