Please see the attachment.
In cell G12 a category is selected via dropdown. In cell H12, a color is selected via dropdown (contingent list).
What formula goes into cell I12 to return the correct Price?
Thank you.
Please see the attachment.
In cell G12 a category is selected via dropdown. In cell H12, a color is selected via dropdown (contingent list).
What formula goes into cell I12 to return the correct Price?
Thank you.
Last edited by boothexcel; 03-29-2013 at 07:25 AM.
Hi and welcome to the forum
try this...
=INDEX($A$1:$F$5,MATCH(H12,$A$1:$A$5,0),MATCH(G12,$A$1:$F$1,0)+1)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thank you FDibbins - that works for BATCH1, but not for BATCH2 or BATCH3, any other ideas?
Try this
=INDEX(INDEX($A$2:$F$5,,MATCH(G12,$A$1:$F$1,0)+1),MATCH(H12,INDEX($A$2:$F$5,,MATCH(G12,$A$1:$F$1,0)),0))
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
ChemistB - baller. Thank you.
ChemistB,
How about this format?
Thanks
It seems to work for all 3 batches, but not when adjusting colors for all 3 batches.
The solution that ChemistB provided works.
I have posted an alternate setup as well; looking for a solution to that arrangement. Thanks.
Hi boothexcel
Try the VLOOKUP in I12.
Formula:Please Login or Register to view this content.
Regards Kevin
Merged Cells (They are the work of the devil!!!)
Hi boothexcel
Open the wrong file! Try in F20 in the file you attached in post 6#
Formula:Please Login or Register to view this content.
Hi Kevin UK, thank you. It seems to work except for the first color in each Batch. Thoughts?
Hi boothexcel
Sorry my mistake, try:
Formula:Please Login or Register to view this content.
Kevin UK, thank you, fantastic
No problem boothexcel and thanks for the feed back.
Kevin UK,
How would your formula change if each Batch had a different number of colors?
Kevin UK,
For Batches with different numbers of colors, I made each batch have a row height equal to the largest number of colors (some cells were blank), and then adjusted your formula for the offset height to reflect the row height. Works 4.0. Thanks to all for the assist, much appreciated.
Hi boothexcel
Try:
Formula:Please Login or Register to view this content.
No that will not will not work.
You mightbe better off keeping things simple. if you keep the data like you had it in your first post you could set the ranges to be dynamic.
Last edited by Kevin UK; 03-29-2013 at 10:24 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks