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.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

exceldevs

Active Member
Joined
Aug 4, 2013
Messages
255
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.
 

JohnJames

New Member
Joined
Jan 27, 2014
Messages
5
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>
 

exceldevs

Active Member
Joined
Aug 4, 2013
Messages
255
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?
 

JohnJames

New Member
Joined
Jan 27, 2014
Messages
5

ADVERTISEMENT

Yep exceldevs, that's what I want it to do. Can I get this to work, or am I just out of luck?
 

JohnJames

New Member
Joined
Jan 27, 2014
Messages
5
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!
 

exceldevs

Active Member
Joined
Aug 4, 2013
Messages
255
You're welcome, and I think your decision makes sense since it's a one-time task.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,396
Members
414,063
Latest member
N_Bates

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
Top