Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Large Data

0

I have attached a workbook with two tabs (Overview & Data) I would like to be able to enter a date or date range (weekly) and have the overview tab update each of the corresponding cells based on the date chosen/entered. I currently only have a Start Date to the right. I have considered adding a WK Ending date since I'm looking for data between two different dates. 

For example: if its 7/03/2020 I only want to the overview to show me data between the week start date (06/26/2020) and the week end date (07/03/2020) 

Currently I have each row adding up the grand total for each staff member but would prefer to see this week to week. 

Answer
Discuss

Discussion

Your question was answered as asked. If you have another question now, out of consideration for onlookers, ask a new question in a new thread.
Variatus (rep: 4879) Jul 27, '20 at 8:27 pm
Add to Discussion

Answers

0

From your description it appears that you use your database to present your data. That's what Excel is good at. But now you feel that you need extracts from the database to work with. The proper approach is to separate database and reporting.

The database should be streamlined to facilitate speedy and correct data capture, with just half an eye on data accessibility. This latter criterium suggests, for example, to keep name and surname in different columns and to have a unique identifier for each row. For older versions of Excel the ID column must be on the extreme left. The database must be practical above all else.

To present the data in a readable format with a pleasing appearance is the job of the "report". That is also where you have to data selection tools, in your case perhaps drop-downs to specify a date range. Select a start and end date and the data you want appear on the screen. The important part of this plan is to realise that the data you see in the report are copies, extracted from the db on the fly, in the blink of an eye. Such reports can be saved too, as copies, never touching the original data. But if you can re-create the report with two clicks - faster than you can find the copy and open it - the point of saving reports is in distribution, not preservation.

Your starting point is the design of the "report" (the form has this logical function even if it has another title). Create the layout and the selection tools (drop-downs, if you like). Make sure the form matches your workflow. Then, your main tool to get the data will be the VLOOKUP function, probably XLOOKUP if you have Office 365 or another more recent copy of Excel.

Once you have come that far post a copy of your report and a mockup of your db on this site together with any specific question you might have at that time.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login