Populating a Worksheet with data entered on other worksheets

Hello Excel experts, :-)

I'm hoping someone has done something similar or can be of assistance.

I've a workbook I use to document expenses throughout the year.
It has a sheet for each month of the year (ie Jan, Feb, Mar etc) & each sheet has a description of the purchases in one column, the cost of that purchase in the next, and the type of expense account(from a dropdown list) in the next column. Refer pic 1
Pic 1- Purchases.png

On another sheet called 'summary' it totals each account type by looking for a match on each worksheet, and if true, takes the value in the adjacent cell and adds it for the total spend so far for the year. Refer Pic 2
Pic 2 - Summary.png

On extra worksheets (Tools, Computer Equip, Office Equip, Stationary etc) I breakdown the total value for each account type with each purchase, taken from the 'monthly' worksheets so I can also see what the total for that account type was made up of. Refer Pic 3
Pic 3 - Expense Account Details.png
This part I'm doing manually at present by searching through and copying each purchase detail and cost for each account type, but would really love to have it automated with a macro or some other way so those worksheets (breakdowns of account purchases) are populated as the entries are entered into the 'monthly' worksheets.

I currently have more than one account type on some of those 'detail' sheets and understand it could cause a problem having too many entries in regards to the number of rows available to each account type. I can change it to a sheet for each account type if need be.

I hope that makes sense and I've attached snaps of each part I've referred to.

Appreciate if someone can help me with this.
Thankyou in advance to all that can.

Hi Richard.
Thank you very much for your reply.
I should have firstly mentioned that I am a novice with Excel and don't quite understand a couple of things with your reply. Sorry.
I will try to explain how I got to where it is now.

Originally Posted by Richard Buttrey

Hello and welcome to the forum.

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.

My original design was just the monthly sheets, so I had a representation of each months purchasing. And I could print each out at months end for reports.
I then added the summary sheet of those expenses to know what was being spent as the year went on.
Lastly, at years end, I find Im needing to collate the entries into some breakdown to print a report and justify what was spent on each account.
Again, Ive not a lot of experience with this sort of thing, but if there's a better way to do it as you say, Then I'm happy to change whatever is required to make it better / work.

Originally Posted by Richard Buttrey

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

Pivot table is not something I've any experience with. :-/

Originally Posted by Richard Buttrey

So before you get too far with this I'd create a single sheet database that contains the same columns as you already have on each monthly sheet but with the important difference that all your data is recorded on a single sheet. Forget about individual monthly sheets.

So one worksheet for all entries for the year?

Originally Posted by Richard Buttrey

The Expense Acc and Paid column values could be Validation drop down cells from which you could pick values. The way I normally arrange these things is to have a single data entry row above the database in which the new values are entered, then a button which runs a macro that adds the new record to the database.

I do have both the 'Paid' and 'Expense Acc' columns as dropdown lists already.

Originally Posted by Richard Buttrey

Once you have this single database sheet theb any analysis you could ever want is available in seconds with a Pivot Table

So are you saying all the data is entered in row 2 (in the attached pic) and the macro will 'load' it into the rows below one row at a time for the year?

Thank you again for your help :-)

Hello Richard / All

I have had a look at the Pivot Tables Richard suggested and I can see the many reporting options available :-)

With reference to the attached pic, can I get some assistance with a macro to load the data entered onto the database below it, if I've understood your suggestion correctly?

Thank you :-)

Single Database.png

Ahhhhhhh.... 'folks are dumb where I come from...." lol
Well I am anyway haha

Now I see it. Yes, it appears if somewhere in the table is selected. I must have looked through the ribbon several times, but each time without the table selected.
I do feel rather foolish. :-/
Apparently the big brightly coloured pink 'Pivot Table Tools' tab is not as easy to see if you're me lol

Thankyou very much, Ford. Appreciate your help :-)

Thank you Richard :-)

Your workbook looks a lot nicer than mine. I do like the row 7 freeze and the button is certainly more appealing as well.
The database and table being dynamic is really great. Also the named ranges as well, I see :-)) so I can add more expenses/cards if the need arises.

Thank you so much ! :-)

There is one strange occurance that is happening though. Each time I open the file, the first four times I try to transfer data it is giving me something different in the database.. If I add the clear contents code, Im then getting 4 blank entries before the entered data appears...

I'm sorry to annoy you still with this, but might you know why that may be happening?

Thank you once again for your help :-)

Sorry Richard, I was editing my earlier post to explain there was nothing wrong and just noticed you've replied.

It works perfect :-)

Thank you very much indeed :-)

Originally Posted by Richard Buttrey

Hi,

Is this happening with the workbook I uploaded in #13? I ask since you mention 'after I add the line of code'. The clear.contents code is already present.

Richard,
The workbook you put up for me didn't have that code present.

Refer snapshot of newly downloaded file:
Code1.png


You did provide it for me to add in though and I added it to the very next line of code

This is where I place it:
Code2.png


What I HAVE noticed though is that as soon as I add that code, and then do a data entry and add it to database, my pivot table total is $6 short.
For some reason the total has dropped the very first entry (ie Jan 1st, mmm clothes $6)
I believe it's this one because if i change the value and do it again (on a newly downloaded file from scratch) it's the new value that's short after I add the code.

This is happening on the workbook you uploaded for me, straight from downloading.

My apologies I'm still annoying you :-/

Hi Richard

The record in row 24 is the one that seems affected. It remains there but it's value doesn't seem to be included after the clearcontents code is added.



The workbook I uploaded doesnt have the clearcontents code added yet, it is exactly as you uploaded it for me.
If the file is opened and used without changing any code it appears to behave correctly.

Albeit it doesn't clear the data in the 'newrecords' range.



So to try and explain where it occurs, doing the following reproduces it...
I open the file that I download. (as you uploaded it)
I look at the Pivot table worksheet and note the total is $192 which is the correct sum of the entries currently within the database.
Before doing anything else, load the range clearcontents code within the existing macro (as i've shown in post #19).
Then hit the Macro button to load the new data (tools $123 as it is already there) into the database, and the new value in the pivot table should have increased by $123 to a new total of $315, but it doesn't. It only increases to $309 ($6 short)

Ive tried this again with a clean file from scratch, changing the value in E24 to $10 instead of $6 before adding the clearcontents code, and the sum will be out by $10 instead of $6. That's why I believe it's that row that is the problem.


So I'm thinking it has something to do with me putting that line of code in. :-/

Hopefully that makes what I'm trying to explain a little clearer.

Thanks

Further to my post #23 above, what I've found is as below:

On opening the file, a check of the 'DATA' range with the name manager indicates it is correct and includes all rows with entries.
Delete the entry in cell B3 and a check on the 'Data' range reveals it is one row smaller (row 24 is no longer included)
So as soon as the clearcontents code is added to the macro, the 'DATA' Range is no longer the complete database, and that
is why the Pivot Table total sum is incorrect.

Is there a way that can be rectified or will I need to clear the first row (B24 being the first entry to the database) and leave it empty?
Actually that doesn't work unless something is in cell B24 (like 'leave empty' or similar)

Hope that makes things even clearer..Thanks again :-)

Does anyone have any thoughts on this?

Workbook attached :

Purchase Details.xlsm

Hi Richard,

Just checking to see if you felt there was a way around the problem I pointed out in
my previous post #24?

ie. Whether the database size can be consistent with all the entries or whether I'll need
to remember and accomodate for the bottom row being dropped off the database whenever the
contents of the 'newrecord' entry row is cleared.


Thank you again for all your help with this.