Hi Pip,
you posted your first thread at about 7:20pm my time yesterday, so I figured you'd be up and about now as it is about 8:50pm here - not sure exactly how many hours there are between us. I've put together a formula-based solution to your problem (attached), so a few general comments first:
As you submitted the file as an .xls, I've saved it in the same format - you get a few warning messages about conditional formatting and colours used, but you can ignore these if you save to the same format. I didn't enable macros, so I didn't look at what you had in there - I suspect they are linked to the buttons on the first sheet to enable a user to jump to other sheets. I've removed all filter settings and also revealed all columns that were hidden, as it is better to be able to see everything and avoid unwanted consequences to formulae or data in hidden columns. I figured you'd be able to set things up as you want them if you are happy that the workbook does its job. I haven't changed any of your data, but I changed the NOW() function to TODAY() in column M, as you only want the date rather than the date/time. I've set most sheets up down to row 30, although a couple of sheets needed more rows. If you need more, then just copy row 30 down into row 31 onwards. I've also set up Freeze Panes on most sheets.
I've amended the Status formula to this in R7:
and I've also set up the conditional formatting using the same colours as in column Q. I decided that you probably would want to include data from the Quality Assurance sheet, so I made the necessary changes to that.
I've added a new sheet called Lookup_tables, which basically lists the sheet names and the maximum number in columns U and V on each sheet. The general approach that I've taken in this file is to identify records which match the criteria in the Status column, and then set up a simple sequence, which will automatically propagate into the next sheets in line. So if there were three records identified in the first sheet they will be numbered 1, 2, 3, and then in the next sheet the numbering would carry on to 4, 5, 6 etc for other records which match the criteria, and then again into the third sheet.
To accomplish this I've used columns U and V in each sheet as helpers, with the sheet name in U1 and "Due" and "Overdue" in cells U6 and V6. This is the formula in U7:
and the formula is copied into V7 to give this (virtually identical):
If the status matches row 6 then a number is incremented, otherwise a hyphen is returned (this helps to show where the formula has been copied to). In the first sheet in the sequence (Quality Assurance) cells U5 and V5 both contain zero, but on other sheets U5 contains this formula:
=MAX(INDIRECT("'"&INDEX(Lookup_tables!$B:$B,MATCH($U$1,Lookup_tables!$B:$B,0)-1)&"'!U:U"))
(The formula in V5 is very similar - it just has V:V at the end instead of U:U). This finds the largest number used on the previous sheet, so that the numbering can carry on in sequence. It does this by finding where the sheet name from U1 exists in the lookup tables, and then choosing the one before it, and then looking in column U of that sheet for the largest number. That's the crucial part of it in this situation, getting the sequential numbering established - the other formulae in the reporting sheets are basically INDEX and MATCH.
I have again used columns U and V on the reporting sheets - column U identifies the sheet and column V the row within that sheet where the sequential numbers occur (i.e. the records which match the criteria. In the DueDocuments sheet this formula is in U7:
and this in V7:
The first uses the numbers in the lookup tables to identify where ROWS($1:1) exists and returns the appropriate sheet name. Note that the term ROWS($1:1) returns 1 on that row, but as the formula is copied down it becomes ROWS($1:2) then ROWS($1:3), returning 2 and 3 respectively, so that is how the sequential numbers are generated on this sheet. Again, hyphens are used to show where the formula is active but not returning anything, rather than using "" which is difficult to see !! The second formula finds the row where that sequential number exists in column U of that sheet.
So, in column A an INDEX formula retrieves data from column A of the appropriate sheet for the matching record, with this formula in A7:
A similar formula with b:b, c:c instead of a:a could be used to get data from column B, column C etc. However, I did notice that you have some gaps in your data on some of the sheets, so rather than return a zero for these (which is Excel's default approach), I modified the formula in column C to this:
i.e. if it is empty then return a blank otherwise return the value. This is then copied across with changes made to the c:c to suit other columns. And with the appropriate formatting set for dates etc., that is basically it. The set-up for the OverdueDocs sheet is very similar, although the formulae in the helper columns look at column V in the other sheets.
Well I hope this explains how the sheet works and that you can use it going-forward.
I'll submit the post first and then attach the file, as the Forum is acting up a bit today and I don't want to lose it all as happened to me the other day.
Pete
LinkBack URL
About LinkBacks
Register To Reply
Originally Posted by HaHoBe
Re: VBA to 'on open' of workbook look at all sheets for 'OVERDUE' and transfer to Main Rep
Bookmarks