Price Grid [index/lookup/match]

**casey63** · 03-16-2012, 10:53 AM

Hi Everyone.

My first post on hear and to be honest a complete novice to Excel

I need help in two ways, would very much appreciate anybodys help.

I need to produce a spreadsheet to use as a price list. Its for my window company and I need to be able to input a width and height figure in to cells on a seperate sheet to reach a price (it also needs to round up if someone inputs a size slightly over one figure but under the next....if that makes sense? I have 50 or so window styles which all need a grid. Can anyone please help me with this?

Also I would be interested if its possible that once the price grids have been done for me to be able to incorporate a method of 'four corner pricing' ie that i only need to input a figure in the four corners of the matrix manually and the rest are then calculated for me, this is purely just to save me time rather than having to input every figure for ever style.

All this seems impossible to me but looking at some of the help and guidence you guys have given to others....I'm keeping me fingers crossed and would be eternally grateful.

Thanks in advance.

**darknation144** · 03-16-2012, 11:12 AM

You need to post a sample workbook. I can help you at least with the first one but I'm not going to type what data you might have when you could just post what you have.

As for the grid pricing strategy it's possible depending on the rules you want to use.

**casey63** · 03-16-2012, 02:30 PM

Hi. Thank you for your quick response....I really appreciate it.

Please find attached (in its very early form) the sheet i require.

Essentially i want to be able to input a style code in cells c6:c25 of the 'check sheet' page, then a width and height figure and for a price to appear. The rest of the sheet I think i can manage. The cells in orange are just to show the inputer that these are the only cells to be typed in.
The grids on the 'price list' and 'basic price' will eventally be bigger as I have around 50 styles. Ive shown style 1 and 2 but i would like to be able to just input the data on the 'basic price' page and for the figures to appear marked up on the 'price list' page. This will help in the future if i have to implement a price increase. Essentially then the price that appears on the 'check sheet' will come from the 'price list' page.

My apologies for my rather basic 'wording' of what i require. Any help you can give me is truly appreciated.

**FDibbins** · 03-16-2012, 07:13 PM

Try the attached, it doe what you need. The only thing is, I used named ranges to automate the the lookup, based on whatever name you put into check sheet style code column. You will see that I have 3 named ranges on your price list sheet, for each pf your 2 tables...I named the ranges after the "style code". The 1st range if the entire table, the 2nd range is the width range and the 3rd is the height range.

So you have a style1 for the entire table, style1row for the width range and style1column for the height range. The same applies fort style2.

I know this may be a little bit long winded, because you will have to create 2 ranges for each table that you have, but its the only way I could figure out to work with a multitude of different lookup on potentially different sheets.

Try this and let me know if it does what you want, there is probably a simpler way, without the range names, but off hand, i cant think of it (yet). Let me know if you need any more explanation on how it works

**casey63** · 03-27-2012, 04:05 PM

Thank you so much for your help with this an my apologies for the delay in my reply. I can see that it does work, but i do have some questions if you dont mind. Its quite important that a number only is input in the style rather than having to keep typing in 'style1' etc etc...is this possible. And how do i go about adding more price/style grids as i have around 50?

**Richard Buttrey** · 04-01-2012, 05:26 PM

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your latest thread.

Thread Closed.