Inserting rows when tied to index/match function

HollyN

Board Regular
Joined
Jun 9, 2004
Messages
59
My input spreadsheet is called P3 Budget, my output is P3 2006. All the values in P3 2006 are tied to the P3 budget sheet through index/match where they match the general ledger # as well as the month to get your value.
I'm running into problems though when someone inserts a new line item into the P3 budget sheet. As you'd guess, it throws all my rows off in the P3 2006 sheet.

I need to find a way for this not to happen each time someone makes a new general ledger entry because it makes a disaster of my output page.

Can anyone help?
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Can you give a bit more detail on the structures. I would have thought that using the index/match would have covered the insertion of a new line in the budget sheet.


Tony
 

HollyN

Board Regular
Joined
Jun 9, 2004
Messages
59
I talked with the person who adds the G/L and she says the only way to avoid this is to make sure they have the output sheet open when they add the new G/L to the input sheet...

why would that be?
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Holly

Sounds like the output sheet is directly linked to the budget sheet. Something like =budget!a2 as the formula in A2 of the output sheet.

Index / Match should cover insertions of new rows in the budget sheet as long as the range is adequate to cover any insertions. Or maybe this is where the problem exists.

Can you give an example of your struture and the formulas?


Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,118,320
Messages
5,571,531
Members
412,401
Latest member
allenayres83
Top