Macro/Formula Help?

[fluci]

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?

[Max]

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 <at>yahoo<dot>com
----
"fluci" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Max]

> 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 <at>yahoo<dot>com
----

[fluci]

im really new at using excel
what do i 'revise' the formula with?

[fluci]

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]

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$101,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!

[Max]

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 <at>yahoo<dot>com
----

[fluci]

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

[Max]

You're welcome !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik
----