Replies

People who went over to Mr Excel have already seen it, but if not, Jon Peltier (who is one of the real experts on charting in Excel) points out that Andy Pope had a very similar solution to the same problem although his approach uses a (very slick) UDF to calculate the cross-over points, and then pastes the appropriate values into the plot ranges on demand via VBA. One really nice thing about Andy’s approach is that it allows an increasing or decreasing target value, which is pretty cool.

Hans – right you are; while I am tempted to claim that I was merely testing to see who was paying attention, I have to admit that I just wasn’t quite as clear as I should have been.

If you needed this sort of effect for data that you intended to present as a line chart (say monthly values for some statistic) you would have to re-cast it as an XY chart using a proxy like the start of month or mid-month date.

There is no excel function that will do exactly what you need. I couple of years ago I needed to interpolate on some complex (actually – unknown, but not known to be simple) curves, and I wrote a UDF to do it. The UDF takes arguments of the range of known X and Y values for the curve, the X value for which an interpolation is required, and an optional argument to determine how to deal with values of X outsdie the range of the known X values. I hope the treatment is evident from the UDF module. Note that you may need some error checking – for instance, if the calculated TG is 3.6 that could either represent an unacceptable value, and the sheet should throw up an error result, or (as it is set up now) it just “assumes” that it can calculate based on extending the values in the yellow, orange, and blue data columns linearly.

The attached file works by determining the nearest depth above and below the measured depth. It interpolates between the values in the yellow, orange and blue columns for each of these depths based on the calculated TG (see cellsI7:I8). In each case the “Known X” values are the TG’s identified at the top of the column, the “Known Y” values are supplied by an offset function that reads down into the table an appropriate number of rows, and the “X” value of the estimate is the calculated TG. As noted, if the TG is outside the values of 2.4 to 3.5 it will extrapolate linearly from the nearest data points. This returns a value for the depths above and below the measured depth for the calculated TG. The calculation then interpolates between these two values for the two depths based on the measured depth in cell I10.

[indent]

---

and specify that the value must be between 1000 and 9999

---

[/indent]

Shouldn’t the bounds be 0-9999, or perhaps 1-9999? Is the 7th digit of a SSN always >1, or is a zero possible in that space? I am not aware if there are restrictions that would prevent a SSN of -say- 9889-44-0012…

Ummm….

Because until two minutes ago, I didn’t know that the “ROW()” function existed…

Old dog; new trick!

Sure.

In the usual case, when you are colouring every “Nth” row, you test for MOD(RowNum, N) = 0 – in this case, test for MOD(RowNum,8) <4. The actual formula in the conditional formatting test would be:

=(MOD(CELL("row",E5),8) < 4)

In fact, this will leave the first three rows uncoloured, then begin alternating 4-row blocks. If you really need to get the first four uncoloured, use

=(MOD(CELL("row",E5) - 1,8) < 4)

File | Page Setup | Sheet tab | Columns to repeat at left
(use “Rows to repeat at top” for page headers).

[indent]

---

if you’ve done other things in between, deleting and recreating the legend….

---

[/indent]
I’m usually more in that boat…

Thanks, Hans.

[indent]

---

Click on an undesired label.
Press Delete

---

[/indent]
Is there any way to undo this, short of deleting the legend and recreating it?

If Time In is in A1, and Time Out is in B1, try:
=B1 – A1 – 1/24
formatted as time

The reason is that excel stores times as “fractions of a day” so 1/24 represents one hour

drat! Hans beat me by 0:02 (or 0.00139 of a day!)

You could sort and count the data by putting it into a pivot table with the data field either as a count of items or the sim of a dummy value set to one for each data point – see attached. If you prefer you can drag the “Type” button to the left column of labels to get the single-column format you specified – this was just more compact. I had to delete some of the data to get the zip file to fit on the Lounge – it was right at 100KB when I tried to post it the first time.

By the way, there were some “P” “F” (etc) values that were distinguishable by leading or trailing spaces. I removed them with a “Trim” function – if this is correct you will have to do the same with your data file…

Hi Bill:

