Hello,
I am very new to Excel and cannot get my head around macros. It doesn’t help that I am also blonde!
I am using MS Excel 2003.
I understand that my approach is probably not the most efficient, but given that I have only tonight to solve this issue I guess I am stuck with it for now. Any assistance you could give me would be very much appreciated.
Originally what I had been doing which worked quite well was:
On sheet 2 “Data Entry” I had a column called ‘Check’ (column D) which contained the following formula:
=COUNTIF(E7:BB7, "<>")
This returned an incrementally increasing value if there was data in the row.
The initial macro I wrote copied the entire sheet (which I renamed Report) and then ran a this little gem of a macro (which I found here) which hid the rows in Column D which contained a ‘0’ whilst leaving the headings (blue fill)
The initial problem I could not solve by myself was how to divide this up on a monthly, quarterly or annual basis.Please Login or Register to view this content.
So I created the attached spreadsheet and as the check column now required more than just 1 column and it wouldn’t fit on a single spreadsheet, I created a ‘master’ validation sheet which contained the relevant formulas for the monthly, quarterly and annual splits across two main areas ‘theme’ – info in the drop down boxes or text in the w/ending month columns and ‘audience’ (located in blocks at the end of each month and corresponds to an entry in the theme section – or should).
The key elements from the client’s perspective is that they wanted to be able to run a report for ‘theme’ and ‘audience’ for a single month (I was thinking current month) or for a quarter (starting from July 12) or annually, with the last option being the next 3 months from the current month.
I would then run the hide rows macro for that month based on the information in the relevant column on the master validation sheet.
That’s where I got stuck and don’t know where to from here.
I have attached a sample spreadsheet minus sensitive data with some sample reports. Only one report tab allowed at any one time.
The key sheets are:
• Hide – Validation (which will be hidden)
• Start here (not required for reporting – will link to relevant sections on the data entry sheet using a macro)
• Data Entry
• Publication Dates (not required for reporting – static data only)
• Generate Report
• Report (generated by macro from the Generate Report tab)
TEST Spreadsheet - Excel Help v1.xls
I am also having problems with the size of the file.
Please let me know if you need more info.
Really thank you – I mean it. Numbers are not my forte.
Suzi
Bookmarks