How to perform consolidation of data and sort through drop down menus?

Carl, you are probably going to need to restructure some things to make it work for you, but what it sounds like you want/need are dependant menus and a filter system that gets beyond the 2 criteria filter in Excel.

Watch this video for th filter system: http://www.excelforum.com/excel-prog...own-lists.html A lot can actually be achieved with thesew filters. They function as an OR operator if you set it up to use many criteria from the same column and an AND operator when using them in conjunction of additional columns.

See both the filter and dependant menus in the attached file, though this example only uses one row for the filters. But, you can set it up to use as many as you want.

Hi BeachRock,

I will look at the spreadsheet you have kindly given me and see if I can try and sort something out. I will post an update to let you and others know what the outcome is. Can I quickly ask though when you say restructure some things, what should I consider restructuring?

Thanks

Carl,

Take a look at the attachment. On the Cases sheet, select a Brand from the dropdown menu in B2 and a Status in C2 and then push the "Set Filter" button.

I had to reorganize your data on each day sheet in order to create the solution. You had cells merged where data existed that you wanted to retrieve. If you want to be able to do anything with your data, you should always avoid merging cells where that data exists. It can be a huge pain to get the data to return with formulas otherwise.

You'll notice that the attachment is now a 2010 macro enabled workbook instead of 2003 version running in compatibility mode.

I also created a new sheet named "Lists". This is now where the named ranges for your dropdown menus exist, Brand and Status, instead of in column A on every sheet. This way you only have one place where you need to change things and it will affect all dropdown menus using those named ranges on every sheet all at once.

The attachment assumes that you will only have 26 rows of data on each day tab in the Case tables. The Cases sheet is protected (not with a password) so you don't accidentally delete the many formulas in the B6 to E811 range. You can select the cells and see the formulas in them. Notice the gaps in data as you scroll down. I used the data from sheet '1st' for all of the Case tables on the others sheets. If you select the cells you can see how each section of 26 rows relates to each of the day pages. If you will need more than 26 rows on each day sheet, you'll need to add them into the Cases sheet table and create the formulas to reference the new rows added from each Day sheet.

This is what I was thinking you were talking about. A reference to each Case table data all on one page with a filter.

Let me know if it fits the bill.

Hi BeachRock,

I really, really, really thank you for this. This is what my cousin wanted on his spreadsheet and you have made him happy. I have been trying to build a database equivalent as Im more into databases than spreadsheets. But hat you have done is amazing and i can't thank you enough.

If you don't mind I just have a question to ask you and the spreadsheet is fully complete.

For each separate worksheet (1st ... 31st), in the cases table can the following buttons be included:

1. Add Row: This will add a new row in the table

2. Delete Row: located next to each row, user can delete a row by clicking on it, will display an alert asking if user is sure to delete then user decides if he/she wants to delete row.

3. Clear Table: Removes all rows in table, again asks user if they are sure if they want to clear table, if confirm then it will do it.

If it is easier can you give me a step by step guide on how to include the buttons for let's say 1st worksheet and then I can try it for 1st worksheet and include it in other worksheets. I have been trying to use Macros on my mac excel 2011 but have been struggling so the step by step guide would be very helpful.

I will try to figure out how to not include blank rows in the "Cases" worksheet table at the mean time

Again thank you so much for the spreadsheet and help.

Hi Carl,

I'm glad you like what I posted last night and that it's on the right track per your cousin's vision of what it should be. You happen to be asking for things that I'm pretty familiar with. I can do what you are asking with the additional stuff but it will end up needing to have all case data, no matter which day of the month, all on one sheet. Basically, you'll end up with the same way of being able to filter as I previously posted but it won't be on a sheet per day type of configuration with a Cases sheet to pull the data into. I will add a column that will auto-enter the date of the record creation so the additional Day sheets won't be necessary.

The same can also be done for the EOH and the Brochure tables. About those tables, can you explain what their intended use is for me, what each label means to you, what is EOH, Dialled, etc? Do they at all tie into the cases themselves? I'd hate to get too far into making the changes you are requesting and then find out later they need to be in the mix with the cases. On first thought, I think both of them can be on one sheet by themselves with a a summary table containing a dropdown menu to show you info based on the month, or something of that sort, and with a similar look and functionality to the Cases sheet.

