?
Solved

Populate a field based on a selection in excel 2003

Posted on 2012-03-20
13
Medium Priority
?
280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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
 
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 800 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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 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