Pre-populating

greentom22

New Member
Joined
Sep 13, 2006
Messages
6
Hi

I'm trying to figure out the easiest way to pre-populate cells based on a selection in a central cell above.

When a user selects a certain number in a drop-down box in a cell, I would like that selection to be automatically inserted into the new cells as a default.

A further wrinkle would be that these defaults would be inserted only if a cell in that row has a number entered.

So: If A1 = 24% (selected from drop down box)
Then C3 = 24% (automatically selected from drop down box but can be changed)
Only if A3 = 1 and not empty (ie user has selected to use this row.)
If A3 is empty, then B3 is empty.

So far I've done the whole sheet without using VBA & macros (I'm a amateur at VBA) but I have a feeling I'll need to use VBA to get this done. So please, as simple as possible would be great.

Thanks
GreenTom
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,940
Office Version
  1. 365
Platform
  1. Windows
Yes, this can be done, using VBA.

Right click on the Sheet tab name, select View Code, and paste this code in the resulting box:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$A$1" And Target <> 0 and Range("A3")=1 Then
        Range("C3") = Target
    End If

End Sub
Now, whenever a change is made to cell A1 (that enters a value in A1), cell C3 will automatically be updated.
 

greentom22

New Member
Joined
Sep 13, 2006
Messages
6
Re: Pre-populate

Thanks a bunch. That seems to work.

How do I increase the range from say C3 to include down to C8? I tried simply changeing to C3:C8 but I get a "Run Time Error '13' Type Mismatch." Then when I debug it highlights the Target Address section.

Also, can I effect the pre-populating so that C3 is populated only if A3 = value selected from a drop-down box in that cell?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,940
Office Version
  1. 365
Platform
  1. Windows
To populate C3:C8, this will work:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    
    If Target.Address = "$A$1" And Target <> 0 And Range("A3") = 1 Then
        For Each cell In Range("C3:C8")
            cell = Target
        Next cell
    End If

End Sub
In order to get it to trigger based on a drop-down box, you might need to use a different Event, like Worksheet_Calculate instead of Worksheet_Change. I will need to look further into that.

Here is a good write-up on all the Event Procedures:
http://www.cpearson.com/excel/events.htm
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,940
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What kind of drop-down box are you using?

I tried a Data Validation drop-down box, and the Worksheet_Change event procedure seems to work fine with that.
 

greentom22

New Member
Joined
Sep 13, 2006
Messages
6
pre-populate

thanks JM. yes i am using validation lists.

this seems to be working. i changed the code a bit so that the range reads: Range("A3") <> 0 so that any value will cause the entry of the master cell value into the respective pre-populated cells.

I'm having a couple problems though. I can't seem to get them the individual lines to work individually.

For example, the user would fill in A1 from a selection produced from a validation list. lets say its 25%

next the user would go to A3 and from a validation list pick a number, lets say 3. My goal is then choosing this number would populate cell c3 (different column in the same row) with the original 25% from a validation list. then the user could change this pre-populated 25% to whatever he wanted and not effect anything else.

the user would then go to the next row, row 4, and repeat the procedure. but these rows would be blank until the selection of A4, which then would populate C4 with the original 25%.

also, i'm getting some error messeges whenever i try to clear the sheet (ie delete the values in the individual validation lists.) Any to stop this?

thanks for your help.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,940
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

OK, I sense you simplified your original problem, and now are beginning to add in the complexities (multiple rows, etc).

In order to develop a solution that EXACTLY fits your needs, we need to know all the rules/conditions up front, like:

-What is the entire range this is going to be applied to?
-Which cells being populated should "trigger" the automated pre-population (you mentioned A1 and A3, I'm not sure which one changing "drive" the whole process).
-Any other caveats
 

greentom22

New Member
Joined
Sep 13, 2006
Messages
6
Ok, I'll start using the real cells. Everything before was for simplification.

In cell N6, a user enters a percent from a validation list. Lets say 25%

Then a user will select a quantity from a validation list in cell B25. Typically between 1-10 but can be up to 100. Clicking in this cell and choosing will populate the 25% into cell L25. (basically what I'm doing is guaranteeing that cell L25 will not be ignored and left blank. it commonly is ignored but is very cruicial to company profit.)

After they've finished with row 25, the process can be repeated if desired in row 26 thru 32. Again, choosing quantity in B inserts value into L.

This will also be repeated in 38 to 45 and 51 to 58. Same column references.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,940
Office Version
  1. 365
Platform
  1. Windows
OK, give this a shot:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRange As Range
    
'   Set range to trigger macro if a value is entered into this range
    Set myRange = Range("B25:B32, B38:B45, B51:B58")
    
'   Check to see if cell just changed is in specified range
    If Not Intersect(Target, myRange) Is Nothing Then
'   If there are values in N6 and specified range, copy N6 to column L of same row
        If Target <> 0 And Range("N6") <> 0 Then
            Target.Offset(0, 10) = Range("N6")
        End If
    End If

End Sub
 

greentom22

New Member
Joined
Sep 13, 2006
Messages
6
wow, looks great. so far works perfect.

what does the offset (0,10) mean? Is it because B is 10 columns away from L?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,676
Messages
5,549,374
Members
410,911
Latest member
AniEx
Top