troubleshooting Question

Dynamic Range Update Problem After Adding Records to Sheet in Excel 2007

Avatar of creativefusion
creativefusionFlag for Australia asked on
Microsoft Excel
10 Comments1 Solution323 ViewsLast Modified:
All,

I have had a brief look around the knowledge section but cannot seem to locate anything that matches my needs here.

I have a simple workbook with two sheets in it named "InvoiceHeader" and "Ledger".

In the sheet named "Ledger", I have added a button to add a set of predefined records to quickly assist whoever can help me with solving this problem.

Now there are two items I need to resolve here.

1. Each time I add a new row of records to either of the sheets, I need the ranges for each of them to grow automatically immediately after the addition has been completed;

After adding a row of data, I should be able to query the newly resized range to check the specific record that was just added. I am using forms to do this and in the Sheet named Ledger, I am most interested in the field NDate in column(a).

2. I want to do this in the most efficinet manner to avoid slowing the workbook down as it will grow quite large over time; i.e. Index, Match, Offset...

For Example:

=Ledger!$A$2:INDEX(Ledger!$A:$W,MATCH(REPT("z",20),Ledger!$A:$W))

I have had a few shots doing this on my own but am getting frustrated because I cannot get them to work the way I want them to.

Enclosed is a clean file for reference.

CF
Sample.xlsm
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros