# Automatically filling in rows for a column of numbers

#### HeatherinAtwater

##### New Member
I do medical billing and I use a spreadsheet where the first column is a list of numbers (5 digits long) and after each number the rest of the row is a bunch of formulas. the codes get repeated randomly down the column and I end up tediously copying and pasting the formula for each number as I see it in the list. Is there any way once I have filled a bunch of them out to have it seach the rest of the list and fill the repeats in automatically?
I am new to this forum so I hope that makes sense
Heather

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

##### Well-known Member
To clarify:

You have a spreadsheet that consists of several columns. Column A is a 5-digit number. Columns B~ are all formulas. The formulas in columns B~ are dependent on the number written in Column A. For instance:

A1 = 12345
B1 = "=SUM(Sheet12345!A1:A10)"

A2 = 54321
B2 = "=AVERAGE(Sheet54321!A1:A10)"

If you enter 12345 in A3, you want B3 to automatically change to the same formula as B1, since A1 and A3 match?

#### HeatherinAtwater

##### New Member
That is much clearer thank you.

##### Well-known Member
It's possible with a macro.

Let's say I have this data:
Excel Workbook
AB
11231035
2345567
3567678
4789789
Sheet1

I want to have it add the appropriate formula if I put 123, 345, 567 or 789 into column A. I right-click the Sheet1 Tab and go to View Code, and paste this:
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngFind As Range

If Not Intersect(Target, Range("A:A")) Is Nothing Then
Set rngFind = Range("A1:A" & Target.Row - 1).Find(Target.Value, LookIn:=xlValues)
If Not rngFind Is Nothing Then
Target.Offset(0, 1).FormulaR1C1 = rngFind.Offset(0, 1).FormulaR1C1
End If
End If
End Sub``````
The values will auto-update when I enter text into column A.

Replies
22
Views
681
Replies
5
Views
170
Replies
1
Views
110
Replies
1
Views
86
Replies
2
Views
162

1,195,721
Messages
6,011,296
Members
441,600
Latest member
Ramatu

### 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.

### Which adblocker are you using?

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

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