The “CalendarUpdate” routine is not a function, but a procedure – the distinction is that a function is (typically) entered into a cell, and returns a value – which is how Steve’s “MyVLookupAll” UDF works. A function cannot change any part of the worksheet – it can only return a value in the cell it is entered into. A procedure can change a worksheet (for better or worse) – in the case of “CalendarUpdate,” it pastes the text you want (Name, Rank, Action Level, etc…) into the Calendar tab and changes the format of the cells it has pasted data into to make it display properly – but first it has to be run. Unlike a function, which returns a value continuously and (usually) updates it immediately, a procedure is active at a particular point in time (“event procedures” are activated without deliberate user intervention, but they are still activated at specific times). A function has to be entered in the cell where you want the result to appear; a procedure can usually be run from anywhere in a file, although sometimes the results will depend on which sheet or which particular cell was active when the procedure was run. In the case of CalendarUpdate it doesn’t matter, since it doesn’t operate on the selected cell or worksheet – the only caveat is that the worksheet names are hard-coded into the routine; if they change then the references in the routine will have to be changed or it will fail when it runs (and bring up a VBA warning that the “subscript is out of range” – which won’t help anyone).

Date References
When CalendarUpdate is run it will first erase all the data in the Calendar tab, and then paste in all the the current data from the ‘qry’ tab – it doesn’t matter whther dates have been entered in the B2:xx2 range or not, since they will all be erased in any event. The routine reads all the dates from Column A of the ‘qry’ tab – if there are twelve distinct dates it will paste them into the range B2:M2 of the Calendar tab; if there are 50 it will paste them into B2:AY2, and so on. Note that it will not insert a date if there is no entry for that date in the ‘qry’ tab – if you need (or want) to see blank columns for days when there is no scheduled activity the routine will have to be changed to accommodate that.

Running CalendarUpdate
If the qry data is only updated once when the file is opened, it makes sense to run CalendarUpdate once as well, immediately after the query has been refreshed. As I said I think that if CalendarUpdate is run from the Workbook_Open event it will occur after the query has been refreshed – but I am not sure of that; aside from an appeal to authority, the only way to be sure of that is to try and then test the result.

