+ Reply to Thread
Results 1 to 3 of 3

data validation-creating a costing model

  1. #1
    Registered User
    Join Date
    09-12-2008
    Location
    Cardiff
    Posts
    7

    data validation-creating a costing model

    Please can anyone help me with a data validation problem? I am creating a costing model where the user makes a variety of selections from pull down lists I have created in data validation.
    In one section the user chooses a carton type of G1, G4 or other using list in cell A. When the user selects G1 or G4, cell B populates (via lookup) with no. of rolls in that size carton. The user cannot overtype this figures. If the user selects 'other', cell B defaults to zero and user is instructed to enter roll qty into cell C. The user can only enter into cell C if cell B is zero.
    This appears to work okay except where the user firsts selects 'other' in cell A, enters value in cell C and then changes carton type in cell A to G1 or G4. The qty for G1/G4 automatically updates AND we have the manual entry for the 'other' carton type. Without having to rely on user to know that they need to delete the 'other' qty how can I get cell C to automatically zero if qty in cell B. At the moment the data validation in cell c is as follows

    =IF(Q29=0,TRUE).
    Last edited by VBA Noob; 09-14-2008 at 05:48 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,613
    i think the easiest solution would be to Conditional Format the C cells such that they turn red if both B and C values are greater than zero and let the user enter zero in C. I don't know if you can zero C without circluar reference errors.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    09-12-2008
    Location
    Cardiff
    Posts
    7
    Many thanks, I will try that. As extra protection I have been through the detailed workings and put IF function on any workings involving the quantities in these two cells.

+ 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. Sorted data in drop list (data validation)
    By ONB in forum Excel General
    Replies: 4
    Last Post: 08-30-2012, 07:22 AM
  2. Replies: 11
    Last Post: 06-27-2008, 04:39 PM
  3. Extract and summarize from 100 worksheets into one
    By HMIExcel08 in forum Excel General
    Replies: 6
    Last Post: 06-23-2008, 10:08 AM
  4. Data validation - when copied & pasted validation overlooked.
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-23-2008, 04:29 PM
  5. scroll bar usage with data validation
    By maacmaac in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-30-2008, 12:03 AM

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