It will be a few days until I have time to work on this again, but I won't forget about you. Unfortunately, life tends to get in the way sometimes. I'll probably have something to you by Saturday or Sunday my time.

Hi Carl,

See attached.

This should get you going. We can work on the EOH and Brochure tables later after I hear back from you about them from your cousin.

Cheers!

Carl,

I think we just missed each other on postings. Take a look at post 9. With this there is no need for multiple sheets for the days or for there to be a consolidation sheet. It'sall done on the same sheet. You can delete a select row based on the cell you are in or you can select as many rows as you need to and delete thm all at once, each having the checks and balances to make sure they meant to delete the row(s). This version also gives you the ability to have up to 6 filter criteria, so if you want to filter on more than one Brand of vehicle, just hit the + sign to the left the first filter row and an additional filter row is unhidden. You just eed to make sure that the number of filter rows you have visible are actually used. If you have 4 filter rows visible then you must have at least one filter column with all four rows filled with data to be filtered. Use the minus sign to collapse the filter rows you don't need.

Additionally, there are two ways to add new Case records, one uses the drop down menu in B1 to select the number of new recors to be created and will use the first filter row (s) cells D5:F5 to create the new records all with those items pre-filled in for you. I also added a filter for Case # so if you know the case number and want to look it up quickly, just put that number into the filter at G5 or add multiple filter levels to look up more than one Case #, up to 6 at a time.

Carl,

Have you had a chance to take a look at the workbook I posted in post 9 and show it to your cousin?

Hi BeachRock,

Yes I looked at your Cases spreadsheet. Let me first pass on the message from my cousin who wants to say thank you for the first spreadsheet you created. I gave him that spreadsheet to use at moment before you can create him the more dynamic "Cases.xlsm" spreadsheet. I want to thank you again for all the help as well.

Ok so I told him that the dynamic spreadsheet will be done by either this weekend or next week as obviously you need time to spend on this. I asked my cousin on what EOH and brochure tables are.

EoH is end of honeymoon calls, my cousin's team contacts ppl that have bought a car after 8 weeks to make sure there happy.

Brochure is for follow up calls on ppl that requested a brochure!

The tables are not not linked exactly with the Case data in the Case table. But what I think he would like which you mentioned before is at top of page, have a filter where picks from days 1-31 and it will show the relevant EOH and Brochure tables specific to that day. He did say that he wants the EOH and brochure table to be displayed at the top of the spreadsheet (virtually the same layout as the spreadsheet I posted to you in post 1), and below that will be the Cases table which you created in Cases.xml.

While testing you Cases table there were a couple of things I found just for your information:

• If I click on the = button, multiple times, it only shows 3 rows of filters maximum, not 6.

• Sometimes when I click on the - button remove filter rows, it removes 2 rows and not one.

• If I perform a filter search, lets say for Brand, If I click on the "New Case Blank" button, it does not add a row. It only adds a new row if filter is removed.

That is only issues I found but everything else seems fine. My cousin also would like to know if the Case table can be automatically be sorted through "Day" column and for the date stamp, don't put the day but keep the month and year, this is because he is going to use seperate spreadsheet for each month. So this spreadsheet you working on belongs to November 2013, so if he ever wants to refer back to cases in November 2013, he knows because of the date stamp on the left column.

That is all the information I believe I have obtained. Thanks again and you made my cousin very happy and he really appreciates it. And thank you from me and looking forward to your new dynamic spreadsheet in upcoming days. Any questions please post back to me.

Thanks

Carl

Hi Carl,

Please look at the new attachment in this post. I think I've got everything working the way your cousin requested.

The only way to get around problems of having the EOH and Brochure tracking tally tables and placing them directly above the Cases table was to put them in columns to the left of the Cases table and adding a button to hide or view them as desired. Otherwise there would have had to be data cells merged in many places and that is to be avoided at all cost for functionality to be correct.

The problem with the filter level buttons is an inherent glitch in Excel when using buttons on rows or columns that are ever hidden and unhidden. I have placed them elsewhere on the sheet and all work correctly now and are never hidden.

