I was wondering if this is even possible: We have trainers that travel all around the world. We would like to use a calendar base sheet to see in what country they are over a date period. To gather the information, I have created an Excel form. Now, how can I populate data in new cells using the start date that was filled in in the form. This entry should now contain the Name of the country and the Time Zone.
Welcome to the forum.
So you are wanting to determine a country and time zone from a date entry alone? This is not feasible.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
It would help if you attached a sample Excel workbook, showing how your data is laid out and what you want to do with it. The second yellow banner at the top of the screen explains how you can do this.
Hope this helps.
Pete
Thank you very much. I am going to see what I can put toghter to explain this.
Ok, here is the example file:
The trainer in the field, that will travel to a country (data is not correct - just an example), will complete the form and from the data sheet we can get the information to populate the calendar on 3rd sheet.
Now is there any way that we can automate the population to sheet 3 using the data from sheet 2?
Last edited by paulabrink78; 05-24-2024 at 05:50 AM.
OK - first problem: you can't have both manual entry AND formula entry on the Gantt chart: it's one or the other. If you do enter manually over a formula, then the formula is gone. So, leave would need to be listed somewhere and brought in with a formula.
This is far and away from what I thought you were asking!!!
Mmm, I have searched the web over and down, and I could not find anything that can do this. Seems the Calendar sheet will then have to be populated manually.
What are you on about??? I have just said (implied) that it can be done. The only caveat is that you will need to list your employees' leave periods in a table. Maybe it could be populated by a form, or you could populate it manually.
Do you understand?
For example:
AliGW on MS365 Beta Channel (Windows 11) 64 bit
First Name Surname Type of Travel / Leave Date From Date To Country of Travel Time Zone Paula Brink Training India UTC +3 Dawie Brink Training France UTC +2 Gerritt Coetzee Leave
Sheet: Data
The data in your Gantt chart doesn't match the data in the data table - that's not really a great set of sample data! It's supposed to be realistic, but I can work with it (but I'll have to change it).
You have gone, perhaps incorrectly thinking this can't be done. Well, in the hope that you come back, here's a mock-up of what can be done.
I have updated rows 3, 4 and 5 of the Gantt chart to make this work.
Then in B6 copied across and down:
=IFERROR(LET(pf,FILTER(Data!$B$3:$H$5,Data!$B$3:$B$5&" "&Data!$C$3:$C$5=$A6),FILTER(INDEX(pf,,6)&" "&INDEX(pf,,3),(INDEX(pf,,4)<=B$5)*(INDEX(pf,,5)>=B$5))),"")
Let me know if you want to pursue this.
You might need to improve your search skills, then, because there's a lot of this out there!I have searched the web over and down, and I could not find anything that can do this.
O wow, this is great! Thank you so much.
Yes, I would love to figure this out some more.
So then, the trainers do not know where they will travel to say more than 2 or 3 months ahead. This means, they will need to complete the form on a regular base, meaning that the data should not add into new lines on the Gantt, but it should rater keep on populating in the record that exists.
Does this makes sense?
It won't add lines to the Gantt chart. Have you tested it?
Have a look at the attached: I added another line of data for Paula Brink and you will see that it's been added to her row on the chart. I have also changed the data table into a structured table and changed the formula to reflect this - as lines get added to the table, the formula will adapt.
If this is not possible, then you'd just need to make the ranges longer than they'll ever need to be.
I'm attaching a copy with some conditional formatting applied to give a better idea of what this could look like.
Hi AliGW,
Thank you so much. I am studying this intensely. I never even anticipate that this could be done!!! This is all new to me.
May I ask, if you could perhaps explain the formulas to me:
=EDATE(B3,1) - As far as I can see this populate a new month, but I need to make it 2 for July, 3 for Aug? Or I can adjust the B3 to AG3 to get July and so on?
=SEQUENCE(,EDATE(B3,2)-B3,B3) - I can understand somewhat that this refers to the Month May in B3 - But what exactly are we calculating here? I am having difficulty to copy this over for the rest of the months.
=LEFT(TEXT(B5#,"DDD"),1) - Am I correct to say, this looks at the number of the day 1 in B5, using the Left function to extract that but we want to see the Name if the Day extracted not the number of the month day?
Wow, this is awesome.
Let me stop here for now.
Yes:
B3 - contains the date 01/05/2024 (formatted as "mmmm").
EDATE(B3,2)-B3 - takes the date TWO months after the date in B3, then takes B3 away from it - this determines the number of days/dates that we need for our 2-month spread (here that's 61).
SEQUENCE(,61,01/05/2024) - we need sequence to generate a sequence of dates (61 of them) starting with 01/05/2024.
LEFT(TEXT(B5#,"DDD"),1) - returns the date in B5 and formats it as a day name, taking the leftmost character from that name - B5# simply means that we are going to do this for the whole of the row starting at B5 and ending where the formula in B5 ends (BJ5).
Attached is the workbook with some breakdowns to explain.
Is the:
=IFERROR(TRIM(SUBSTITUTE(LET(pf,FILTER(Table1,Table1[First Name]&" "&Table1[Surname]=$A6),FILTER(INDEX(pf,,6)&" "&INDEX(pf,,3),(INDEX(pf,,4)<=C$5)*(INDEX(pf,,5)>=C$5))),"Training","")),"")
Dependant on the formula for the Days, Dates, ETC or can I manually populate the rest of the year to Dec?
I have to go and do some jobs. When I come back (in about an hour), I'll show you how to extend the calendar with the formulae. I hope you don't mind being patient.
OK - I've quickly done it. I've also updated the conditional formatting rules to match. I have NOT formatted the month sections or filled in all of the month names - you can do that (follow my example for July for the rest).
I shall be back in an hour or so to take any further questions.
No, I do not mind at all.
Also need to run for my boy at school. I will only look at this tomorrow again.
Thank you once again for all your help - I am learning so much!
Did you want to do this for a full calendar year?
OK - I've assumed that you will want the whole year. The opening date is taken from cell A1, which contains the year. The month shading will update automatically, but note that the merged cells above will need tweaking next year (which is not a leap year). I am sure you'll be able to do this. I have also frozen column A containing the staff names so that you can scroll across the Gantt chart and still see them.
I hope this helps - just shout if you need any further assistance.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Good morning, I am viewing your file now. Thank you so much for your help so far.
Glad to have helped.
Let me know if you would like further guidance.
If you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Yes, please - please can you have a look at my Macro - This is they way to submit a record. - So I have used the Concatenate function to combine the Country and the UTC - Please see the Raw Sheet. But now, Is it possible to so my answer in the Formula bar instead of the formula?
Last edited by AliGW; 05-28-2024 at 04:37 AM. Reason: Please don't quote unnecessarily - use the Quick Reply button instead.
Here is the file
No, sorry - I don't do VBA. Do you want me to move this to the VBA section for you?please can you have a look at my Macro
No, unfortunately not. You might want to see if you can get the VBA to populate the grid. I can't help you with this.Is it possible to so my answer in the Formula bar instead of the formula?
Ok, no that is fine. I am also not very into VBA, but I can search.
Thank you so much for all your help.
Is it possible to submit 2 Reputations?
You don't need to search - if I move the thread, a VBA expert can advise you.
Spometimes you can rep spomeone twice, sometimes you have to rep another member in between.
I am moving the thread.
Sorry 1 last option: Is it possible to build in merge cells into a function?
So then, when it gathered the info - merge the number of cells that was complete on the Gantt?
Again, no, which is why you'll need VBA (if it can handle merged cells). I have moved the thread and put out a call for help.
Great - Thank you so much
Hallo, I would like to ask some help please.
I have a spreadsheet - it also contains a Macro - that completes a Gantt chart with a country name and Time Zone. This is done by a very smart formula that I did not build myself but had lost of help with. I, now want to merge these cells so you can see the name of the country and UTC nicely.
Please see the workbook attached.
I was advised that this should be done in VBA...
Here's one way based on what was already there. Probably needs more work if you wanted to validate the dates or add the users etc, but it should give you a general idea.
Click the "refresh" button to update the gantt.
Code is as follows:
Please Login or Register to view this content.
Thank you so much for your reply. So I am at the very end om sheet - I only needed the data to merge when the formula populated it.
So, I came up with this:
AND IT WORKED!!Please Login or Register to view this content.
Thank you so much!
Last edited by AliGW; 05-28-2024 at 11:18 AM. Reason: Code tags added - please review the forum guidelines.
Thanks for the rep.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Good morning AliGW,
I was good, until I start making adjustments to my form. May I use this thread to ask your help on my original sheet or should I close this and post again?
Just continue here. Remove the SOLVED tag for now.
Great, Thank you.
There was a meeting yesterday with this and some new development to take place. I am first going to address this and then I will get back to you with my questions.
Thank you once again for all your help! This Tracker is getting smarter by the day.
Just so that you know, I shan't be able to help with the VBA myself (not my bag - hate it and rarely use it), but soemeone else will. Try to wait until you have all your ducks (questions) in a row so that nothing is moissing - there's nothing more frustrating for helpers than a steady drip-feed of requirements!!!
Great, will do.
No this is not on VBA, this is on the grant formula that you developed for me. I had to change the Input Form and, obviously, now the formula does not work.
Ok, Could you please help with this:
1. Fix the formula to pull the data from Table 1 to the GANTT on the Tracker sheet - So I have combined the Name and Surname to 1 cell - Also using Data Validation with a dropdown box. I also had to change the dropdown box for Travel / Leave, the formula refers to Training - That Training in the formula should now be Travel
2. I am unsure about the Conditional Formatting - I still want to see the 2 colours: Orange for Leave and Green for Travel. Maybe if the formula is fixed this will pan out correctly?
Just a note, the client wanted to see all the cells with borders around, thus I changed this in the Conditional Formatting.
I would really appreciate your help on this again. I am so bad in understanding the argument of this great formula that you have done
Please see the Draft 04 attached.
No, sorry.Fix the formula to pull the data from Table 1 to the GANTT on the Tracker sheet
We established several posts ago that the formula was not going to be able to bring through the data in the format you required. This is why the thread has been moved to the VBA section - you need VBA to populate the Gantt chart. Similarly with the CF.
I shall put out a call for help to the VBA expeerts.
Yes, that is correct, So I just want to populate the data the way that you have done in the beginning of this thread.
What I have done in the mean time is write a VBA script to select the GANTT chart input and merge the cells that have been complete.
So, I just need your original formula to populate the GANTT chart with the data from the DATA sheet in each row.
Is this making sense? We might not need VBA experts, just your fixed formula?
Thought I'd populated it in post #34?
That wasn't clear to me.What I have done in the mean time is write a VBA script to select the GANTT chart input and merge the cells that have been complete.
Formula:
=IFERROR(TRIM(SUBSTITUTE(LET(pf,FILTER(Table1,Table1[Name & Surname]=$A6),FILTER(INDEX(pf,,5)&" "&INDEX(pf,,2),(INDEX(pf,,3)<=B$5)*(INDEX(pf,,4)>=B$5))),"Training","")),"")
CF has been updated. I am somewhat perplexed that you were unable to make the necessary changes yourself, though. You need to take ownership of the formula now, so if you don't understand it, ask.
But I would like to use the method from the from the sheet that you created from post #26 - with the formula - it should only populate the data but should not apply any special formatting.
Would you then mind breaking this down into an explanation for me please?
=IFERROR(TRIM(SUBSTITUTE(LET(pf,FILTER(Table1,Table1[Name & Surname]=$A6),FILTER(INDEX(pf,,5)&" "&INDEX(pf,,2),(INDEX(pf,,3)<=B$5)*(INDEX(pf,,4)>=B$5))),"Training","")),"")
What I should say is, I understand what an IFERROR is, and a Trim Function. Also, I have had to do with the Substitute function, and little with INDEX, but I am unsure how the argument go.
Example: IF this, then do that, otherwise do this...
Does this make sense? I rather want to learn more on this.
OK.
pf is a variable:
pf = FILTER(Table1,Table1[Name & Surname]=$A6) (which filters the source table to rows matching A6).
FILTER(INDEX(pf,,5)&" "&INDEX(pf,,2),(INDEX(pf,,3)<=B$5)*(INDEX(pf,,4)>=B$5))
Concatenates columns 5 and 2 from pf where the fourth column is bigger or equal to B5 (this changes as the formula is copied across to look at the correct column, C, D, E etc.).
TRIM(SUBSTITUTE(...,"Training",""))
Substitutes the word training with a blank and then trims any leading or trailing spaces away.
IFERROR(...,"")
Replaces error messages with a blank.
Thank you so much.
It seems as if I keep on doing something wrong here. I had to replace the word Training with Travel. The trainer then select between Travel / Leave and Training is not in my data anymore. I have now tried to change "Training" to "Travel", but then my formula stops working. It then selects the first cell of the date and a date in the middle, but not to the end.
Is this because because the 5 of the argument is referring to column 5 of my data?
I have copied your formula to my sheet, changed "Training" to "Travel" and just fill it down for January. Please see attached.
My apology - please see attached.
What do you want to appear on the Gantt chart?
Give me ONE example of each category. NOT in the workbook, but here in a post. Add at least ONE of each to the source data.
Last edited by AliGW; 05-31-2024 at 08:07 AM. Reason: Typo fixed.
The trainer would travel to Argentian UTC -13 (Country of Travel) from 01/01 (Date From) to 10/1 (Date To) - Therefor the Country of Travel should fill the cells on the Gantt from 1 Jan to 10 Jan.
Each cell will of course be filled with this.
NO! That's NOT what I asked.
You have THREE categories - at the moment, you only have one represented in the sample data.
I need an example of each of the three and you need to TELL me how you want entries for each to appear on the Gantt chart (NOT where they should appear).
Please try to answer the questions I am asking.
I am so sorry, by the life of me, I am not sure what you are asking.
Categories? Travel and Leave - Not sure if this is correct??
Or do you mean: Travel Type, Dates and Country of Travel?
OK.
You said that you have three categores: Travel, Training and Leave.
At the moment, there are just THREE examples in the source data table, and they are all travel.
I asked how you want entries for each of the three to appear on the Gantt chart.
For example, do you want Travel to appear like this:
AliGW on MS365 Beta Channel (Windows 11) 64 bit
Sheet: Tracker
or like this:
AliGW on MS365 Beta Channel (Windows 11) 64 bit
Sheet: Tracker
Same question for Leave and Training. And what will be in the fifth column of the source data for these two? This is why I need examples. I can't work with what I don't have and can't see.
OK - I've made an executive decision for you. I've added some data to the source data table to test this and it's all set up in the attached.
Travel - shows country of travel and time zone - shaded green
Training - shows Training - shaded blue
Leave - shows Leave - shaded amber
I had to fix the conditional formatting again - it was shot to pieces!
Ok, the light went on for me!
I only have 2 Categories - Training changed to Travel.
So, it is TRAVEL or LEAVE.
I would like to see them like your second Image - Only the Country and UTC - Then TRAVEL can be GREEN filled with Conditional Formatting and LEAVE can be orange.
Ok, I see why the info is not sufficient, because my Table's heading in Column F say Country of Travel - but if you or on leave it should state Country of Leave. Where Column C in the table would say if you Travel are on Leave. I still need to see in What Country they are even if they are on leave - I should then change Column F to say Country only.
Is this beginning to form a picture?
OK - I'm going to have one last attempt.
There's a problem - without any reference to travel or leave on the Gantt chart, it's not possible to set up conditional formatting. Well, it is, but it would all have to be the same colour, which is not what you want.
The formula would be this:
=IFERROR(LET(pf,FILTER(Table1,Table1[Name & Surname]=$A6),FILTER(INDEX(pf,,5),(INDEX(pf,,3)<=B$5)*(INDEX(pf,,4)>=B$5))),"")
but I have already applied it so you don't have to.
Last edited by AliGW; 05-31-2024 at 09:01 AM.
Good morning! Thank you so much for your help on this.
What I have done - I the dropdown box for the country, I added "Leave" as the first option. We do not really need to know in what country the trainer is when on holiday, but mostly it will be in South Africa. The I used Conditional Formatting that should the text contains "Leave" it will colour the cell in orange:
Attachment 870914
I think this would work perfectly.
Thank you once again!
I am going to use your ideas on one of my own spreadsheets where I keep my hours for my clients!
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
I'm not sure if you're satisfied with the current solution yet, but I still want to present my solution. Regarding UTC, I searched online and filled in the list of country names and UTC.
When pressing the 'Submit' button, the data sheet will update, followed by the Tracker sheet updating accordingly, including merging the relevant areas
PHP Code:Option Explicit
Sub data()
Dim lr&
lr = Sheets("Data").Cells(Rows.Count, "B").End(xlUp).Row + 2
Sheets("Data").Activate
With Sheets("Form")
Cells(lr, "B").Value = .Range("C5").Value
Cells(lr, "C").Value = .Range("C7").Value
Cells(lr, "D").Value = .Range("C9").Value
Cells(lr, "E").Value = .Range("C11").Value
Cells(lr, "F").Value = .Range("C13").Value & " " & .Range("C17").Value
End With
tracker
End Sub
Sub tracker()
Dim lr&, i&, m&, rng, ce As Range
Dim fmD As Date, dayCount&, wf As Object
Set wf = WorksheetFunction
With Sheets("Data")
lr = .Cells(Rows.Count, "B").End(xlUp).Row
rng = .Range("B3:F" & lr).Value2
End With
Sheets("Tracker").Activate
lr = Cells(Rows.Count, "A").End(xlUp).Row
For Each ce In Range("A6:A" & lr)
If wf.CountIf(Sheets("Data").Columns(2), ce) Then
For i = 1 To UBound(rng)
If ce.Value = rng(i, 1) Then
fmD = rng(i, 3): dayCount = rng(i, 4) - rng(i, 3) + 1
m = Evaluate("=Match(" & CDbl(fmD) & ",B5:NC5, 0)")
With ce.Offset(0, m)
If .mergecells Then .mergecells = False
.Value = IIf(rng(i, 2) = "Travel", rng(i, 5), "Leave")
.Resize(1, dayCount).Merge
End With
End If
Next
End If
Next
End Sub
Quang PT
Thank you so much for this script. I am going to have to spend some time and test this.
I have another question on VBA coding:
I created a Ribbon for the Administrator to conduct some functions in this workbook.
What VBA script can I use to write a macro that will go to a specific sheet? I have buttons in the worksheet with hyperlinks to do this, but now I want a button in my Ribbon to go to a sheet and that will run then in a Macro.
This macro should be able to go from any sheet to say my Raw sheet.
The new question requires a new thread with a suitable title in the VBA section.
This thread should have been marked as solved already.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks