Closed Thread
Results 1 to 6 of 6

Price Grid [index/lookup/match]

  1. #1
    Registered User
    Join Date
    03-15-2012
    Location
    Oxford, England
    MS-Off Ver
    Excel 2003
    Posts
    6

    Price Grid [index/lookup/match]

    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.

  2. #2
    Forum Contributor darknation144's Avatar
    Join Date
    01-24-2012
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    552

    Re: Price Grid [index/lookup/match]

    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.

  3. #3
    Registered User
    Join Date
    03-15-2012
    Location
    Oxford, England
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Price Grid [index/lookup/match]

    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.
    Attached Files Attached Files

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,108

    Re: Price Grid [index/lookup/match]

    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
    Attached Files Attached Files
    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

  5. #5
    Registered User
    Join Date
    03-15-2012
    Location
    Oxford, England
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Price Grid [index/lookup/match]

    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?

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Price Grid [index/lookup/match]

    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.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1