• In the “This Workbook” code pane click the left dropdown to bring up “Workbook” and the right dropdown to find “Open” – the VBE will automatically put in a skeleton for the “Workbook_Open()” event
• type CalendarUpdate as the ony entry in the skeleton
• close the VBE, and return to the Excel file
• in the ‘qry’ tab, enter some obviously visible false information (or change an existing record) – the work surrounding daffty Duck” for example – then save and close the file
• re-open the file – this will update the ‘qry’ tab, and the false entry you made should be overwritten. Switch to the Calendar tab – if the CalendarUpdate routine ran before the query was refreshed then Daffy Duck will be listed on the Calendar tab; if that entry is not listed then the update worked with the good data, and we should be happy with that arrangement
  [/list]If Daffy Duck is still listed, you have two options:
  • attach CalendarUpdate to a Command Button (probably on the Calendar tab – you just have to remember to run it after yoiu open the file and the query has been updated
  • attach CalendarUpdate to the Worksheet_Activate() event of the Calendar tab: if you save the file with the qry tab active then when it reopens it will refresh the query with the qry tab open – on switching to the Calendar tab it will re-create the calendar automatically.
    [/list]On either option, it might make sense to include a line in the routine to enter a “Last updated on” tag on the calendar as a trigger to the user that it might need to be updated…

[indent]

---

I can’t think of a good reason for having that space there at all.

---

[/indent]
Just a matter of taste, I guess, since I really like having that space and format cells to get it (it also shows up in the ‘comma’ formats). It separates text in titles and labels from the cell border when laying out a table, as well as providing a little more space between numeric values and any text in the cell to the right.

(Edited by dcardno on 08-Sep-06 22:33. )

Bill – I think either of the solutions posted so far will work for you – they will just work a little differently .

[indent]

---

The “qry” is just static for now but in the final version it will actually be linked to an access data table and will refresh upon opening the spreadsheet

---

[/indent]
I thought so…

To get the refreshed data into the “Calender” tab you would have to either re-run the “UpdateCalendar” routine that I suggested, or using Steve’s UDF, you would have to ensure that the data range supplied in the function argument matched the range returned by the query – you could do that with a dynamic range name defined for the range =OFFSET(‘qryListRatingActionsList(1)’!$A$2,0,0,COUNTA(‘qryListRatingActionsList(1)’!$A:$A)-1,5) and using that in the function argument.

The limitation in Steve’s approach (and it is pretty minor) is that you have to establish the dates in the range B2:Bxx (EDIT: range B2:x2) on the Calendar tab – so if you have different dates after a query refresh you have to make sure that the strip of dates in that row includes all the dates that you need to report on. If you have different numbers of dates then you will either have to add or delete formulas (or dates) in the columns to the right, as required. At the same time, the formula is dynamic – if you manually change the data on the ‘qry’ sheet (or if you refresh it) the changes will show up on the Calendar sheet (subject to the comments about getting the right dates in the top row). Using my routine the right dates will be reflected when you run the routine – but the results are not dynamic: they are pasted into the Calendar sheet as text. Of course, this is good and bad as well – you can annotate them as required, or change the format, if you like.

I would suggest including the “UpdateCalendar” routine as an “auto open” event – on the “this workbook” class module include “UpdateCalendar” in the “Workbook_Open()” event. I believe that the “on open” will fire after the query has refreshed – if not, you could have “UpdateCalendar” run when on the “before deactivate” event of the ‘qry’ tab – if you save the workbook with the ‘qry’ tab selected then when you open it the query will refresh, and when you click off the ‘qry’ tab and onto the ‘Calendar’ tab it will automatically refresh the ‘Calendar’ tab…

Try:

Sub UpdateCalendar()

Dim i          As Integer
Dim iToRow     As Integer
Dim iToCol     As Integer
Dim dtLastDate As Date

Dim shData     As Worksheet
Dim shCal      As Worksheet

Set shData = Worksheets("qryListRatingActionsList(1)")
Set shCal = Worksheets("Calendar")

With shCal
    .UsedRange.ClearContents
    .Cells(2, 1) = "Date"
    .Cells(3, 1) = "Actions Due"
End With

dtLastDate = shData.Cells(2, 1)
iToRow = 2
iToCol = 2
shCal.Cells(2, iToCol) = dtLastDate

For i = 2 To shData.UsedRange.Rows.Count
    If shData.Cells(i, 1) = dtLastDate Then
        iToRow = iToRow + 1
    Else
    With shCal.Cells(1, iToCol)
    .ColumnWidth = 200
    .EntireColumn.AutoFit
    End With
        iToCol = iToCol + 1
        iToRow = 3
        dtLastDate = shData.Cells(i, 1)
        shCal.Cells(2, iToCol) = dtLastDate
    End If
    Call PasteData(i, iToRow, iToCol, shData, shCal)
Next i
With shCal.Cells(1, iToCol)
    .ColumnWidth = 200
    .EntireColumn.AutoFit
    End With

shCal.UsedRange.Rows.AutoFit

End Sub

Sub PasteData(i As Integer, iRow As Integer, iCol As Integer,  _
FR As Worksheet, LOC As Worksheet)

Dim stText As String

stText = FR.Cells(i, 2) & ", " & FR.Cells(i, 3) & Chr(10)
stText = stText & FR.Cells(i, 4) & Chr(10)
stText = stText & FR.Cells(i, 5)

With LOC.Cells(iRow, iCol)
.Value = stText
.WrapText = True
End With

End Sub

This will paste the cell values you want into the Calendar tab as static text. A dynamic link would be really hard and (I suspect) quite fragile. You could attach this to a button on the Calendar sheet and just update whenever the source data changes.
It seems that the data is coming from a query – if that changes the name of the sheet you will have to find a way to update the object used in the VBA code for the source data sheet. Note that I had some trouble getting the columns and rows to auto-fit, and setting the columns as really wide then “auto-fitting” them was a kludge to get it to work – others may have a better approach. I would also add a “screenupdating = false” /true pair – but this is still in debugging mode…

Edit
Two things I forgot to mention. First, since this is sourced from a query, I assumed that the data would be in date order. If not, you will have to sort the data sheet by date (ascending or decending, whichever is preferred) before you run the routine. Second, this does not list “all dates” along the top row in the calendar – only the dates where an action item is identified.