cell updating question

[aj_gabby]

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

 

[Tom James]

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!

 

[Max]

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.

 

[aj_gabby]

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

 

[Max]

But sheet A. is in a formated layout like
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

---

 

[aj_gabby]

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

 

[Max]

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)

---

 

[aj_gabby]

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 .

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)

---

 

[Guest]

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

 

[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

 

[Max]

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

 

[aj_gabby]

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
_____ ___ ________ _____________ __________
#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
_______ ___________ _______
______________
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

 

[Max]

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

---
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
_____ ___ ________ _____________ __________
#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
_______ ___________ _______
______________
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