+ Reply to Thread
Results 1 to 4 of 4

Options that can overwrite rule for every row in Spreadsheet

  1. #1
    Registered User
    Join Date
    04-01-2020
    Location
    Brisbane, Australia
    MS-Off Ver
    Office 2010
    Posts
    3

    Options that can overwrite rule for every row in Spreadsheet

    Hi All

    I am comfortable in Excel but not an advanced user so what I want to do is exceeding my knowledge base:

    I am working on a spreadsheet - 200 rows
    Each row = 1 product
    Each product has local and overseas manufacturers with the local manufacturers not being a problem. The spreadsheet already has some formulas.

    The overseas manufacturers (4):
    For each manufacturer is a base price which is determined by a tick box (calculated on a different spreadsheet)
    price calculation for the product is generally based on the minimum price but at times it might have to be overwritten to select a certain manufacturer
    The rule is: minimum price which results in the manufacturer and the price to be copied in the relevant columns going forward.
    Exception: User selects manufacturer over price.

    I thought about using option buttons. 1 button for each row as default = minimum and one for each manufacturer as selector. Which meant 1,000 option buttons with 5 in each group. Having played around with it a bit I quickly realised that this is a ludicrous undertaking and am hoping someone has a better solution to this. I have no knowledge of VBA but am great at cutting & pasting code and stitching it together.

    I have a sample file attached and hope it makes sense.

    Hope even more that one of you smart people can help. Thank you in advance
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: Options that can overwrite rule for every row in Spreadsheet

    May be you do not need VBA if I understood your problem

    At the moment your Manufacturer is selected on the lowest price via a formula.

    Proposal:
    Instead of this formula replace the Manufacturer by the value. Via conditional formatting you can highlight the manufacturer if it is not the cheapest one (see attached file).
    in column S (Imp Cos USD) you exchange formula =MIN(N5:Q5) with =INDEX($N4:$Q4;;MATCH($R4;$N$3:$Q$3;0)) to get the corresponding price
    I also added a dropdown box to select your manufacturers

    cheers
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-01-2020
    Location
    Brisbane, Australia
    MS-Off Ver
    Office 2010
    Posts
    3

    Re: Options that can overwrite rule for every row in Spreadsheet

    Hi Gue2013

    thank you for the fast reply.

    One question though: do I now have to select the manufacturer for every product rather than pre-selecting the MIN and having the option of changing? I have the feeling I am missing something here. (the selection of MIN price is the rule, the change of manufacturer the exception)

  4. #4
    Registered User
    Join Date
    04-01-2020
    Location
    Brisbane, Australia
    MS-Off Ver
    Office 2010
    Posts
    3

    Cool Re: Options that can overwrite rule for every row in Spreadsheet

    Hi All

    thank you for looking into this. And thank you again to Gue2012 for a solution. It didn't quite work the way I intended.

    I have now decided on a roundabout solution which is not as elegant or clean but it works.

    I have my solution attached as a sample file in case someone else is looking for it.

    Cheers

    Kat
    Attached Files Attached Files

+ 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] Data Validation Rule: Not Function with Exceptions to the rule
    By KidZest in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-18-2019, 11:45 PM
  2. Replies: 6
    Last Post: 02-18-2015, 03:40 AM
  3. [SOLVED] Add to rule macro that exports Body, date/time sent, and sender into excel spreadsheet.
    By vamosj in forum Outlook Formatting & Functions
    Replies: 2
    Last Post: 01-21-2015, 12:09 PM
  4. Automate marker options in spreadsheet?
    By emergencyfan in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-05-2014, 05:04 AM
  5. Replies: 20
    Last Post: 11-15-2012, 09:32 PM
  6. Excel Spreadsheet Options not Remembered
    By john essery in forum Excel General
    Replies: 1
    Last Post: 07-06-2012, 09:15 AM
  7. saving options along with spreadsheet
    By woody in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-01-2005, 03:24 PM

Tags for this Thread

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