+ Reply to Thread
Results 1 to 3 of 3

Guidance required on what to search for to help solve an issue in Excel 2003

Hybrid View

Rhothgar Guidance required on what to... 02-13-2019, 07:48 PM
dflak Re: Guidance required on what... 02-14-2019, 01:31 PM
Rhothgar Re: Guidance required on what... 02-25-2019, 06:10 PM
  1. #1
    Registered User
    Join Date
    10-30-2011
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2003
    Posts
    5

    Guidance required on what to search for to help solve an issue in Excel 2003

    Hi

    I have a spreadsheet which calculates profit for items bought and sold.

    I want to grow it so it is more 'intelligent'. It currently has a row for Postage Cost but instead of manually entering this all the time I would like a drop down or two where I can specify weight of the item and also size and if it fits within those parameters it tells me what the cheapest possible postage option is from several ranges of postal options.

    So my dilemma is I don't know where to start.

    a) Should I have a Worksheet for Royal Mail, one for DPD and another for our other courier?
    b) Should those Worksheets then be defined as named ranges or something else?

    I suppose this is quite hard for anyone to visualise but if an item weighs less than 2kg, it normally goes with Hermes unless one side is greater than 12cm in which case it may or may go with Hermes but Royal Mail. If over 2kg, it is likely to go with DPD

    I've attached the Royal Mail 'range' to show the costs and weight bands. At the moment, this is on the calculation sheet so should probably be moved to its own sheet. I've yet to create the DPD and Hermes options.

    If I was to go more advanced, it would make sense to include some kind of drop down for item value and whether I want it fully insured. However, that would add a whole new level of complexity.

    I imagine most of the stuff I need to look at is logical arguments but I'm just taking a stab in the dark quoting that with my limited knowledge.

    I might even have it all laid out wrong. Maybe VLOOKUP or HLOOKUP would be of use?

    Genius help appreciated...

    TIA



    Capture.JPG

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,957

    Re: Guidance required on what to search for to help solve an issue in Excel 2003

    Investigate VLOOKUP with the TRUE option. It is designed with "stair step" ranges like you have in mind.

    As for drop down list, look at data validation: http://www.utteraccess.com/wiki/Data_Validation.

    If you think the list that you are validating against might be dynamic then you can use the offset command: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges.

    It's been quite a while since I've used Excel 2003. I think that if you are trying to reference a range on another sheet for the purposes of data validation, then you must give it a name (either static or dynamic).
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    10-30-2011
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Guidance required on what to search for to help solve an issue in Excel 2003

    Many thanks for your input. I shall certainly look into it when I get more time again in the near future.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Excel genius required to solve problem!
    By Neal_Const_Planner in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-14-2018, 10:34 AM
  2. Formula/Syntax required to automatically insert date from percentage series of data
    By Neal_Const_Planner in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-14-2018, 06:32 AM
  3. please help with combination of formulas, array, vlookup
    By ohdiana in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-11-2014, 04:43 PM
  4. Replies: 1
    Last Post: 12-30-2012, 08:03 PM
  5. Excel code required to solve puzzle
    By vasant in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-09-2011, 04:25 AM
  6. Help required to solve a linear equation through excel
    By souravroy1@rediffmail.com in forum Excel General
    Replies: 1
    Last Post: 08-19-2006, 08:20 PM
  7. [SOLVED] Incorrect Japanese guidance with Excel 2003
    By j-mura in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-05-2005, 07:06 PM

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