I was able to devise a way for just one entry table to be used for all items, whether they be Cases related to or not related to EOH or Brochure. Use the buttons for showing and hiding the EOH and Brochure tally tables. When you don't need to see them, you don't have to. They are always tallying information in the Cases table. You can use the Year, Month and day dropdown menus to drill into a specific day for relevant criteria for each brand.

I eliminated one of the buttons for adding new records. Still the same functionality but for both ways in one.

I have also included an FAQ sheet to help with understanding how everything is working or how to do something.

I look forward to hearing feedback from you and your cousin.

Cheers!

Hi Carl,

I do appreciate the compliments, but without the generous assistance I've received from so many very knowledgeable people on this website for my own projects, I doubt I would have been able to help you with this much, if at all. I'm always looking for and learning new techniques to better my skills with Excel but I still consider myself very inexperienced and with so much yet to learn. However, I'm very pleased to hear we're on the right track and hopefully this project is turning out better than you might have thought it could have. That's what I'm after. ...and getting some good practice honing my skills, of course.

First, I must say that your suggestion for the filters being just one button without the + and - is an OUTSTANDING one and I wish I would have thought of it myself! It's so obvious!! Nevertheless, I've built that into the new attachment. Each time you add or remove any level or add new data to, or remove data from any of the visible filter cells, you will always need to click the Set Filter button. However, if you have 1 level showing and you want to add more levels with additional filter criteria, you just need to click the number for the additional levels and add the criteria to them without needing to enter data back into the original levels that were showing; they remain as previously set. You only need to add data to the new levels and then hit the Set filter button to apply the additional criteria. Removing levels: If you have (for example) 5 levels showing and filled in and you want to reduce it by one level to 4 filter levels showing, just click the number 4 filter level button and the criteria from level 5 filter is cleared leaving only that from levels 1 through 4. This is the same when reducing by any number of levels, starting from any number. Pretty slick, if I do say so myself.

EOH and Brochure: Reading your last post I had an "Aha" moment and I now completely understand why there were two sets of each table showing on each sheet of your original workbook. I was scratching my head at first wondering how I could do it within the current confines of space.... I'm happy to pass on that I was able to comply with that without the necessity of adding those secondary tables. I created sort of a spanning filter so you can choose a start date from the three cells C2:E2 and an end date from a new date selection area from K2:M2. Entries in the new end date Year (K2) must be equal to or greater than the Year in C2, the end date Month (L2) must be equal to or greater than the Month in D2 and the end date Day (M2) must be equal to or greater than the Day in E2. For instance, you can span the filter from 2013-12-1 to 2013-12-31 (or any day above 1) and the EOH tables will tally up everything entered in the cases table between and including those dates. Set them both to the same start and end Year, Month and Day and you will only see what the numbers are for that day. The only thing I was not able to adjust for on this is if you wanted to (for example) see the data between 2013-12-3 and 2014-1-2 it won't do anything because the end month and day each are less than the start month and day. I'm really not sure how I would be able to get around that.... but as it is currently, it is highly functional otherwise.

Additional inclusions: I added an Open and Closed column for the Brochure table for T Drive, Email and P Date. It helps tremendously for pulling back more defined output for the EOH and Brochure filter tables. I also made it so the "QTY of Records to Create" (now in T4) is set back to 1 after each time being used. I added some conditional formatting to make it easier to identify blank cells in the cases table.

I'm looking forward to hearing from you about these changes.

Cheers!

Hi Carl,

I'm glad you are so pleased so-far. I debated whether to add the Open and Closed columns for the Brochure table... Guess I should have just left it. Oh well. Not a problem though. They can just as easily be removed.

I agree, your Dad's idea to add a 7th Filter Level is a good one and a relevant change. I'll add that in.

I knew the EOH/Brochure date filter was going to be a problem. I know there are ways to subtract one date from another... That being said, I'll need to figure it out from the know-all that is the Internet. My goal for this is to not have to add anymore rows... I'll try to make use of the 3rd row somehow as I really think this can be done with it looking the same and using a few locked cells to do some calculations. This may take a while, Carl, so I'm glad there's no rush.

