pingvin

New Member
Hi,

I have a work book with two sheets.

On the Sheet1
I have a table with data for Restaurant in cells A1 to I4
Then I have data for Super market in cells A6 to I9
Finally I have data for Coffee shop in cells A11 to I14
 A B C D E F G H I 1 Restaurant Discount offer Low Medium High 2 Min Max Min Max Min Max Min Max 3 Price 1 300 301 600 601 1000 1001 60000 4 Gross Margin 30% 100% 55% 100% 50% 100% 45% 100% 5 6 Super Market Discount offer Low Medium High 7 Min Max Min Max Min Max Min Max 8 Price 1 300 301 600 601 1000 1001 60000 9 Gross Margin 1% 10% 11% 35% 35% 100% 40% 100% 10 11 Coffee shop Discount offer Low Medium High 12 Min Max Min Max Min Max Min Max 13 Price 1 300 301 600 601 1000 1001 60000 14 Gross Margin 45% 100% 50% 100% 55% 100% 55% 100%

<tbody>
</tbody>

On Sheet2
I have another table
 B C D E F G H I J K 3 Price Category: Discount offer 4 Area: Restaurant 5 6 7 Item Code Supplier Item Unit Std.Cost QTY(g) Act QTY Act Cost 8 1 20001 Supplier A Spaghetti Bags 2 1000 1 2.0 9 2 10 3 11 4 12 5 13 6 14 7 15 8 16 9 17 10 18 11 19 12 20 13 21 14 22 15 23 24 Price GM 25 \$10.00 80%

<tbody>
</tbody>

Based on the value in cell D4 and J25 in Sheet2 I want the update the cell value in D3 on sheet2.
For example if the value in D4 is "Restaurant" and the cell value in J25 is "\$10.00" I want it to get the value from cell B1 "Discount offer" on Sheet1 and show this in cell D3 on sheet2. Because the value in D4 states restaurant which means that the values located somewhere between cell A1 to I4. Further more since the price in cell J25 is \$10.00 which falls between the values in cell B3 to C3 on sheet1. Cell D3 on sheet2 should show " Discount offer".

Next is "GM" in cell K25. If this falls between the "Discount offer Min and Max" values nothing should happen but if it falls out of the Min and Max the cell should be highlighted in red color.
I pasted a example file which basically shows the same as I have described.

If the Value in D4 for example should show "Coffee shop" then the value for D3 should be found somewhere inbetween cell A11 and I14 on sheet1. The gross margin value in cell J25 should be matched with the values in between B14 to I14, if values matches the between the Min and Max values nothing should change but if the values falls out of the Min Max the cell color changes to red.

https://1drv.ms/x/s!AgXqCHJekjZxapUdFYClVY3qxZc

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

AlanY

Well-known Member
try this

formula for CF J25 & K25

Code:
``=\$K\$25 < INDEX(Sheet1!\$A\$1:\$I\$14,MATCH(\$D\$4,Sheet1!\$A\$1:\$A\$14,0)+3,MATCH(\$D\$3,Sheet1!\$A\$1:\$I\$1,0))``

AlanY

Well-known Member
btw, the formula in D3 takes advantage of Rows 3, 8 & 11 on Sheet1 are identical as in your example file, otherwise it wouldn't work

pingvin

New Member
Thanks for the quick answer AlanY. Unfortunatly the prices on rows 3, 8 and 13 will differ depending on if it for Restaurant, Super market or Coffee shop. So I guess it won't work.

Replies
6
Views
415
Replies
5
Views
287
Replies
3
Views
757
Replies
1
Views
55
Replies
11
Views
986

1,141,875
Messages
5,709,113
Members
421,616
Latest member
Rachy_bo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back