Home |
Search |
Today's Posts |
#1
|
|||
|
|||
help with code for conditional summary sheet
Hi Guys
Initially I'm not sure how to even search for something like this but I am in need of help with a little coding again. I'm getting a maintenance workbook together that has many worksheets in it, each worksheet representing a vehicle. In each worksheet B3 will be the vehicle reg and C3 the type. There will then be a table starting at B7 and ending at E7 with date entered, fault descrip, fault remedy and date complete in them. Each fault being entered in the row below (ie B8-E8, etc). A 5th column (F7, F8, etc) will calculate the number of days the fault has existed without remedy and I'll conditional format them to go red if any get to 7 days. That about covers it for each sheet but what I'd like is a way of doing a summary (a macro??) that goes through each sheet (eg 30 rows) and checks the calculation in column F to see if any are 7 days or older. If they are I'd like it to list B3 and C3 then the relevent row that has the entry that is red (ie B7 to E7, B8 to E8, or whatever row number it is) I know this is a coding thing and I'm completely stuck on how to do this so any help would be very gratefully received. Thanks in advance :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with code for conditional summary sheet
EnglishDad,
You would be much better off if you started your design differently. Instead of one sheet for each vehicle, add a column to on of your sheets, where you enter the vehicle name or ID in that column. Then continue on as you stated. Each time you enter data, just enter the vehicle ID in that column. Now you have a database. Apply data filters (Select a single cell in your table and use Data / Filter / Autofilter), and you can use the dropdown at the top to filter the database to show individual vehicles, those which are overdue, etc. HTH, Bernie MS Excel MVP "englishdad" wrote in message ... Hi Guys Initially I'm not sure how to even search for something like this but I am in need of help with a little coding again. I'm getting a maintenance workbook together that has many worksheets in it, each worksheet representing a vehicle. In each worksheet B3 will be the vehicle reg and C3 the type. There will then be a table starting at B7 and ending at E7 with date entered, fault descrip, fault remedy and date complete in them. Each fault being entered in the row below (ie B8-E8, etc). A 5th column (F7, F8, etc) will calculate the number of days the fault has existed without remedy and I'll conditional format them to go red if any get to 7 days. That about covers it for each sheet but what I'd like is a way of doing a summary (a macro??) that goes through each sheet (eg 30 rows) and checks the calculation in column F to see if any are 7 days or older. If they are I'd like it to list B3 and C3 then the relevent row that has the entry that is red (ie B7 to E7, B8 to E8, or whatever row number it is) I know this is a coding thing and I'm completely stuck on how to do this so any help would be very gratefully received. Thanks in advance :) -- englishdad |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with code for conditional summary sheet
Bernie is right.
But if you insist... Excel 2007 with PivotTable and Macro. http://www.mediafire.com/file/nwnqnj2ztkd/01_26_09.xlsm |
#4
|
|||
|
|||
Quote:
The design of the workbook was passed on to me but it does allow us to print out each individual sheet as a maintenance record in its current state so method behind madness type thing! I will investigate your suggestion of using the filters but I might end up back with the old design due to the printing format. I'd also like the summary sheet to be generated when the workbook opens so that you are presented with the important info straight away. I'm not sure you could do this with the filters option??? I could be wrong though, I am a novice at this!! Anyway thanks again for your input and if you do come across some code for my version, do let me know :) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with code for conditional summary sheet
Herbert,
If all your sheets have the same columns, with the same layout (headers, etc.) it would be easy to use a simple macro to get all the sheets into one database. The big advantage of using a database instead of individual sheets is actually reporting, which is more robust with a single sheet. For example, you could use a pivot table on your database to get a summary, by whatever category you want (depending on what you have in your database, of course) like, say, oil changes, brake jobs, tire rotation, windshield washing, etc. And summaries accross vehicles / time frames would be much easier. Then, you can still print out individual sheets by filtering the database to only show one vehicle. So it is a lot more flexible, a lot easier to do reports.... Take a look at: http://peltiertech.com/Excel/Pivots/pivotstart.htm and go down the links, to get an idea of what you can do. HTH, Bernie MS Excel MVP "englishdad" wrote in message ... Herbert Seidenberg;785757 Wrote: Bernie is right. But if you insist... Excel 2007 with PivotTable and Macro. http://www.mediafire.com/file/nwnqnj2ztkd/01_26_09.xlsm Thanks for your help guys, I can't actually use the above link as I'm using office 2003 :( The design of the workbook was passed on to me but it does allow us to print out each individual sheet as a maintenance record in its current state so method behind madness type thing! I will investigate your suggestion of using the filters but I might end up back with the old design due to the printing format. I'd also like the summary sheet to be generated when the workbook opens so that you are presented with the important info straight away. I'm not sure you could do this with the filters option??? I could be wrong though, I am a novice at this!! Anyway thanks again for your input and if you do come across some code for my version, do let me know :) -- englishdad |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
summary sheet | Excel Discussion (Misc queries) | |||
summary Sheet | Excel Worksheet Functions | |||
How can i copy data from a tabbed working sheet to a summary sheet | Excel Discussion (Misc queries) | |||
Relative Sheet Reference (Summary Sheet) | Excel Discussion (Misc queries) |