• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatically insert a blank row in an excel table

V

Veronica Burggren

New Member
I am currently working on a checkbook/budget in Excel 2007. In the checkbook portion if I list the category as "split" I want it to automatically insert blank lines below that entry so I can put the categories that are being split to add to the "debit" portion in my checkbook. Or, if nothing else, I would like it to somehow be able to split my "debit" amount into more than one calculation so I can accurately track spending by category. Currently I'm using array formulas to track spending by category. Any help or suggestions would be greatly appreciated. Here is a sample file in dropbox https://www.dropbox.com/s/fyw5gmk4fba1q0l/Sample Check Register w Splits.xlsm?dl=0
 
Hi Veronica ,

Will you always split the single entry on the Check Register tab into only 2 entries on the Categories tab ?

Will only new entries be entered as Split on the Check Register tab ? Or can an existing entry be changed from what ever it is to Split ?

Narayan
 
NARAYANK991 said:
Hi Veronica ,

Will you always split the single entry on the Check Register tab into only 2 entries on the Categories tab ?

Will only new entries be entered as Split on the Check Register tab ? Or can an existing entry be changed from what ever it is to Split ?

Narayan
Click to expand...
Not necessarily only split into 2 categories ... could be 4, 5 or even 6 at times. Since I am starting fresh with this, yes, only new entries will be split.
 
Hi Veronica ,

If that is the case , then when the user enters Split in any cell in column J on the Check Register tab , how can the code decide how many cells to allocate to this entry on the Categories tab ?

Narayan
 
NARAYANK991 said:
Hi Veronica ,

If that is the case , then when the user enters Split in any cell in column J on the Check Register tab , how can the code decide how many cells to allocate to this entry on the Categories tab ?

Narayan
Click to expand...
Is there a way to add a column where I can put in a number and the code or formula takes it into account on how many to add?
 
Hi Veronica ,

Your new file does not correspond to an earlier post you made :
Is there a way to add a column where I can put in a number and the code or formula takes it into account on how many to add?
Click to expand...
I was expecting that in your existing setup , where in the Category field ( column J ) you were entering Split , now , in an additional column , you would enter a single number , say 3 to signify that this single new entry in the Check Register tab would be split into 3 new entries in the Categories tab.

Your latest file shows the same Data Validation dropdown in both column J and column K. In neither of the dropdowns is the word Split available.

Can you explain how the data entry in this version will work ?

Narayan
 
NARAYANK991 said:
Hi Veronica ,

Your new file does not correspond to an earlier post you made :

I was expecting that in your existing setup , where in the Category field ( column J ) you were entering Split , now , in an additional column , you would enter a single number , say 3 to signify that this single new entry in the Check Register tab would be split into 3 new entries in the Categories tab.

Your latest file shows the same Data Validation dropdown in both column J and column K. In neither of the dropdowns is the word Split available.

Can you explain how the data entry in this version will work ?

Narayan
Click to expand...
I was in too much of a hurry and overlooked that (fixed in the attached). When I enter data in the check register and it is assigned to a category, I'm using array formulas on the categories tab to calculate the amount that goes to the category assigned. When I choose "Split" in column "J" and enter the number of splits in column "K" I am wanting it to either add that many lines below that entry or at the least to somehow get the correct split amount included in the categories total. Attached is the revised with those columns corrected.
 

Attachments

  • New Budget & Check Register.xlsm
    103.3 KB · Views: 4
Hi Veronica ,

Quite a few questions remain ; anyway , you can go through the attached file and comment.

1. The new entries will be made in the table on the Categories tab ; I have converted your data range to a table named Table_of_Categories.

2. The new entries will be made through a Worksheet_Change event procedure , which is triggered when the text Split is entered in any cell in column J , in your Register1 table on the Check Register tab.

3. The number of rows which will be added are to be entered in the corresponding cell in column K ; since it is the entry in column J which triggers the row insertion , it is expected that the data entry in the Register1 table will enter the text Split in column J after the number of rows to be added has been entered in column K , and the Debit amount has been entered in column K.

4. The insertion itself is following the logic :

a. Either an entry labelled Split is already present in the Table_of_Categories table on the Categories tab ; in this case , the code checks whether the next entry below this is blank ; if it is , it means that the required number of blank entries have already been made.

If the next entry below the match is not blank , then the entered number of blank rows are inserted.

b. If an entry labelled Split is not present in the Table_of_Categories table on the Categories tab the code inserts a row and puts in the text Split in the Category column , and the entered debit amount in the Amount column.

Thereafter the entered number of blank rows are inserted.