If you don't mind me asking, how many people work for your cousin's company? Just curious.

Good morning Carl,

The date span problem turned out to not be as difficult as I thought it would be. Tricky to figure out but I enjoyed the challenge. The people using this will still need to provide a Start and End Date in the same cells, so functionality hasn't changed. The only difference is the formulas being used in all of the EOH and Brochure tally cells. I had to add one more column to the Cases table, which will never be seen as it is hidden. The formula in this column (T) converts the Year, Month and Day for it's Cases table row to an actual date such as 11/10/2013. The same kind of formula is used to convert the EOH/Brochure Start and End Date cells to an actual date and are hidden in cells D3 and L3. You won't know they are there unless you unlock the sheet and select them. The new formula in the EOH/Brochure tables are then able to subtract the Start Date from the End Date based on the Start Date being equal to or greater than the dates of the Case row records and the End Date being equal to or less than the dates of the Case row records, returning only rows equal to or between those dates. Problem solved.

So, onto the rest.

There are no more Brochure table "Open" and "Closed" columns.

A 7th Filter Level has been added with the exact same functionality as the rest. I also included the Case # in the creation of new records. I wasn't sure if they might need that or not but they don't have to use it if they don't need to.

I was able to figure out a way to eliminate the possibility of the last data row from being deleted. As with the date span problem, this didn't end up being all that difficult to do either. However, no matter which record is in the 25th row when there are more than 1 rows containing data, it cannot be deleted; the deletion code prevents it. It's sort of a fail safe. If they don't want the current data in the record occupying the 25th row, they should just change it to something else.

I updated the FAQ sheet as well. Basic functionality is covered.

Though I haven't tested it, I think you could print filtered reports from the Cases table by setting a filter and then selecting the filtered rows/columns, including the column headers of the Cases table, and then printing the selection from the Print window.

Please test the EOH/Brochure date span filters and returned data. My tests showed things were coming back correctly but another set of eyes might see something I didn't.

Kind of funny, you're probably just getting up and I'm just now heading for bed. At least now, my brain won't be keeping me awake thinking about this.

Good night

Hi Carl,

I just saw your post previous to mine last night. I'm surprised, with your background why not build them a true DB app, say with an Access front end and a MySQL backend? Maybe they just don't have a need for something as robust as that...

I would think database development to be much more difficult than Excel. So much more can be done. I wish I had that ability in my arsenal.

I took a look at one of the dealership websites for SEAT vehicles. I've never heard of that brand before. Is that an English manufactured car? Looks nice.

Hi Carl,

I made the two small changes. See attached. I'll wait to hear back about locking and unlocking as you described before I try doing it. I've already been looking into it, just in case.

Hi Carl,

I have some good news and some bad.

The good news: I am able to make the conditional cell locking and unlocking work.

The bad news: With the worksheet event that has to be in place on the Cases worksheet for cells to lock and unlock according to the rules you set in your previous post, which is the only place this type of code can be placed or used, we lose the ability to have the Entry Type auto-fill when adding new records. The rest of the other cells can still auto-fill but the Entry Type has to be set manually for each new record. The reason is that in order for the ability to insert multiple records at all, I have to use a command in the new record creation macro code that turns off worksheet events so it will function correctly and then turn it on again after the new records are created. However, when I add new records with the Entry Type set for auto-fill, the locking and unlocking breaks since the Worksheet event was turned on after the new records were created. It doesn't recognize the Entry Type data that was copied into the new records until that data is changed to anything else using the dropdown menu. If I change the current Entry Type to something else, the locking works as it is supposed to for that row but otherwise, none of the cells on that row are locked that should be.

I'm afraid there is nothing I can do about this. We're up against a limitation. Your cousin has a choice to make if he wants the cells to lock and unlock. Either don't use that feature at all and use the file I last posted, or have to manually fill in the Entry Type for each new record created.

Let me know. If he chooses the locking, I'll post what I have now.

Hi Carl,

Haven't heard back from you so thought I would just post the version which uses conditional cell locking. Your cousin can take a look, try it out and see if manually setting the Entry Type is worth having the conditional cell locking or not.

