Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Populate a field based on a selection in excel 2003

Posted on 2012-03-20
13
Medium Priority
?
282 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

647 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