Horizontal List (Excel 2003)

Topic

New Reply

I am by nature an access guy but my boss wants an excel spreadsheet to do the following. (See attached). In the first column of the 2nd work sheet I have a date, then name, rank, position and action level. On the 1st work sheet I have the dates running horizontally. What I need to happen is for a concatenated cell to line up under the proper date in a list if there is more than one action for a particular date. The concatenated cell would read : Name & Rank on Line 1then a line break (vbCrLf) Action Level on Line 2 another line break and finally on the third line Position. Is this possible to do? I did something similar once but I only had one item under a date as opposed to a list. I used VLOOKUP at that time. This has all of my peers baffled.

Reply | Quote

Replies

I don’t understand what you want. Could you give some specific examples of what you expect the result to be?

Reply | Quote

Here is the concept. 1. The actual data work sheet containing the data table is going to be linked to an access table which will “refresh” when the spreadsheet is opened. 2. The calendar work sheet (using the formulas) will always open to the current date in the first date column and successive dates following along beside it. 3. Then I want the calendar date cells to look at the data table!due dates and if they match then list under the date cell any actions that need to happen on that date, there will often be more than one action to list. 4. The list in different cells will run vertically under the date that matches the due date. 5. The data I want in each of the cells in the list under the date will be from 4 different columns in the data table, and concatenated onto three lines (line 1 Name & Rank; line 2 Action Level; line 3 Position). So in summary what I am attempting is, as action dates are changed and staff come and go, the the actions data table will update itself from the HR dB and then post themselves in the proper column in the calendar. 1 and 2 I can do no problem it is staring in item 3 that I need help.

Reply | Quote

Doesn’t post 597,595 do that?

Steve

Reply | Quote

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.

Reply | Quote

Thanks Dean,
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. The work sheet will retain the same name column headings and address. What will change is the number of data rows and the specific data within the rows. As the data table updates I need the calendar to re-look for for any data that belongs under a specific date and make the appropriate adjustments.

Reply | Quote

(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…

Reply | Quote

I think I am getting myself wrapped around the axle.
Based on using your SubFunction ‘Update Calendar’, I have the following questions.

1. Do I call the function in the B1 column in the VLOOK formula as is suggested by Steve for his function ‘MyLookupAll’?
Or is there another step?
2. Does the date cell that the VLOOKUP uses to compare with the A column in the data table have to be established and static prior to the running of the VLOOKUP formula?

On the attachment you can see how the dates are posed when the spreadsheet opens. I do not expect the data table to change after the workbook opens. The data table is prepared at a different file and only updates during an update run at night.

I can almost taste an answer for my problem here ,but all I have now is a wiff.

Thanks to all for the patience in working this through.

Reply | Quote

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…

Reply | Quote

Thanks for the help, I used your procedure and called it from a button on the calendar tab. To get all of the dates whether or not there was an event, I modified my query. I add a table with the next several years of dates in it, then did a union where I asked for all of the dates in the date table and the events that matched those dates from the event query. Then I set the date criteria from the date table to “Between Date()-31 and Date()+223”. This gives me an exact number of dates to fill all of the columns on the work sheet. I had to rename the field in the dates table to Due Date but that was no biggie. Also instead of using a refresh on the work sheet I set up an export function in the access file that overwrites the the data table on the spreadsheet. So when those responsible for entering the dates of evaluations run the export after changes are made and the end user just uses the Update Calendar button when he opens his spreadsheet. Makes everything much cleaner. Again thanks for the help.

Reply | Quote

You can do it with a custom function. I modified the VLookupAll function I posted in Re: Lookup more than one row (2000). Add this to a module:

Option Explicit
Function MyVLookupAll(vValue, rngAll As Range)
    Dim sTemp As String
    Dim rCell As Range
    Dim rng As Range
    On Error GoTo errhandler
    
    Set rng = Intersect(rngAll, rngAll.Columns(1))
    sTemp = ""
    For Each rCell In rng
        With rCell
          If .Value = vValue Then
            sTemp = sTemp & vbLf & vbLf & _
              .Offset(0, 1).Value & " " & _
              .Offset(0, 2).Value & vbLf & _
              .Offset(0, 3).Value & vbLf & _
              .Offset(0, 4).Value
          End If
        End With
    Next rCell

    If sTemp = "" Then
        MyVLookupAll = ""
    Else
        MyVLookupAll = Mid(sTemp, 3)
    End If
errhandler:
    If Err.Number  0 Then MyVLookupAll = CVErr(xlErrValue)
End Function

Then in B1 of Calendar, enter the formula:
=MyVlookupAll(B2,’qryListRatingActionsList(1)’!$A$2:$A$70)

and format the cells to wrap…

Steve

Reply | Quote