cell updating question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aj_gabby@yahoo.com - 16 Nov 2006 05:52 GMT

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

Reply to this Message

Tom James - 17 Nov 2006 01:33 GMT

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!

> 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
[quoted text clipped - 8 lines]
>
> Thanks for your help

Reply to this Message

Max - 20 Nov 2006 19:31 GMT

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.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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
[quoted text clipped - 8 lines]
>
> Thanks for your help

Reply to this Message

aj_gabby@yahoo.com - 21 Nov 2006 16:33 GMT

Max,

> I appreciate the response. Your assumption is correct on sheet B.
>But sheet A. is in a formated layout like
[quoted text clipped - 13 lines]
> And in sheet: A,
> I have a set up like this

U V
Product Size Price Price

> A B C D E
> 1 A1
[quoted text clipped - 7 lines]
> 9
>10 A2

etc

Reply to this Message

Max - 22 Nov 2006 02:09 GMT

>>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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Max,
>> I appreciate the response. Your assumption is correct on sheet B.
[quoted text clipped - 29 lines]
>>10 A2
> etc

Reply to this Message

aj_gabby - 22 Nov 2006 03:01 GMT

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
[quoted text clipped - 11 lines]
> >>10 A2
> > etc

> >>But sheet A. is in a formated layout like
> >>below. A different product labeled every 10 to 15 cells below.
[quoted text clipped - 52 lines]
> >>10 A2
> > etc

Reply to this Message

Max - 22 Nov 2006 03:21 GMT

> 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)

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

aj_gabby - 14 Dec 2006 05:57 GMT

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

> > That would work fine for me but there are objects linked under the
> > product cell in sheet A .
[quoted text clipped - 31 lines]
> xdemechanik
> ---

Reply to this Message

Max - 14 Dec 2006 08:15 GMT

=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?

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> The following formula works fine in colum V sheet AT
>
[quoted text clipped - 19 lines]
> Any suggestions what I did wrong ?
> aj_gabby

Reply to this Message

aj_gabby - 31 Jan 2007 05:17 GMT

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=G2)*(BT!$B$2:$B$100=C2)*(BT!$D$2:$D$100=H2),0)))}

aj_gabby

> =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)))
>
[quoted text clipped - 30 lines]
>
> - Show quoted text -

Reply to this Message

Max - 31 Jan 2007 14:05 GMT

It should work just as well with numbers. Probably the issue is data
consistency, ie there are text numbers within cols C and H which fail
to match with the real numbers within the source ranges.

Try this slight tweak to the array formula:
=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+0)*(BT!$D$2:$D$100=H2+0),0)))}

Adding zero is one way to coerce text numbers to real numbers,
re the tweaked parts: C2+0, H2+0
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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
[quoted text clipped - 6 lines]
>
> aj_gabby

Reply to this Message

aj_gabby - 31 Jan 2007 15:07 GMT

I tried your idea and got the same result. I also checked the cell
formats and made them all numeric. I re-typed the values as well. I
sent you a sample from the work book to see if there is something I am
missing.

Work Book Sample. Formula in colum D

>>D E F G H

_____ ___ ________ _____________ __________
#N/A    32    VR-5321    50.00000000 25.00000000
#N/A    16    VR-5321    1968.50393701     1.96850394
#N/A    12    VR-5321    1968.50393701     2.95275591
#N/A    1    VR-5321    1968.50393701 39.37007874

{=IF(OR(F11="",H11="", G11=""),"",INDEX(BT!$C$2:$C$994,MATCH(1,(BT!$A
$2:$A$994=F11)*(BT!$B$2:$B$994=H11+0)*(BT!$E$2:$E$994=G11+0),0)))}

Sheet BT

>>A B C D

_______ ___________ _______
______________
VR-5321    25.00000000 12.8100        50.00000000
VR-5321    1.96850394    25.6200        1968.50393701
VR-5321    2.95275591    38.4300        1968.50393701
VR-5321    39.37007874    .0000        1968.50393701

> It should work just as well with numbers. Probably the issue is data
> consistency, ie there are text numbers within cols C and H which fail
[quoted text clipped - 24 lines]
>
> - Show quoted text -

Reply to this Message

Max - 31 Jan 2007 16:25 GMT

Aha, so it's not an issue with text numbers. The earlier formula looks for
exact matches. So we need to ensure / fashion it such that there are exact
matches between both the lookup values and the values within the lookup
ranges.

Try this revision using ROUND (to say, 2 dp) on both the lookup ranges and
values, array-entered:
=IF(OR(F11="",H11="",
G11=""),"",INDEX(BT!$C$2:$C$994,MATCH(1,(BT!$A$2:$A$994=F11)*(ROUND(BT!$B$2:$B$994,2)=ROUND(H11,2))*(ROUND(BT!$E$2:$E$994,2)=ROUND(G11,2)),0)))

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>>D E F G
>>H

_____ ___ ________ _____________ __________
#N/A 32 VR-5321 50.00000000 25.00000000
#N/A 16 VR-5321 1968.50393701 1.96850394
#N/A 12 VR-5321 1968.50393701 2.95275591
#N/A 1 VR-5321 1968.50393701 39.37007874

{=IF(OR(F11="",H11="", G11=""),"",INDEX(BT!$C$2:$C$994,MATCH(1,(BT!$A
$2:$A$994=F11)*(BT!$B$2:$B$994=H11+0)*(BT!$E$2:$E$994=G11+0),0)))}

Sheet BT

>>A B C
>>D

_______ ___________ _______
______________
VR-5321 25.00000000 12.8100 50.00000000
VR-5321 1.96850394 25.6200 1968.50393701
VR-5321 2.95275591 38.4300 1968.50393701
VR-5321 39.37007874 .0000 1968.50393701

Reply to this Message

Rate this thread: