Sorry know this is pretty basic stuff here I think. I have a formated
price sheet called sheet A which consists of product names and a
pricing table for each product. the layout for a few products is like
this; product A8, sizes C11-14 and price E11-14, Product A17, sizes
C20-25, price E20-25.
On sheet B you have 3 columns, Product A1-200, Sizes B1-200, Prices
C1-200. This sheet changes on a regular bases and can vary in size. Is
it possible to update Sheet A prices with a Vlookup in this situation
or what can I use to make this work in excel. Any input would be great.
I am pretty new to this.

Thanks for your help

I notice your question got passed up. I think the reason for this is because
it's so unclear. I read it a couple of times and I can't understand your
explanation.

If you still need help, you may want to rewrite your request, make it as
clear as possible, be accurate with your descriptions of cell ranges, etc.
and repost it.

Good luck!

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Sorry know this is pretty basic stuff here I think. I have a formated
> price sheet called sheet A which consists of product names and a
> pricing table for each product. the layout for a few products is like
> this; product A8, sizes C11-14 and price E11-14, Product A17, sizes
> C20-25, price E20-25.
> On sheet B you have 3 columns, Product A1-200, Sizes B1-200, Prices
> C1-200. This sheet changes on a regular bases and can vary in size. Is
> it possible to update Sheet A prices with a Vlookup in this situation
> or what can I use to make this work in excel. Any input would be great.
> I am pretty new to this.
>
> Thanks for your help
>

Here's a dig at it, posted in response to your multi-post in
..worksheetfunctions
(Pl do not multi-post)

Assuming you have ..

In sheet: B, cols A to C, data from row 2 to 100 (say)

Product Size Price
A1 B1 C1
A1 B2 C2
A2 B1 C3
A2 B2 C4
etc

And in sheet: A,
you have a similar set-up

Product Size Price
A1 B1 ?
A2 B2 ?
etc

Put in the formula bar for C2, then array-enter** the formula by
pressing CTRL+SHIFT+ENTER [CSE] (instead of just pressing ENTER):
=IF(OR(A2="",B2=""),"",INDEX(B!$C$2:$C$100,MATCH(1,(B!$A$2:$A$100=A2)*(B!$B$2:$B$100=B2),0)))
Copy C2 down to return the required results. Adapt the ranges to suit.

**Correctly array-entered, Excel will wrap curly braces: { } around
theformula in C2. Visually check that these braces are inserted by Excel
within the formula bar after you array enter. If you don't see it, then the
formula has not been correctly array-entered and you'd get wrong
results. If so, click inside the formula bar, and try the CSE again.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Sorry know this is pretty basic stuff here I think. I have a formated
> price sheet called sheet A which consists of product names and a
> pricing table for each product. the layout for a few products is like
> this; product A8, sizes C11-14 and price E11-14, Product A17, sizes
> C20-25, price E20-25.
> On sheet B you have 3 columns, Product A1-200, Sizes B1-200, Prices
> C1-200. This sheet changes on a regular bases and can vary in size. Is
> it possible to update Sheet A prices with a Vlookup in this situation
> or what can I use to make this work in excel. Any input would be great.
> I am pretty new to this.
>
> Thanks for your help
>

Max,
> I appreciate the response. Your assumption is correct on sheet B.
>But sheet A. is in a formated layout like
>below. A different product labeled every 10 to 15 cells below.
> So will have to adjust the ranges for each product. Played around w/formula posted but
> cant get it to work right.
>
>
> In sheet: B, cols A to C, data from row 2 to 100 (say)
>
> Product Size Price
> A1 B1 C1
> A1 B2 C2
> A2 B1 C3
> A2 B2 C4
> etc
>
> And in sheet: A,
> I have a set up like this
U V
Product Size Price Price
> A B C D E
> 1 A1
> 2
> 3
> 4 C1 D1 ?
> 5 C2 D2 ?
> 6 C3 D3 ?
> 7 C4 D4 ?
> 8
> 9
>10 A2
etc

>>But sheet A. is in a formated layout like
>>below. A different product labeled every 10 to 15 cells below.

Believe the crux problem you faced in adapting the formula here is that the
product col is not populated all the way down, only at each new product
break.

To populate the product col right down,
try the methods described in either of these 2 excellent pages by

David McRitchie:
http://www.mvps.org/dmcritchie/excel/fillempt.htm
Fill in the Empty Cells

Debra Dalgleish:
http://www.contextures.com/xlDataEntry02.html
Excel -- Data Entry -- Fill Blank Cells
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Max,
>> I appreciate the response. Your assumption is correct on sheet B.
>>But sheet A. is in a formated layout like
>>below. A different product labeled every 10 to 15 cells below.
>> So will have to adjust the ranges for each product. Played around
>> w/formula posted but
>> cant get it to work right.
>>
>>
>> In sheet: B, cols A to C, data from row 2 to 100 (say)
>>
>> Product Size Price
>> A1 B1 C1
>> A1 B2 C2
>> A2 B1 C3
>> A2 B2 C4
>> etc
>>
>> And in sheet: A,
>> I have a set up like this
> U V
> Product Size Price Price
>> A B C D E
>> 1 A1
>> 2
>> 3
>> 4 C1 D1 ?
>> 5 C2 D2 ?
>> 6 C3 D3 ?
>> 7 C4 D4 ?
>> 8
>> 9
>>10 A2
> etc
>

That would work fine for me but there are objects linked under the
product cell in sheet A . What if I made a new column 'G' with the
product filled in and hide the value in the cell but viewable in the
formula bar. Would your formula work with this. Also where do the
sheet names come to play in your formula. For readability can we call
sheet A= AT. and Sheet B.= BT. Thank you

> >>Sheet AT
> > U V
> > Product Size Price Price Product w/hidden value
> >> A B C D E F G
> >> 1 A1
> >> 2
> >> 3
> >> 4 C1 D1 ? G1
> >> 5 C2 D2 ? G2
> >> 6 C3 D3 ? G3
> >> 7 C4 D4 ? G4
> >> 8
> >> 9
> >>10 A2
> > etc
> >

Max wrote:
> >>But sheet A. is in a formated layout like
> >>below. A different product labeled every 10 to 15 cells below.
>
> Believe the crux problem you faced in adapting the formula here is that the
> product col is not populated all the way down, only at each new product
> break.
>
> To populate the product col right down,
> try the methods described in either of these 2 excellent pages by
>
> David McRitchie:
> http://www.mvps.org/dmcritchie/excel/fillempt.htm
> Fill in the Empty Cells
>
> Debra Dalgleish:
> http://www.contextures.com/xlDataEntry02.html
> Excel -- Data Entry -- Fill Blank Cells
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Max,
> >> I appreciate the response. Your assumption is correct on sheet B.
> >>But sheet A. is in a formated layout like
> >>below. A different product labeled every 10 to 15 cells below.
> >> So will have to adjust the ranges for each product. Played around
> >> w/formula posted but
> >> cant get it to work right.
> >>
> >>
> >> In sheet: B, cols A to C, data from row 2 to 100 (say)
> >>
> >> Product Size Price
> >> A1 B1 C1
> >> A1 B2 C2
> >> A2 B1 C3
> >> A2 B2 C4
> >> etc
> >>
> >> And in sheet: A,
> >> I have a set up like this
> > U V
> > Product Size Price Price
> >> A B C D E
> >> 1 A1
> >> 2
> >> 3
> >> 4 C1 D1 ?
> >> 5 C2 D2 ?
> >> 6 C3 D3 ?
> >> 7 C4 D4 ?
> >> 8
> >> 9
> >>10 A2
> > etc
> >

"aj_gabby" wrote:
> That would work fine for me but there are objects linked under the
> product cell in sheet A .

> What if I made a new column 'G' with the
> product filled in and hide the value in the cell but viewable in the
> formula bar. Would your formula work with this.

Yes, of course. Just adapt it to point to col G instead of col A

> Also where do the
> sheet names come to play in your formula. For readability can we call
> sheet A= AT. and Sheet B.= BT.

Earlier, in the example, we had:
In sheet: A,
In C2, array-entered:
=IF(OR(A2="",B2=""),"",INDEX(B!$C$2:$C$100,MATCH(1,(B!$A$2:$A$100=A2)*(B!$B$2:$B$100=B2),0)))

Now, in sheet: AT,
assuming the "new" product col = col G, Size col = col C, "new" price col =
col V (?)

Place in V2, and array-enter [CSE]:
=IF(OR(G2="",C2=""),"",INDEX(BT!$C$2:$C$100,MATCH(1,(BT!$A$2:$A$100=G2)*(BT!$B$2:$B$100=C2),0)))
Copy V2 down

As before, the set up assumed in sheet: BT
that col A = product, col B = size, col C = price
(you said that my assumption was correct)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

The following formula works fine in colum V sheet AT

in sheet: AT,
product col = col G, Size col = col C, price col = col V
in sheet: BT
product col = col A , size col = col B, price col = col C

=IF(OR(G2="",C2=""),"",INDEX(BT!$C$2:$C$100,MATCH(1,(BT!$A$2:$A$100=G2)*(BT!$B$2:$B$100=C2),0)))

I needed to add another search criteria to this formula
in sheet: AT,
product col = col G, Size col = col C, price col = col V, Length col
= col H
in sheet: BT
product col = col A , size col = col B, price col = col C, Length col
=col D

I added this to the formula and can not get it to work

=IF(OR(G2="",C2="",H2=""),"",INDEX(BT!$C$2:$C$100,MATCH(1,(BT!$A$2:$A$100=G2)*(BT!$B$2:$B$100=C2)*(BT!$D$2:$D$100=H2),0)))

Any suggestions what I did wrong ?
aj_gabby



Max wrote:
> "aj_gabby" wrote:
> > That would work fine for me but there are objects linked under the
> > product cell in sheet A .
>
> > What if I made a new column 'G' with the
> > product filled in and hide the value in the cell but viewable in the
> > formula bar. Would your formula work with this.
>
> Yes, of course. Just adapt it to point to col G instead of col A
>
> > Also where do the
> > sheet names come to play in your formula. For readability can we call
> > sheet A= AT. and Sheet B.= BT.
>
> Earlier, in the example, we had:
> In sheet: A,
> In C2, array-entered:
> =IF(OR(A2="",B2=""),"",INDEX(B!$C$2:$C$100,MATCH(1,(B!$A$2:$A$100=A2)*(B!$B$2:$B$100=B2),0)))
>
> Now, in sheet: AT,
> assuming the "new" product col = col G, Size col = col C, "new" price col =
> col V (?)
>
> Place in V2, and array-enter [CSE]:
> =IF(OR(G2="",C2=""),"",INDEX(BT!$C$2:$C$100,MATCH(1,(BT!$A$2:$A$100=G2)*(BT!$B$2:$B$100=C2),0)))
> Copy V2 down
>
> As before, the set up assumed in sheet: BT
> that col A = product, col B = size, col C = price
> (you said that my assumption was correct)
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

>
=IF(OR(G2="",C2="",H2=""),"",INDEX(BT!$C$2:$C$100,MATCH(1,(BT!$A$2:$A$100=G2)*(BT!$B$2:$B$100=C2)*(BT!$D$2:$D$100=H2),0)))

It looks ok. Did you array-enter it with CTRL+SHIFT+ENTER?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"aj_gabby" wrote:
> The following formula works fine in colum V sheet AT
>
> in sheet: AT,
> product col = col G, Size col = col C, price col = col V
> in sheet: BT
> product col = col A , size col = col B, price col = col C
>
> =IF(OR(G2="",C2=""),"",INDEX(BT!$C$2:$C$100,MATCH(1,(BT!$A$2:$A$100=G2)*(BT!$B$2:$B$100=C2),0)))
>
> I needed to add another search criteria to this formula
> in sheet: AT,
> product col = col G, Size col = col C, price col = col V, Length col
> = col H
> in sheet: BT
> product col = col A , size col = col B, price col = col C, Length col
> =col D
>
> I added this to the formula and can not get it to work
>
> =IF(OR(G2="",C2="",H2=""),"",INDEX(BT!$C$2:$C$100,MATCH(1,(BT!$A$2:$A$100=G2)*(BT!$B$2:$B$100=C2)*(BT!$D$2:$D$100=H2),0)))
>
> Any suggestions what I did wrong ?
> aj_gabby

I have been using this formula for a while and it works great. I just
recently ran into a problem. When I tried to use numerics in C colum
and H colum with attributes (15.8) I get NA# in the cell the formula
is in. Is there a limitation to what attributes a value can have? Is
there some type of work around for this to work properly?

{=IF(OR(G2="",C2="",H2=""),"",INDEX(BT!$C$2:$C$100,MATCH(1,(BT!$A$2:$A
$100=G*2)*(BT!$B$2:$B$100=C2)*(BT!$D$2:$D$100=H2),0)))}

aj_gabby

On Dec 14 2006, 3:15 am, Max <demecha...@yahoo.com> wrote:
> =IF(OR(G2="",C2="",H2=""),"",INDEX(BT!$C$2:$C$100,MATCH(1,(BT!$A$2:$A$100=G*2)*(BT!$B$2:$B$100=C2)*(BT!$D$2:$D$100=H2),0)))
>
> It looks ok. Did you array-enter it with CTRL+SHIFT+ENTER?
> --
> Max
> Singaporehttp://savefile.com/projects/236895
> xdemechanik
> ---
>
>
>
> "aj_gabby" wrote:
> > The following formula works fine in colum V sheet AT
>
> > in sheet: AT,
> > product col = col G, Size col = col C, price col = col V
> > in sheet: BT
> > product col = col A , size col = col B, price col = col C
>
> > =IF(OR(G2="",C2=""),"",INDEX(BT!$C$2:$C$100,MATCH(1,(BT!$A$2:$A$100=G2)*(BT*!$B$2:$B$100=C2),0)))
>
> > I needed to add another search criteria to this formula
> > in sheet: AT,
> > product col = col G, Size col = col C, price col = col V, Length col
> > = col H
> > in sheet: BT
> > product col = col A , size col = col B, price col = col C, Length col
> > =col D
>
> > I added this to the formula and can not get it to work
>
> > =IF(OR(G2="",C2="",H2=""),"",INDEX(BT!$C$2:$C$100,MATCH(1,(BT!$A$2:$A$100=G*2)*(BT!$B$2:$B$100=C2)*(BT!$D$2:$D$100=H2),0)))
>
> > Any suggestions what I did wrong ?
> > aj_gabby- Hide quoted text -
>
> - Show quoted text -