Hi
I have a staff rotas that are a bit of a mess but I am not allowed to change them.
So I have linked them and bought them into one workbook to format and make function in a more useable way.
To that end I have been asked to show a list of staff availability.
I hope the attachment explains it.
I have tried using Index/Match/Aggregate but I am stumped! [ATTACH]n1218443[/ATTACH]
Thank you for checking for me. I have discovered why I couldn't get mine to work. The date is displayed as DAY only but the actual date behind it was incorrect!
Never rely on someone elses understanding of excel!
I have this vba code which scrolls across a date range to the date entered in cell E4 from column K and returns to column K when E4 is blank
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngMatch As Long
If Not Intersect(Target, Range("E4")) Is Nothing Then
On Error Resume Next
lngMatch = Application.Match(Range("$E$4"), Rows(4), 0)
On Error GoTo 0
If lngMatch > 0 Then
ActiveWindow.ScrollColumn = Cells(3, lngMatch).Column
Else
ActiveWindow.ScrollColumn = Cells(3, 11).Column
End If
End If
End SubNow it works fine when applied in Worsheet 1 but not when applied to work sheet 2 as well
How do I apply this code to all worksheets in a single workbook? I have 3 worksheets in my workbook.
Many thanks
Thank you.....I don't know is the simple answer so I probably haven't.....how do I do them??
Many thanks
SO I have found many vba codes online that output comments into cells from the master workbook to my workbook. The comments can then be used with conditional formatting for my purpose. All good so far.
But when I log back in to a PC and open both work books the output comments show '#value' and do not work. I have to re-link to the source data each time.
Is this to be expected?
Is it possible to link a range of cells to another spreadsheet so that the formatting and comments are linked too.....as it does with copy & paste?
I have been told this is only possible with VBA/macro. I have linked a sheet from one workbook to another and need see the comments and formats. I have no experience with VBA code. I am linking an entire worksheet.
Is it possible to link a range of cells to another spreadsheet so that the formatting and comments are linked too.....as it does with copy & paste?
Unfortunately I am not able to upload the full spreadsheets as that is not allowed by my employer.
All I was allowed to do was to provide an example and then adapt that to work for me.
The main difference is that I need to import the information into one workbook from 2 others and not from one sheet to another. I can do it using the index/match formula. I got that working on the example and then adapted as required. I was hoping to be able to do the same with VBA code.
I will create 2 other workbooks as an example for you when I return to work.
I do greatly appreciate all the work you have done and it is a great help for me to see the programming required to answer my question.
I will create another post in due course!!
Many thanks thus far!
Simon
Unfortunately I am not able to upload the full spreadsheets as that is not allowed by my employer.
All I was allowed to do was to provide an example and then adapt that to work for me.
The main difference is that I need to import the information into one workbook from 2 others and not from one sheet to another. I can do it using the index/match formula. I got that working on the example and then adapted as required. I was hoping to be able to do the same with VBA code.
I will create 2 other workbooks as an example for you when I return to work.
I do greatly appreciate all the work you have done and it is a great help for me to see the programming required to answer my question.
I will create another post in due course!!
Many thanks thus far!
Simon
I will try that when I am back in the office. I now have 2 weeks off work so it will be after that.
A couple of questions...
Why does the date format have to be written in reverse? People will use the Excel shortcut to input today date for example.....
Can it be made to work with dd/mm/yyyy?
and
Given that this is an example and with the expectation that it will work, I will explain how I need it to work for the final spreadsheet I need to create.
It will do exactly the same as the example but will collect the data from 2 other workbooks. Both are staff rotas.
In Workbook 1 there is a list f 14 names in C4:C18. In Row D3:NJ3 are the dates for a year from 1st April to 31st March
In Workbook 2 the is a list of about 60 names C4:C56. Dates are as above.
From workbook 1 I need to collect the data/comments/colours from the last 7 names C11:C18
From Workbook 2 I need to collect the data/comments/colours from two lots of names form C11:C18 and C40:C47
In work book 3 I will have a list of names similar to the example with one cell to input the date and 6 other cells with the date derived from B5.
The names will be in Column C but the rows will not align with the other workbooks.
Needless to say I have to formula to collect and display the data but I need a code to import the matching comments/colours and for it all to change with date changes and then to clear when the date B4 is blank
I hope this is possible???
[ATTACH]n1204267[/ATTACH]
Try this one for me please?
OK I have used it on the attached example and another I have......neither work for me!!???
I am doing as you said in your first reply but it only gives the first lot of formatting and doesn't change or clear....am I doing something wrong?
Ok this time it will format correctly for the inputted date. So any date from sheet 1 can be entered in Sheet 2 B4 for the 1st time and it will format as desired
But entering subsequent dates the Comments and Colours remain from the first input date.
I still need the formatting (Comments and Colours only) to change with the date on sheet 2 to match sheet 1.
Many thanks
Simon
Any of the dates shown on sheet 1.
I start with 1/04/2018 and it populates with contents, comments and Colours.
I then change to 05/04/2018 and it will update contents, as per formula, but Comments and Colours remain as they were from previous date.
If I then delete the date all contents disappears but Comments and colours remain.
*****
What ever date I enter, that appears on Sheet 1, the Comments and Colours are the same as though I had entered 1/04/2018.
So it is only picking up the comments and colours for that date.
Thanks
Ok it is still not moving the Comments or Colours.
If you enter a date it will populate as on sheet 1, contents,Comments and Colour, correctly. When the date is changed the contents changes but the Comments and Colours remain from the previous date. The comments and Colours need to move with the dates....
Sadly I don't have any VBA knowledge so I cannot see how your suggestions operate.
Many Thanks once again!
Thank you
Its getting there. The contents of the cells up date when the date is changed as required but the Comments and Colours stay.
I need the Comments and Colours to behave in the same way as the contents. Move with the date they appear under.
If the Date in B4 is deleted then the range goes blank.
Many thanks
Thank you for that it works ........but not as I need it to.
When I change the date in B4 the data in B5:H13 is not updated....it remains the same.
There is a formula in B5:H13 that imports the cell contents(only the 1st 2 characters) from sheet1 when the date in B4 is changed or be blank when B4 is blank.......and I want that to remain....unless it can be built into the code. I only need to add the Comments and Cell Colours using the VBA code.
Many Thanks in advance...
I have an example spreadsheet that contains data across a date range against a list of names on sheet one
I have used index/match to import the cell contents to sheet 2 but I also need to show any comments and cell colours.
This isn't possible using index match but I believe it is with VBA. I would like it to work when the data updates....so the formatting changes corresponding to the data.
Example attached
Many thanks
Simon
[ATTACH]n1204148[/ATTACH]