adding a record in the next cell in excel

[harvesterlily]

I have a process that is automating our various payroll entries. Once this has been done, I am wanting to automatically update our checkbook register that is in excel. The excel spreadsheet already exists, i know how to open an existing workbook and find the appropriate month's worksheet. This worksheet will already contain data, as it is copied from month to month and values in certain columns are just deleted. This means that the column that calculates the balance may or may not already have data.

EX:
Desc Date Deposit Misc Disbursements Balance
Beg Balance 12/1/05 1000.00
21450 12/1/05 20.00 980.00
980.00

** The 2nd $980.00 should be under balance, but screen doesn't display it that way.

I would want to post my payroll entry just under the check #21450 on the same row where the $980 has already been calculated. In some cases my entry will be a new record as the balance will not already be calculated and the formula would need to be copied down. How do I test for this scenario? Also there are grand totals further down the sheet (I will be inserting/updating records between the last complete entry and the grand totals.) Any ideas?

Thanks

 

[SkipVought]

Hi,

Totals at the BOTTOM, where the user has to HUNT to find, is a vestage of paper, pencil and adding machine.

Think out-of-the-box!

Put your aggregate information right at the very top, for all to see right off! It solves the problem of sandwiching it in with a shoe horn between other data and the run-of-the-mill, the-way-we've-always-done-it, stick-in-the-mud paper 'n' pencil mimic, make-the-user-search-for-the-nuggets, approch.

Just a thot! ;-)
Hi,

Totals at the BOTTOM, where the user has to HUNT to find, is a vestage of paper, pencil and adding machine.

Think out-of-the-box!

Put your aggregate information right at the very top, for all to see right off! It solves the problem of sandwiching it in with a shoe horn between other data and the run-of-the-mill, the-way-we've-always-done-it, stick-in-the-mud paper 'n' pencil mimic, make-the-user-search-for-the-nuggets, approch.

Just a thot! ;-)

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[harvesterlily]

Thanks Skip. This was the way the box was presented to me. If I had the freedom to redesign the register, that would completely alleviate my problem. I only mentioned the aggregates so as not to get an overly simplistic answer.

That still leaves my original dilemma. The Balance column contains a formula that may or may not have been copied down. I need to test to see if there is a value present to determine where I will be posting my next item. I need to be able to test for rows that have all empty cells (no values) except for the balance. If a balance only row exists, I need to update that row with my new entry, otherwise I need to add a new row. Any ideas?

Thanks again,
Terri

 

[SkipVought]

I'd use the Worksheet_Change event to calculate the balance using VBA -- NOT a spreadsheet formula.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}