copying adjacent cells with formula

[tanner]

I wonder if it's possible to use vlookup or some such function to call
and copy cells from a table.

I have a table such as:

product color units weight retails discount total cost

I want create a sheet where when I choose the product, the next cell
shows a dependent list of the available colors for the product
(something I think I already figured out), but then I'd like to copy
the adjacent cells, with formulas, from the product table. So if I
choose product A I get all the cells copied from that line to a form.

Hope that makes sense...and I really hope it's possible.

Thanks in advance,

Tanner

[Bob Phillips]

=VLOOKUP(A2,Sheet1!$A$1:$G$100,COLUMN(),False)

put this in B2 and copy across

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"tanner" <[email protected]> wrote in message
news:[email protected]...
> I wonder if it's possible to use vlookup or some such function to call
> and copy cells from a table.
>
> I have a table such as:
>
> product color units weight retails discount total cost
>
> I want create a sheet where when I choose the product, the next cell
> shows a dependent list of the available colors for the product
> (something I think I already figured out), but then I'd like to copy
> the adjacent cells, with formulas, from the product table. So if I
> choose product A I get all the cells copied from that line to a form.
>
> Hope that makes sense...and I really hope it's possible.
>
> Thanks in advance,
>
> Tanner
>

[tanner]

Hi Bob,

Thanks. I tried this and it only partially worked. only the first two
cells copied over. Any ideas why?

[Bob Phillips]

Should work for all. Do the rest have values?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"tanner" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> Thanks. I tried this and it only partially worked. only the first two
> cells copied over. Any ideas why?
>

[tanner]

It's kind of a complicated table that I'm looking up from. What I need
is to copy the formulas and values of adjacent cells based on the
choice made.

Item Color Quantity Weight Units/row
units/Pallet Total Weight
Paleo Beige 123 23.5lbs 42
144
Bergerac Grey 123 32 lbs 38
122


So if I have a drop down list on another sheet that chooses Paleo, then
the adjacent formulas and cells from the table would be copied over.
What makes it more complicated is that the color is a dependent list
based on the product chosen, but I can put color ahead of item and deal
with that dependent list that way. I just need some way, however, to
copy all those adjacent values and cells from the table. Something like
"lookup a value" then "copy cells from column c:g adjacent to this.

Maybe I should be doing this through a macro tied to a button.


Thanks again for your help.