Solved

Populate a field based on a selection in excel 2003

Posted on 2012-03-20
13
244 Views
Last Modified: 2012-03-22
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?
0
Comment
Question by:tmaususer
  • 7
  • 6
13 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37746087
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
 

Author Comment

by:tmaususer
ID: 37746932
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37748169
THat would require a macro

Chris
0
 

Author Comment

by:tmaususer
ID: 37748196
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37748281
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
 

Author Comment

by:tmaususer
ID: 37748530
Chris,

I wouldn't know how to do that.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37748980
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
 

Author Comment

by:tmaususer
ID: 37749178
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37749279
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
 

Author Comment

by:tmaususer
ID: 37749404
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37751022
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
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 200 total points
ID: 37751211
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
 

Author Closing Comment

by:tmaususer
ID: 37752289
Awesome Help....... Thank you very much
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now