+ Reply to Thread
Results 1 to 10 of 10

Generate a form IF another cell has a specific value but otherwise use vlookup

  1. #1
    Registered User
    Join Date
    09-14-2011
    Location
    Newton Abbot, England
    MS-Off Ver
    Excel 2007
    Posts
    23

    Generate a form IF another cell has a specific value but otherwise use vlookup

    I have posted before but i am trying to hit my problem from a different angle , heres what i am trying to do...

    I enter data into a cell (Say A1) from a data validation list which includes the "Specific value"
    the next cell (B1) uses VLOOKUP for its corresponding data
    (C1) also does this but returns a different column

    If the value in A1 is specific value (eg "*" ) from the list I would like a manual input of the values for B1 and C1

    This whole process occurs around 280 times on the sheet .

    I am green onthe use of VBA but recgonise it probably uses Range and ActiveCell

    Can someone PLEASE help
    Last edited by Wobbles; 09-20-2011 at 02:47 AM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Generate a form IF another cell has a specific value but otherwise use vlookup

    What exactly do you want to happen?
    I assume (tell me if I'm wrong), that this is a protected spreadsheet and therefore cells B1 and C1 are protected. You could set up a VBA routine that unprotects your spreadsheet, clears C1 and B1, then unlocks C1 and B1 and reprotects your spreadsheet.

    Is this specifically for row 1 or will this be carried through down the column(s)? Is this something you will be changing again and again (thus will want those formulas put back in)? or will you be working from this template and thus each use will be permanent?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    09-14-2011
    Location
    Newton Abbot, England
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Generate a form IF another cell has a specific value but otherwise use vlookup

    Quote Originally Posted by ChemistB View Post
    What exactly do you want to happen?
    I assume (tell me if I'm wrong), that this is a protected spreadsheet and therefore cells B1 and C1 are protected. You could set up a VBA routine that unprotects your spreadsheet, clears C1 and B1, then unlocks C1 and B1 and reprotects your spreadsheet.

    Is this specifically for row 1 or will this be carried through down the column(s)? Is this something you will be changing again and again (thus will want those formulas put back in)? or will you be working from this template and thus each use will be permanent?
    Thank you for responding .

    Yes I do intend to lock and protect the cells / sheet
    In summary i am trying to in groups of three horizontal cells do the followng.
    The leftmost cell is simply to select a standard shift (eg Early , Late etc. ) which provides the value for the lookup
    The other two cells fill with the Shift START time and FINISH time
    There are however occassions when these times need manually adjusted.

    I cant just key these values into the cells because i would lose the formula for future use (eg if the lookup value is re-edited )

    I was going to try to just use the cell change VBA to re paste the formula if the A1 (for example) was NOT "*" but i am again unsure as to how to do this bearing in mind this will happen in various cell locations at random

    I want to find an efficient way to do this AND learn from it , sadly at the moment VBA is tough going.. I WILL GET THERE EVENTUALLY
    Last edited by Wobbles; 09-16-2011 at 12:53 PM. Reason: critical omission (added the word NOT)

  4. #4
    Registered User
    Join Date
    09-14-2011
    Location
    Newton Abbot, England
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Generate a form IF another cell has a specific value but otherwise use vlookup

    Is it something im doing wrong ?

    My post never seemed to be solved :-(

  5. #5
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Generate a form IF another cell has a specific value but otherwise use vlookup

    this will happen in various cell locations at random
    You'd need some way of telling excel which cells you want to look up.

    Can you post a sample of your workbook?

  6. #6
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Generate a form IF another cell has a specific value but otherwise use vlookup

    Wobbles,

    Attached is an example workbook based on the sample criteria you provided. It contains two worksheets:
    'Schedule' - The worksheet where you can type in a person's name and select their shift
    'Shift Hours' - The worksheet containing the different shifts and the vlookup table.

    I set the "Specifc Value" in this example to be "Custom". When Custom is selected from 'Schedule' column B, you'll be prompted for 2 input boxes, the start time and end time. Note you'll have to put in the am/pm when typing in these or it won't work correctly. So for start time you could put in 4 am for 4:00 am, or 6:30 pm for 6:30 pm, etc. If its changed away from "Custom", it replaces cells C and D with the vlookup formula. The 'Schedule' worksheet password is "123" (no quotes).

    Here's the Worksheet_Change event code for the 'Schedule' worksheet. Change the ShiftCol, CustVal and wsPW as necessary.
    Please Login or Register  to view this content.


    Hope that helps,
    ~tigeravatar
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-14-2011
    Location
    Newton Abbot, England
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Generate a form IF another cell has a specific value but otherwise use vlookup

    Quote Originally Posted by Kyle123 View Post
    You'd need some way of telling excel which cells you want to look up.

    Can you post a sample of your workbook?
    please look at another post i have and what follows

    http://www.excelforum.com/excel-prog...r-similar.html

    This is where i am thus far
    Please Login or Register  to view this content.
    I am worried this is looked at a double posting but it has not been my intention as i was trying to hit the problem from different angles .
    Please comment any code so that I learn from it

  8. #8
    Registered User
    Join Date
    09-14-2011
    Location
    Newton Abbot, England
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Generate a form IF another cell has a specific value but otherwise use vlookup

    Quote Originally Posted by tigeravatar View Post
    Wobbles,

    Hope that helps,
    ~tigeravatar
    It all helps , i cherish all the help i can get - Thank you

  9. #9
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Generate a form IF another cell has a specific value but otherwise use vlookup

    Wobbles, did my post address what you were looking for...?

  10. #10
    Registered User
    Join Date
    09-14-2011
    Location
    Newton Abbot, England
    MS-Off Ver
    Excel 2007
    Posts
    23

    Re: Generate a form IF another cell has a specific value but otherwise use vlookup

    Quote Originally Posted by tigeravatar View Post
    Wobbles, did my post address what you were looking for...?
    To a degree it has yes and thank you. I have managed to get to the stage where i can produce the two input boxes however it was done differently to your way with the same or a similar result . I am still looking at the code and deciphering it ( im still learning VBA).

    if i were to have a form come up instead where i can key in the start and finish times in raw format it would be less keyboard intensive for the user eg 2115 instead of 21:15 or 0915 PM

    my requirement is not to choose the employee selected for the hours as i have tostick with someone else's existing layout ., my mission is simply to make it simpler for the user who is barely pc literate let alone excel familiar.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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