It works perfectly as far as the worksheet event will allow. I added some conditional formatting so the Entry Type cell on each new record will be noticibly visible of needing to be updated to one of the entries in the dropdown for that cell. Until something else is entered into that cell though, all of the cells for that row in columns Z through AD will remain unlocked. It's a worksheet event and the event can't trigger until after something is selected in the dropdown, i.e. changed to anything else. If one of the other selections is applied in the Entry Type cell, the locking will function as it should for each type. If a cell containing a qualified entry in that cell is then changed to being blank, the cells in Z though AD will then be locked.

Each new record created, the Entry Type cell will turn to a pinkish color. I adjusted the message in the Filter Level 1 Entry Type cell to say "Cannot be used in auto-fill" and removed the double lined green line around that cell.

Let me know what they decide to use.

Hi Carl,

I hope you are feeling better. I've actually been sick myself, took Friday and Monday off from work because of it, but not feeling much better today... That was the only reason I had so much time to work on this for you.

Ok, so I read through everything Chris is asking for to be changed and I'm afraid we have a problem with most of it.

1. He wants to remove all of the columns in the case table for Dialed, Contacted, T Drive, Email and P Date. However, if he wants the EOH and Brochure tally tables to work, this can't be done at all. All of the numbers for those tables come from the Cases table using the numbers from those columns in conjuction with the Entry Type and the Status fields. They are all tied together for his EOH and Brochure table reporting. If I remove those columns from the Cases table, where will the EOH and Brochure tables get their information? It seems we are taking an enormous step backward and I'm really not understanding where he's going with this. On your original file attachment in post 1, both the EOH and Brochure tally tables have Dialed and Contacted columns. Both of those tables, again in the original, from what you told me, the top EOH and Brochure tables were for increaing the number of each cell as necessary throughout each individual day and the bottom tables would equal the totals for all criteria for that day and then carry those totals forward for each consecutive day for that month. That is exactly how my file works now with the addition of working with years instead of just the months of the current year, but it absolutely must have the Dialed and Contacted columns in the case table for the EOH related items to be tallied and the T Drive, Emailed and P Date columns for the Brochure entires to be tallied. How does Chris plan to tally these items if not through the interface I created? He also said he wants another clumns added to the Brochure table for tacking if he sent another additional brochure. That wll do nothing unless we put in place another column on the Cases table just for trackin that specific event. So, the Brochure Specific columns would have 4 clumns instead of 3.

2. Then he says to eliminate all of the Entry Types not related to either EOH Case or EOH Enq on the Cases table.....? This isn't making sense. Why track them on the cases table at all if you aren't going to also track if they were Dialed and Contacted? Also, what about the Brochure information? If there is no Entry Type for that and no columns for T Drive, Emailed and P Date (and an addendum column for a 2nd brochure sent), again where is that information going to come from?

I can understand if he just doesn't need the "Cases" Entry Type at all and we could just remove that selection from the Entry Type drop down menu... But then we're back to why use the Cases table at all if we're removing all of the columns that are used for tracking???

3. Lastly, Chris asked for a button to make the Start and Stop dates both be for the current day's date. The best I can do there is add a button that would copy the year, month and day from the Start date criteria to the End date criteria. He would have to manually input the start date no matter what. The drop down menus are providing static years, months and days as just a number, 2013, 2014 etc. for years, 1 through 12 for months and 1 through 31 for days and are not physically date related otherwise. That's why I had to come up with a way to change the combination of the three for each Start and End dates to an actual date in order for the calculations to work going beyond one month of data.

Honestly, Carl, I'm baffled about these change requests, at least the first and second above. What he seems to be requesting is a completely different workbook with completely different functionality or something and I'm simply not willing to go there with as much time as I have given on this so-far; over 40 hours. Does he just not understand how it works? If he doesn't need "Cases" in the Entry Type, not a problem and it can be removed, but what is the sense in any of the requests otherwise?

Hi Carl,

Ok, I'll wait to hear from you.

