Thread: 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

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?

Originally Posted by ChemistB

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

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?

Originally Posted by Kyle123

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

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit
    
With Target
    
    ' Finally cell(L3) should indicate a valid row and column with for example the word valid
    
    ' [ CHECKING OF THE NEED TO RUN THIS ROUTINE ]
    If .Row < 9 Then Exit Sub                       ' Checks that the row is within the area for running this routine
    If .Row Mod 2 <> 1 Then Exit Sub                ' Having checked the area now check to see if the row number is odd
                                                    ' This check means the routine will run on every second row based on
                                                    ' the rows needing this done are all odd numbered
    If Cells(8, Target.Column) <> "*" Then Exit Sub ' This checks the column is valid and works by a * being
                                                    ' placed in row 8 of a valid column 9this can be concealed
                                                    ' by making the font the same colour as the background
    
    If .Value = "*" Then                            ' Final check if * is selected run the routine below
    
   
        ' This Should run if the user is to choose * from the drop down list
        '
        Dim Start_time As String ' Set Shift Start Time
        Dim Finish_time As String ' Set Shift Finish Time
        Start_time = Inputbox("Start Time", "MANUAL ENTRY MODE 1 of 2") ' Input The Start Time

        Application.EnableEvents = False
        .Offset(0, 1).Value = Start_time ' Update cell with the entered start value
       
        Finish_time = Inputbox("Finish Time", "MANUAL ENTRY MODE 2 of 2") ' Input The Finish Time

        Application.EnableEvents = False
        .Offset(0, 2).Value = Finish_time ' Update the cell with the entered Finish time
    Else
        
        Application.EnableEvents = False
        .Offset(0, 1).Formula = "=VLOOKUP(" & .Address & ",$AY$9:$BB$20,3,FALSE)"
        .Offset(0, 2).Formula = "=VLOOKUP(" & .Address & ",$AY$9:$BB$20,4,FALSE)"
    End If
End With

ws_exit:
    Application.EnableEvents = True
End Sub

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 .

Originally Posted by tigeravatar

Wobbles,

Hope that helps,
~tigeravatar

It all helps , i cherish all the help i can get - Thank you

Originally Posted by tigeravatar

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.