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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
wow, looks great. so far works perfect.

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

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top