As for the Start and Stop date being the current date, the Start and End Year, Month and Day cells are Data Validation dropdown menus. They won't be able to use a formula in them the way you are thinking they might, at least as far as I am aware. Might be a complete waste of your time. However, I think I have an idea for it. Sorry, there does always seem to be a way around most things, I just wasn't thinking far enough out of the box when I first replied about it.

If we add a button that is used to just blank out the Year, Month and Day for both the Start and End dates, I can add some more to the current formulas in D3 and L3 that would cause them to be the current date if the Start and End date fields are blank and would otherwise be the date entered into the Start and End date DV menus.

The code in cells D3 and L3 would look like this:

D3

Formula:

Please Login or Register  to view this content.

L3

Formula:

Please Login or Register  to view this content.

You can unlock the Cases sheet on the most recent version I've posted and insert the formulas above into D3 and L3 and you'll see how they would work if you then blank out the C2:E2 and K2:M2 cells. I tested it just now and it works perfectly to make the EOH and Brochure tables look at the current date only. If Chris wants to span the days from a past day to the current day, he will just need to change the start date Year, Month and Day and do nothing about the End date as it will automatically be set for the current date if K2:M2 are blank. Obviously, if he wants to span a time frame ending before the current date, he would have to enter both a Start and End date.

About the locking version of the Cases workbook, if he doesn't want them locking/unlocking then just have him use the version in Post #23. Same thing, a completely 100% working product but just doesn't use the Worksheet event to lock and unlock those cells.

Hi Carl,

I have made every change requested by Chris. You'll find the new attachment on this post.

1. All Brochure related columns in the Cases table have been removed.

2. The Dialed and Contacted columns have been removed from both the Cases table and the EOH filter table. They were removed from the Brochure table around three or four versions so didn't have to change that.

3. The Brochure table no longer does any automatic tallying and is now setup for manual entry ONLY for T Drive, Email and P Date and with the additional "Brochure" column. The totals in each of those columns still adds them up from above.

Carl, sorry, I didn't see your most recent post (34) until just now, after I had already completed all of the changes Chris asked for as identified in your post #33 and my post #35. Those changes are in post 35 attachment.

As for your recent post... Are you kidding me??? This is ridiculous. There isn't any need for the second EoH table. It's called automation for a reason. This is turning out to be a total waste of time for both of us. Chris doesn't seem to know what he wants or just doesn't understand at all how anything I've done for him works. First he likes everything and wants more added, and now he wants it all removed? This isn't a willie nillie process as Chris seems to be treating it. 99% of the people on this forum wouldn't have gone as far as I did and continue to do it all for him. Now he seems to want to take it back to how you were originally setting it up.

I'm not making any more changes. There have been at least 3 "final" versions that are exactly what he asked for. Carl, I'm done. I wish you personally good luck because it seems you might be back to square one with what your cousin wants, if he can ever figure it out.

This doesn't reflect on you.

Sorry

The Final Change.

Hi BeachRock,

