how do I start with a clean page everyday whilst retaining the data

I have the attached sheet doing what i need it to do thanks to this forum. I need to start every day with a blank sheet though but with the formulas attached and also want to be able to find out what i paid for a certain product when it was last bought over say the previous 2-4 weeks, so if someone is quoting me £5 for kiwis then i want to be able to pull up prices i paid for kiwi on the spot and quite easily/quickly..is this doable ? it would be a bonus if it also told me the supplier i bought it from. I dont understand how i am going to have a clean sheet everyday and have the old sheets available for getting information from when needed..are they hidden away or on another sheet ?

Welcome to the forum.

We'd like to help you but first..

Pls take some minutes to read forum rules and specially-in this case- rule#1

Originally Posted by loady

I should not assume that all forums are the same

True!

Title is ok now! Thank you!

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, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.


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.

I'm attaching the start of a system which you should find more flexible.

On the Variables tab maintain your lists of products and suppliers. Just add new items as necessary. If you delete any items don't delete the whole row as you may also delete an item in the adjacent table. Delete the cell and choose the Shift Up Option.

These entries are now available in the drop down cells on the Data sheet in B7:C7
Enter new records on row 7 and click the button to add the new record to the database. It will appear as the first row with the other rows shifted down. Until all items are entered you won't be able to add the record.

Now that you have a proper database you can see that on the Pivot Table you can see a complete analysis. To see results for a particular day just select the day in the drop down in B5. Being a pivot table you can drag and drop fields around to see the data presented in different ways. You could for instance drag the Supplier name field to the left of the Produce field to see all produce for a particular supplier.

Play around with it and you'll see how powerful is the Pivot Table.

I should of course have added that all produce and costs is entirely random, merely for the purposes of testing.

Perhaps I should have added that you will no doubt have several questions, like for instance what was the spend with a supplier on a particular day. If yo right click in the Pivot Table in the Supplier column and tick the 'Sub Total Supplier' option you'll find a sub total is immediately added to the PT.

Richard, i see it..i see !! i can see how powerful this is going to be and i am really enjoying playing with..was it a difficult thing to make or is a pivot table a blank template ? with regard to my sheet i created will i still be able to produce that as my end result that i can have printed out when i get back to HQ ?, my boss will not want to see it on a screen nor will he care about this, he likes paper and he likes it exactly the way i created my buying sheet, for me, it is a learning curve and another string to my bow which i can play out with in my job.

The fog is clearing a bit now. I see the tabs along the bottom and i see my sheet there, how is that tying in with the pivot table ?, on a daily basis when i go buying am i entering the information into the data sheet ? that information is then available in the pivot table and then somehow i can present the days buying on my buying sheet i created in the same format ?

Hi,

Perhaps you'd better upload your workbook.

As you're beginning to find PTs are extremely flexible and powerful and I guarantee that since you're in the early days there's more interesting stuff around the corner.

As you're discovering, whilst they're powerful (giving you the ability to summarise and analyse very quickly) their presentation is fixed. So if your boss isn't happy with the presentation (he'll learn!) then you'll need to create a specific daily buying report. Fortunately you have all the data in your database so you now need to get to grips with an Advanced Data Filter which can extract specific records (based on some defined selection criteria) from a database to a particular place and create the look & feel he's used to.

If you add in a new sheet showing the presentation style you'd like to see for the daily report no doubt we can add a simple macro that will automate it for you.

Hi,

I've added a data filter as well so that when you change the date on the Daily Purchases sheet the data changes.

The Pivot table is essentially the same but I've just tidied it up a bit.

One new bit of functionality you may or may not want. Go to the pivot table and double click any of the numbers in the total column. See immediately how a new sheet is added and populated with the records from the database that supports the total.

You may never use it but I mention it as just another bit of PT functionality. Right click on any sheet tab name that's added and delete it whenever you want - or of course choose Delete Sheet from the menu.

Hi,

I've created a dynamic range name that covers the data and used the range name as the data source for the PT. Hence you can delete all the data, apart from the header row 5 and then add your own data. The range name will automatically expand or contract to cover it. I've added an instruction to the Pivot sheet Activate event in the Visual Basic (macro) environment that will automatically refresh the PT every time you navigate to the sheet.
Incidentally did you mean to remove the functionality that I added which allowed you to enter a new record at the top of the sheet and press a button to add it? No problem if you have but just wondered.

Yes it was a single line macro and would probably not be recognised by the 'Lite' version of Excel that you see on your device. You should also check that you don't lose the range name 'data' that covers your list of items when you open Excel on your device.

To see the subtotal by supplier, or indeed any field that's in the PT, just right click in that column in the PT and you'll see an item 'Sub Total and then the name of the field'. Just tick or untick this as necessary.

See attached

Thanks Richard, i did not delete anything as far as i am aware, i just noticed that the clicky to add data disappeared on my device, it would be great on my device to have that large area to click and the data goes in. I am not holding my breath on getting the sheet to perform on an android device though, i cant afford a a windows tablet at this time, although that would be nice.

Looking at attached..it seems very different again..just the PT and a new historical data sheet but the data sheet has gone ?..im slowly loosing it :S

Hi,

Looking at the file I last attached I think it was based on the one that was attached to post #14 which I thought was yours but clearly not.

Open my original in post#13. It already contains the functionality of the dynamic range name that covers your data automatically so just delete the data as you wish and replace as necessary. The PT is also automatic and will refresh every time you navigate to that page.

Just to repeat, the Daily Purchases sheet is just another, non pivot table way of handling your data and reporting. They both produce the same results. You don't need both the Pivot Table sheet and this one so delete whichever you don't want unless you want to keep both out of interest.