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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

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,122,353
Messages
5,595,668
Members
414,007
Latest member
bongda360org

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