I have a spreadsheet that is used to enter jobs. There is a calendar sheet that shows the dates the jobs are scheduled, then there is a sheet that lists all the jobs horizontally (columns) and lets you assign team members to the jobs. When the code is run to insert a new job it adds it to the calendar in the respective date, then adds it to the first available column on the team sheet, and it creates a hyperlink on the calendar to the teams sheet for quicker finding of the job to assign people to it. I have another button that will delete a job if it is canceled or was entered incorrectly.
The issue I am having is that when a job is deleted, the corresponding column on the teams sheet is deleted and all columns are shifted to fill in the blank spot. When this happens, all hyperlinks that were pointing to columns to the right of the deleted job are still pointing 2their originally assigned columns and not their associated job's new location. Is there a way to make them more dynamic? Here is the formulas and vba to create the hyperlinks in the first place
In a round about way on my calendar sheet cell P17 gets the column letter of the newly added job to the teams sheet. O1 has the name of the associated teams sheet for the specific calendar year.
P18 =concatenate("'",$O$1,"'!",$P$17,"$4:",$P$17,"$11")
Which gives the result 'FY22 Teams'!$D$4:$D$11
The vba I use to insert the hyperlink is
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Range("P18").Value, TextToDisplay:=("S21").Value
Where S21 contains the job name.
Everything works great until I delete a column on the teams sheet, hense why I was hoping to make this a little more dynamic. Thanks for the help!
The issue I am having is that when a job is deleted, the corresponding column on the teams sheet is deleted and all columns are shifted to fill in the blank spot. When this happens, all hyperlinks that were pointing to columns to the right of the deleted job are still pointing 2their originally assigned columns and not their associated job's new location. Is there a way to make them more dynamic? Here is the formulas and vba to create the hyperlinks in the first place
In a round about way on my calendar sheet cell P17 gets the column letter of the newly added job to the teams sheet. O1 has the name of the associated teams sheet for the specific calendar year.
P18 =concatenate("'",$O$1,"'!",$P$17,"$4:",$P$17,"$11")
Which gives the result 'FY22 Teams'!$D$4:$D$11
The vba I use to insert the hyperlink is
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Range("P18").Value, TextToDisplay:=("S21").Value
Where S21 contains the job name.
Everything works great until I delete a column on the teams sheet, hense why I was hoping to make this a little more dynamic. Thanks for the help!
