Complex Price recall from a table on another sheet

Hi there,

I'm hoping someone can help me with a problem that I'm having.

I'm working on the attached sheet and am hoping to use this as a quoting tool to enable us to go paper free.
I have everything else I need in place except the pricing - I can not figure out how to get the correct prices bought in from the table on the "prices" tab.

In the full version of the sheet there is a resources tab with a list of all of my fabrics on and each is given a price band.
I need a way to distinguish which table these are bought down from; price range a, price range b etc.

I also need the values to be rounded up to the next value on the table - e.g. a width of 2000 would need to be taken from the 2134mm column.

Looking forward to hearing from someone

Thanks,
Kirsty

Hi Kirsty, maybe not the best way, but I made a helper column on the Prices page (realising now that it could just as easily be on the resources page if that was easier).

Formula is:

Please Login or Register  to view this content.

It uses INDEX/MATCH to find the row number within Prices!A6:A57 (helper column) with the combination of Price Band and the row with next greater width than E11.
It then uses INDEX/MATCH to return the column number of the next greatest drop than F11.
Then it uses INDEX to find the appropriate price from the price table using that row no. and column no.
After that it multiplies the imported price by the qty in D11.
If there is an error (nothing to lookup or outside of ranges) it will display "".

Originally Posted by AliGW

PS Please update your user profile. There is no such version as Office 360 - it's Office 365. as in days in a year, not degrees in a circle). Thanks.

Thanks for the welcome, apologies - profile has been updated!

Thanks Beamer, works beautifully!

Would this formula still work if I wanted to link to various tables or would it get too messy?
(For example if I want to use different price lists for each type of blind depending on what is selected in cell C11)

Hi again, I just realised that I lied in my last post. I said that "the widths can change for each type" which is wrong.
I'm working on a table so that you can have different sizes for widths and drops in your calculations if necessary.

Thanks again Beamer, so grateful for your help!

I'll pop this into the full sheet and see how I get on!

Hi Beamer,

Just realised that the width and drop are the wrong way round in the formula - on the price sheet the width is across the top and the drop is on the left side next to the helper column. Is it simple enough to rewrite the formula to reflect this?

Thanks,
Kirsty

Thank you for your help

Hi Kirsty, I think it's fixed now. All I did was swap the E11 and F11 cell references in the formula.

Thanks so much, hope you're safe and well.