Populate a field based on a selection in excel 2003

I don't use excel that often but I am stuck until I can convert the data. I have an excel file and the first tab contains two cells. Cell a1 has a heading of supplier, cell b1 has a heading of supplier number. I have a second tab called suppliers that contains the actual data of suppliers and supplier numbers. I was able to create a drop downlist in the first tab so basically you can press an arrow button and the list of suppliers shows up based on the supplier data in the suppliers tab. My question is what do I have to do to have the supplier number populate in cell b1 automaitcally based on the supplier they select in the "a" cells. I don't know how to tie this from my suppliers tab or the syntax. Does that make sense?
tmaususerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris BottomleySoftware Quality Lead EngineerCommented:
Specifically it will depend on how you implented the dropdown so the following assumes:

1. a range freddie that identifiesd the values for the drop downs
2. A header row exists
3. the range freddie is to the left of the data required in the 'linked' column on the first sheet.

=IF(ISNA(VLOOKUP(A2,freddie,1,FALSE)),"",OFFSET(Sheet2!A1,MATCH(A2,freddie,FALSE),1))

It is essentially:

I not found ... i.e. blank then return blank, (=IF(ISNA(VLOOKUP(A2,freddie,1,FALSE)),"",)
otherwise find the column a reference in the freddie column, then move from the reference cell, the appropriate number of rows and columns to return the datum required.

If you are using a hard coded range then replace freddie with the relevant range - but since the reference list is on sheet 2 I think you will be using a name hence this implementation.

Chris
0
tmaususerAuthor Commented:
I was able to do some reading on vlookups last night and it is working correctly. Now my issue is making certain cells "mandatory". Example cell A1 is a date field. So if cell A1 has data entered then cell B1, E1, and F1 must have data entered. Any ideas?
0
Chris BottomleySoftware Quality Lead EngineerCommented:
THat would require a macro

Chris
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

tmaususerAuthor Commented:
could I do something like this.

if[a1].value = "" THen
msgbox "something",vbokonly, "entry required"
[a1].select
cancel=true
end if

I think i'm close.
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Th eproblem is detecting change ... and allowing for a drag.   Detecting selection change occurs AFTER the row has changed.  Thereafter which row were you on?

You can detect a change in a cell and test the data across that row, but that does not address the selection change.

We can for example add a variable to store the 'old' row and examine it each time there is a selection change resting the focus back to the previous row.  This tends to cause synch issues like errors wipe out the datum and changing worksheet / workbook.

In short it can be done but it introduces a fair number of issues.

Chris
0
tmaususerAuthor Commented:
Chris,

I wouldn't know how to do that.
0
Chris BottomleySoftware Quality Lead EngineerCommented:
We do but the root issue is trying to allow for all the errors that can occur ... it makes the workbook a lot more complicated to maintain and prone to failures therefore do you really want to do this?

Chris
0
tmaususerAuthor Commented:
Chris,

I'm not looking for anything complex. I know users will always find a way around things which is why I am getting rid of this log at some point but for now I have to live with it and improve upon it which I have done through the vlookup. The last real thing I need for example is:

cell a3 is where you would enter a date. If a3 is populated with a date the cell c3 and d3 need to be mandatory. I think I can finish it from there. Can you help with this?
0
Chris BottomleySoftware Quality Lead EngineerCommented:
To simply check those cells then put the following in the code page for the worksheet, (right click the tab and select code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range

    If IsDate([A3]) And [A3].Value <> 0 Then
        If [c3] = "" Then
            [c3].Select
        ElseIf [d3] = "" Then
            [d3].Select
        End If
    End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Range

    If IsDate([A3]) And [A3].Value <> 0 Then
        If [c3] = "" Then
            [c3].Select
        ElseIf [d3] = "" Then
            [d3].Select
        End If
    End If

End Sub

Open in new window


Chris
0
tmaususerAuthor Commented:
Chris,

I will give myself the idiot award of the day. So you code above works great but I wasn't thinking about the next entry below. So can you do a range like from a3:a2000 and repeat for the mandatory cells? If so how? I will increase the point to 200.
0
Chris BottomleySoftware Quality Lead EngineerCommented:
That's where the big problems come in with synchronisation.  i suppose to avoid all such concerns you could check every cell in the used range ...

Private Sub Worksheet_Change(ByVal Target As Range)
    checkRange
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    checkRange
End Sub

Sub checkRange()
Dim cel As Range

    For Each cel In ActiveSheet.UsedRange
        If IsDate(Cells(cel.Row, 1)) And Cells(cel.Row, 1).Value <> 0 Then
            If Cells(cel.Row, 3) = "" Then
                Cells(cel.Row, 3).Select
                Exit For
            ElseIf Cells(cel.Row, 4) = "" Then
                Cells(cel.Row, 4).Select
                Exit For
            End If
        End If
    Next

End Sub

Open in new window

0
Chris BottomleySoftware Quality Lead EngineerCommented:
A little more focussed and reduced processing would be:

Private Sub Worksheet_Change(ByVal Target As Range)
    workSheet_CheckBlanks Target
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    workSheet_CheckBlanks Target
End Sub

Private Sub workSheet_CheckBlanks(ByVal Target As Range)
Dim rngCheck As Range
Dim rw As Variant
Dim col As Integer
Dim testrange As Range

    Set rngCheck = Me.Range("a:a, c:c, d:d")
    If Intersect(rngCheck, Target) Is Nothing Then Exit Sub
    For Each rw In Intersect(rngCheck, Target).Rows
        If Me.Cells(rw.Row, rngCheck.Columns(1).Column).Text <> "" And IsDate(Me.Cells(rw.Row, rngCheck.Columns(1).Column).Text) Then
            For col = 1 To rngCheck.Areas.Count
                If Me.Cells(rw.Row, rngCheck.Areas(col).Column) = "" Then
                    Application.EnableEvents = False
                    Me.Cells(rw.Row, rngCheck.Areas(col).Column).Select
                    Application.EnableEvents = True
                    Exit Sub
                End If
            Next
        End If
    Next
    
End Sub

Open in new window


Chris
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tmaususerAuthor Commented:
Awesome Help....... Thank you very much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.