Copy a Vlookup formula into a cell of newly inserted row XL07

JohnJames

New Member
Joined
Jan 27, 2014
Messages
5
Hello,

Excel 2007, Windows 7

I have a worksheet I've been assigned to automate for work. Essentially I'm creating it in order to eliminate human error; and cut the users time of data entry in half (possibly).

Currently I'm working on a Vlookup formula that works great! but I quickly came to the realization that when inserting a new row; formulas don't copy from the other cells. I can't place the vlookup formula in every cell of the column because they insert new rows pretty frequently

Is there any way I can fix this issue? I'm just starting to learn macro/vba and I'm pretty sure that may be the way to go, but I have no clue where to begin.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hi JohnJames,

A simple alternative would be formatting your data range as table (i.e. select your entire range of data, in Home menu click [Format as Table] under Styles)
Thus whenever you add new row, the formulas will be copied down accordingly.
 
Upvote 0
Thanks for the help exceldevs!

I've heard of this alternative and tried to execute it once before but it didn't work. The whole row will not have the vlookup formula. Here's a sample of one category showing the setup. The user can add a new row for "Category info", but "Category 1" and "Category 2" will not have the vlookup formula.


A
B
C
D
E
F
G
1
Category 1
Hours week of.....
Hours week of.....
Hours week of.....
Hours week of.....
Total Hours
2
Category info
4
4
1
9
3
Category info
8
9
17
4
Category info
4
2
6
1
13
5
Category info
8
6
5
8
27
6
7
Category 1(Total Hours)

<tbody>
</tbody>

8
Category 2
Hours week of.....
Hours week of.....
Hours week of.....
Hours week of.....
Total Hours
9
Category info
4
4
1
9
10
Category info
8
9
17
11
Category info
4
2
6
1
13
12
Category info
8
6
5
8
27
13
14
Category 2(Total Hours)

<tbody>
</tbody>
 
Upvote 0
Say there are formula in G2:G5, do you mean if user insert new row after row4 (i.e. G5 become G6) G5 will inherit the formula from G4?
 
Upvote 0
Thanks for the help exceldevs.

So I decided to go with your first suggestion, which was creating a table and placing the formula in that row. I had so many cells that didn't need the table it took me around 50+ minutes to remove them and place the original information back in there, I think that's better than continuing to search for a "better" solution or creating a macro for it. Plus this will be the only time I would have to do that many cells anyway.

Thanks again!
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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