If this is not what you want , please describe the desired logic in detail.

Narayan
 

Attachments

  • New Budget & Check Register1.xlsm
    112.9 KB · Views: 3
NARAYANK991 said:
Hi Veronica ,

Quite a few questions remain ; anyway , you can go through the attached file and comment.

1. The new entries will be made in the table on the Categories tab ; I have converted your data range to a table named Table_of_Categories.

2. The new entries will be made through a Worksheet_Change event procedure , which is triggered when the text Split is entered in any cell in column J , in your Register1 table on the Check Register tab.

3. The number of rows which will be added are to be entered in the corresponding cell in column K ; since it is the entry in column J which triggers the row insertion , it is expected that the data entry in the Register1 table will enter the text Split in column J after the number of rows to be added has been entered in column K , and the Debit amount has been entered in column K.

4. The insertion itself is following the logic :

a. Either an entry labelled Split is already present in the Table_of_Categories table on the Categories tab ; in this case , the code checks whether the next entry below this is blank ; if it is , it means that the required number of blank entries have already been made.

If the next entry below the match is not blank , then the entered number of blank rows are inserted.

b. If an entry labelled Split is not present in the Table_of_Categories table on the Categories tab the code inserts a row and puts in the text Split in the Category column , and the entered debit amount in the Amount column.

Thereafter the entered number of blank rows are inserted.

If this is not what you want , please describe the desired logic in detail.

Narayan
Click to expand...
I guess I'm going about this all wrong. I don't necessarily need to add lines since I will enter each line as it happens. What I'm really looking for it to do (maybe it's just formulas in the balance column that could be changed). I always want a running balance, but if there's a line that says "split" I need the balance column to only calculate that line. Then I want to be able to enter in each line below the "splits" what is actually supposed to be calculated in the categories tab. I have written notes inside this workbook.
 

Attachments

  • New Budget & Check Register 22222.xlsm
    107.2 KB · Views: 2
Hi Veronica ,

There is no point in notes.

Can you first upload a workbook which has a lot of data as you would have entered it ?

There is data , and then there is data processing ; if your data entry does not depend on data processing , as it will in an inventory system where the issues depend on the stock available and the stock available depends on the previous issues having been processed , just enter a lot of data , and upload that workbook , so that your notes can be read in conjunction with the data.

Just going through the notes means who ever reads them will have to visualize the data and / or the data entry process.

Narayan
 
NARAYANK991 said:
Hi Veronica ,

There is no point in notes.

Can you first upload a workbook which has a lot of data as you would have entered it ?

There is data , and then there is data processing ; if your data entry does not depend on data processing , as it will in an inventory system where the issues depend on the stock available and the stock available depends on the previous issues having been processed , just enter a lot of data , and upload that workbook , so that your notes can be read in conjunction with the data.

Just going through the notes means who ever reads them will have to visualize the data and / or the data entry process.

Narayan
Click to expand...
Not sure how to explain it further. Maybe its just not possible to do. Basically it's just like any other check register other than the fact that I want to track my spending by category, which I've done by using array formulas on the category tab. My predicament by doing so is that if say I go to a grocery store and spend $100 but only $75 is for groceries, $15 is for household stuff and the remaining $10 was for sales tax I can't figure out how to make those categories add into those array formulas without having to list each item on a separate row in my check register. I would like to be able to list each in a separate row but have my running balance not include the lines that are the split amounts (in this instance the $$75, $15 & $10); only the total of those ($100). I have listed on the check register what the balance column (N) should look like in my example.
 

Attachments

  • New Budget & Check Register 22222.xlsm
    106.5 KB · Views: 9
Hi Veronica,
I have a personal budget I made for myself and for split transactions, I just don't bother with the "Split" line as you have it currently laid out. For example, if I go to Costco and buy cat food, dog food and groceries, I list Costco 3 times.

My suggestion is to keep it simple. I would also lump in sales tax with the items being purchased. When I first started my own budgeting, I tried to segregate the sales taxes, but it became too tedious. Just my two cents...
Also, the Categories tab, you can use a SUMIF formula instead of the array formula you have there.
 
Hi Veronica ,

Now that your data is available , things are clear.

I have just one question ; as I understand it , you need the macro to insert the required number of rows into the table on the Categories tab.

The macro , as written , is using the Worksheet_Change event as a trigger ; do you want that the macro should run as and when an entry is made in the Check Register tab ?

Can you not run the macro as and when you want to , through a command button ?

If this is possible , then the existing macro can be modified slightly to work the way you want it to.

Narayan
 
You must log in or register to reply here.
Back
Top