Never mind - I found you can accomplish this by manipulating the data in Power Query.
That's the way it's imported from another source that I have no access to
Can someone advise how to separate information inside a cell for counting in a pivot table?
I have attached a simplistic example showing various employees performing a task(s) on a given day.
Because of the way the task is supplied in column C, ie where more than one task is performed, is separated by a comma, the pivot table incorrectly shows the amount tasks performed by each employee as just one each.
How is it possible to count each task correctly for each employee?
Thanks for the update. I'm just trying to cover my bases here. The people destined to be using the excel sheet are the type to complain that Clear All doesn't actually clear all when they have clicked additional cells...;-) but I see you have removed that option... which in turn removes that issue.
Also the cells in the 'hidden row' contains text in cells which is referenced by another VBA sub which is irrelevant to what I'm trying to achieve here, but thought I'd include it to help demonstrate if it was impacting on the issue that we are discussing.
I'll go and study it now and see if I can learn something from it.
Again, many thanks for your help.
Hi Roy, sorry to be a bit of a pain but I've since noticed an anomaly which wasn't apparent to me at the beginning when I was trying to get my head around this.
I've moved the Data Validation selector to it's proper place in my chart (I6) and amended the VBA to suit and all worked well up until I filled in row 6 columns with header titles.
Because the sheet VBA now sees text in (row 6 , column 18), the vba bombs out and I get a run time error 13 mismatch error when I select Clear cells.
I've tried adjusting the row offsets but this just creates issues when selecting the proper row ie will select a row when there is no text (email address) in the corresponding cell in column R.
Also Clear all, doesn't actually clear all ticks. It only clears those ticks that were made using the Select All.
If after Select All I then make additional selections by manually ticking individual cells in column I, these selections are left behind when Clear all is selected.
Can you help please?
You sir, are a genius!
Just the job.
Exactly what I'm after.
Thank you so much for your help with this. With the drop down menu it works as advertised so I have no complaints at all and I really appreciate it.
However I feel that maybe I wasn't clear in what I was asking for.
What I meant was I would like the ability to maintain individual row selection as demonstrated in the attached file, with the addition of the ability to select all or none. Unless I have missed something, your code removes the individual selection ability and replaces it with all or nothing?
So ideally, clicking A1 on or off selects all or nothing... whilst clicking A2.... A103 still individually selects / deselects that row.
Hoping someone can help me with this.
I have been playing with a VBA routine that allows a person to select multiple rows in a sheet. (Not my work - I 'liberated' it from a you tube video).
The routine simply allows you to select a cell in column A and it will place a tick in the box and highlight the row with a colour using conditional formatting. Similarly clicking the same cell unticks the box and removes the highlighting colour.
This works fine if you only want to make one or two selections but becomes tedious if you want to select a lot of rows.
So, what I would like to do is be able to 'select all' by clicking Cell A1 and have it select all those rows that have an email address in the corresponding row in column R.
Similarly, clicking A1 again deselects everything.
Wow - something so simple.
This now works as I would expect.
Thanks very much for pointing me in the right direction. I've been staring at this for weeks... and now I also understand a little more as to where my error was.
I have a word document that contains four bookmarks unsurprisingly named "Bookmark_1", "Bookmark_2", "Bookmark_3" and "Bookmark_4".
I want to paste a different sentence from Excel into each bookmark. The sentence to be pasted is chosen based on where it should appear in the document and the value of a variable named Status.
The bookmark name should be retained and not destroyed when pasting the new text as the document is cycled through several times, with successive documents containing different text based on the value of status.
The value of the Status variable is obtained from elsewhere with the original Excel sheet, but I replicated it manually in this Sub just to make the selection process work.
My issue is copying the chosen text in the Text(n) string into the bookmarks in the Word Document - which I seem unable to achieve.
Having watched several video's on you tube and read some articles stating how 'easy' it is, I managed to replicate their demonstration but have failed to successfully implement it into my worksheet.
The original example used this which I understand is pulling the text from a worksheet cell A1 which works just fine in testing.
.Bookmarks("Text2").Range.Text = ws.Range("A1").Value
However because I'm pulling from a string and not a cell, I get an error message "Runtime Error 424: Object Required
Can someone please advise where I'm going wrong?
- Sub Paste_Text_Into_Word_Bookmarks()
- Dim objWord As Object
- Dim ws As Worksheet
- Dim x, BmkCount As Integer
- Dim Text_1, Text_2, Text_3, Text_4, Status As String
- Set ws = ThisWorkbook.Sheets("Sheet1")
- Set objWord = CreateObject("Word.Application")
- objWord.Visible = True
- objWord.Documents.Open "C:\Users\Smudge\Desktop\test.docx" ' change as required
- 'Select text to be pasted into bookmarks
- 'Status is simulated here by manually making x = 1, 2 or 3
- x = 1
- If x = 1 Then Status = "Single"
- If x = 2 Then Status = "Pair 1"
- If x = 3 Then Status = "Pair 2"
- For BmkCount = 1 To 4 'Number of bookmarks in word doc
- If BmkCount = 1 And Status = "Single" Then
- Text_1 = "Text for Bookmark 1."
- ElseIf BmkCount = 1 And Status <> "Single" Then
- Text_1 = "Alternative text for Bookmark 1."
- ElseIf BmkCount = 2 And Status <> "Pair 2" Then
- Text_2 = "Text for Bookmark 2."
- ElseIf BmkCount = 2 And Status = "Pair 2" Then
- Text_2 = "Alternative text for Bookmark 2."
- ElseIf BmkCount = 3 And Status = "Single" Then
- Text_3 = "Text for Bookmark 3."
- ElseIf BmkCount = 3 And Status <> "Single" Then
- Text_3 = "Alternative text for Bookmark 3."
- ElseIf BmkCount = 4 And Status <> "Pair 1" Then
- Text_4 = "Text for Bookmark 4."
- ElseIf BmkCount = 4 And Status = "Pair 1" Then
- Text_4 = "Alternative text for Bookmark 4."
- End If
- Next BmkCount
- With objWord.ActiveDocument
- .Bookmarks("Bookmark_1").Range.Text = Text_1.Value
- .Bookmarks("Bookmark_2").Range.Text = Text_2.Value
- .Bookmarks("Bookmark_3").Range.Text = Text_3.Value
- .Bookmarks("Bookmark_4").Range.Text = Text_4.Value
- End With
- Set objWord = Nothing
- End Sub