This is Chris (Carl's cousin), Carl gave me his log in details so that I can message you. Let me first say thank you very much for the spreadsheet, I know you have spent a lot of your own time on this and appreciate the effort you put in, this is a big help for me and my employees.

I know Carl said you wasn't overly keen on the manual entry EoH and Brochure tables. The automatic one you made was great but problem is that we tend to use the spreadsheet at end of day so it will take time for myself to stay back end of day add write up each and every case we have. That is why with the manual entry I can simply enter in the figures for a day and then the next day or a couple of days later, I can write out the cases in the case table when I have time. That is why I needed manual entry. Also I knew you made an automatic Brochure table but brochure had nothing to do with cases. It is my fault as I did not explain to Carl fully first time round so I think that is where the confusion occurred.

But the spreadsheet you made is really good and is perfect except for one tiny thing which I will ask you myself as Carl said if I have got any issues then I need to message you directly. When I wanted a manual entry, I wanted to keep track of the manual entries, so for example if I entered in figure in the EoH Manual and Brochure table for 14th November 2013, then if I change the date to a different one, then it would display the figures in EoH and Brochure table entered for that changed date, if I go back to the 14 November 2013 then it will display the figures in EoH and brochure table I just entered for 14th November 2013. That is how I want the manual entries to work.

I just want to know if you can do this one last favour for me (and it is the last one, everything else is perfect) and then we can start using your spreadsheet fully from next week?

Carl has managed to create an extra date section on the spreadsheet and he said he create a today button for the manual entries, but he does not know how to keep track of the figures for the manual entries when entered, that is why I am asking you. If you have any questions then feel free to message me on my personal email: [email protected].

I apologise for any unnecessary inconvenience I have given you but I appreciate and thank you for all your effort.

Thank you and hopefully you can make this change for us and finish the spreadsheet fully.

Chris Brooks

Sales Manager
SEAT

Listen Chris,

I don't know what you think this forum is for, but it's not a free for all on getting unlimited free help from people who know how to do this stuff, and to have them do your project for you. It's for you to build your own project and learn how to do the things you don't know how to do by getting a little assistance when you are stuck. Every time you add or remove columns or rows, all of the coding in the background has to be updated and the process is always a few hours worth of my time to complete. You act as if I am somehow obligated to continue doing all of these changes for you.

I don't want to or mean to sound like or be a jerk, but do you even understand at all how the version in post 23 works? How any of it works? Did you even look at it? It does EXACTLY what you have just requested of me to do again for you. What you are now asking me to do is give you back cumulative reporting for Brochure stuff when that ability was already there on the version in post #23 and you had me remove it via a request from Carl.

That version gives you the ability to track EoH Cases, EoH Enquiry AND Brochure information, all on a day to day basis, all in one entry table. The cumulative filter tables for both EoH and Brochure gives you the ability to see each individual day ALREADY and the Cases table itself allows you to be able to filter and see ONLY the entries you want to see for any Year/Month/Day, Entry Type, Brand, Status and Case #. The manual entry tables for either EoH OR Brochure are not necessary at all and are completely useless. All of the data is already there for you in the Cases table if you would just learn how to use it.

1. Create a New Record in the Cases table.

2. Set the Year, Month and Day of the new record entry.

3. Set the Entry Type for the new record to either "EoH Case", "EoH Enq" or "Brochure"

4. Set the Status to "Open" or "Closed"

5. Place a 1 in the T Drive, Email or P Date columns for that entry as they are completed.

6. Set the filter for Year, Month, Day, Entry Type and Status at the end of the day to see ONLY the ones you entered for that day.

7. View the EoH and Brochure cumulative tables and click the button "Show Today" and the tally information for all of the entries from the Cases table for that current day are listed.

These are the criteria the cumulative tables need in order to provide the information you want for any day or span of days from the past to the current date. The data is always going to be in the Cases table and also available within the cumulative tables for ANY DATE YOU WANT TO SEE. Your manual entering is done within the Cases table itself.

If you look at the cumulative tables for EoH and Brochure, you can set the date for both Start and End dates to be the current day by pushing the button for that. Both the EoH and Brochure cumulative tables will now reflect ONLY the data for the current day. If you want to look at a span of days you set the Start and End dates as necessary and the cumulative data for that span will be reflected in the EoH and Brochure cumulative tables.

You already had the abilities you are asking for. The extra button you had Carl create won't work without a location to pull cumulative data from for the Brochure tracking criteria. That location WAS the Cases table. I am not going to make yet another entry table for you to track cumulative Brochure stuff when you already had that ability in the attachment on post 23.

You are making this so much more difficult than it needs to be and I simply don't have the time to continue giving to you.

I will make you a deal. I will modify the attachment in post 23 for the following:

1. Remove Dialed and Contacted columns from the Cases table and the EoH Cumulative table.

2. Add a new “2nd Brochure” column to both the Cases table and the Brochure cumulative table and update
the Brochure cumulative table formulas so the new column of data is tracked from the Cases table in the
Brochure cumulative table.

3. Remove the "Case" Entry Type from the Cases table.

Manual entry will only be done using the Cases table and there will not be a manual entry for anything in the EoH/Brochure table area as it is completely unnecessary.

That’s it, final offer. Otherwise figure out how to change all of this stuff yourself.

Originally Posted by carlbrooks

What we tend to do is enter in the figures first for the EoH and Brochure table and then at the end of the day or next couple of days, we enter in the cases in the case table. The reason we do this is because of other tasks we don't have time during working hours to enter in the cases information row by row and we have to make sure each day that we send the EoH and Brochure figures to our head office (they are only interested in the figures for EoH and Brochure each day). We keep track of cases so that the case records match the figures. Because you create the cumulative EoH table now we can do that after entering in all cases.

But if we only use cumulative EoH then we have to keep updating the case table with the latest cases but if we don't have time to fill them in during working hours, then the cumulative figures won't be updated so this will be a problem when wanting to send the correct figures to head office because they are not updated (unless we spend time filling out all the cases but they want it before 5pm every day), so that is why I requested an EoH Manual entry table as well so that if we manually enter the figures first, then we can send those figures first and worry about filling the case records later. That is why I requested for an EoH Manual entry table so hopefully you understand now why I needed it.

In terms of Brochure table, that has nothing to do with the case table at all, it is something completely separate. It's just a manual entry table of figures to keep track on how many brochures we have given out. Nothing to do with the case table at all.

Ok so hopefully i explained the situation fully and that you understand. This is my offer which will be less work than the offer you have given me:

Chris, I think I have figured out what you are talking about. However, rather than this being something that will not take as long for me to do, what you are asking for will take longer yet.

Please let me explain what I think you are asking for and you tell me if I have it or not.

You are trying to have running figures and actual figures with per day and cumulative numbers for both EoH Cases and EoH Enquiry and you only need running and cumulative running figures per day for the Brochure numbers. You want to be able to make those tables each show figures based on the span of the Start and End dates. The reason you want this is because you need to keep track of the totals of the numbers for each of them per day and still be able to report to corporate on those numbers even though you haven't entered the Actual case data yet because you are always a few days behind on that part. The Brochure information is just a continual tally but still has to be based on a date of when they were entered so the span filter will work for it too. I assume you will want the span filter for each to also work independantly for its own category.

You want to have a manual entry table that looks just like the cumulative tables for EoH and Brochure. You want to be able to update the numbers in both of those manual entry tables manually throughout the day and then at the end of the day hit a button that records them and blanks the manual entry cells so they are ready for the next day. The values must be recorded somewhere in order for the cumulative running table to have a place to pull the span filter data. I assume you would use the running figures span filter to remind you of how many EoH cases you are behind on entering for your Actuals. When you do have the time to enter the cases in the EoH Case table, already on the Cases sheet, is when that table is used so you can be able to also track your Actual numbers for EoH. I think you are also hoping for the running and actual numbers to match at some point as you get your self caught up with the Actuals.

Have I accurately described what you want this thing to do? Let me know if I missed anything.

Now let me explain in terms of what it will take in Excel to make what you are asking for.

As I said above, the values for the running numbers per day must be recorded somewhere in order for the cumulative running table to have a place to pull the span filter data. The data manually entered into the EoH manual entry table and the Brochure manual entry table will need to be copied from each table and put onto another sheet at the end of each day using a button/macro. This is to provide a repository for each of them so the additional cumulative tables tracking the running numbers have the data available in order to function at all. Each time data is added to either of these tables, there will need to be a time stamp associated with the entry that is based on the current Year, Month and Day, just like what is used to track the actual numbers on the Cases sheet table. In the end there would be 3 EoH tables (1 for manual entry per day, 1 for running cumulative numbers, and 1 for cumulative Actual EoH numbers) and 2 Brochure tables (1 for manual entry per day, 1 for running cumulative numbers). Then there are the additional span filters, one for each separate cumulative table, 3 in total.

Excel isn't as easy to work with to make the changes you want as you thought. Easier for me than you but I haven't been doing this very long really so I'm sure I'm slower than people who do this for a living. There is no "easy" way for anyone to do it, just the right way. So, as you can see, I would have to basically duplicate most of what I've currently done two more times and all of the code for each current button function would need to be modified to adjust to the new locations for everything because of adding more columns to include the additional tables. I would estimate it would take me several hours.

I sent my email address in a private message that Carl should have received in his email. We should take this offline.