I am using Excel on a Mac and I can insert VBA code through the developer tab.
The purpose of this spreadsheet is to have a visual of sites allocated & available on a campground and will be used for an annual camping event for our organisation.
I have two sheets in my workbook (See attached images being screen shots of the worksheets)
Sheet1 has data in two columns. Column A is 'Site Number' Column B is 'Camper Name'
Sheet 2 has a site map layout so for example Row 4 shows sites 1 to 13 with 1 site allocated per column eg A4 = 1. (site 1), M4=13 (Site 13)
I currently have conditional formatting on Sheet 2 on the site numbers which does a lookup and if there is a name beside the site number from sheet 1 it will colour the cell red (meaning not available) but if the camper name from sheet 1 is blank it colours the cell green (meaning available). The conditional formatting is all good and working well. I created the conditional formatting using an xlookup function.
What I want to be able to do now is for a comment to be automatically added to the corresponding site number cell on sheet 2. That comment data comes from a lookup from sheet 1. The comment is to be the camper name, which is column B sheet 1. Of course if that camper name on Sheet 1 changes or is deleted the comment will need to update or be deleted accordingly.
Example, if on sheet 1, site 10 has the camper name 'John' then I want a comment added to site 10 on sheet 2. When I select site 10 in sheet 2 I want a comment box to pop up that says John. If there is no camper associated with site 10 I dont want the comment box created.
It should be noted that this is just a sample set of data it could well be that we are dealing with 500 sites or more all up, meaning on sheet 1 columns A & B could run down to row 500 or more.
Can this be done via VBA ?
Look forward to any help you can provide.
With thanks
The purpose of this spreadsheet is to have a visual of sites allocated & available on a campground and will be used for an annual camping event for our organisation.
I have two sheets in my workbook (See attached images being screen shots of the worksheets)
Sheet1 has data in two columns. Column A is 'Site Number' Column B is 'Camper Name'
Sheet 2 has a site map layout so for example Row 4 shows sites 1 to 13 with 1 site allocated per column eg A4 = 1. (site 1), M4=13 (Site 13)
I currently have conditional formatting on Sheet 2 on the site numbers which does a lookup and if there is a name beside the site number from sheet 1 it will colour the cell red (meaning not available) but if the camper name from sheet 1 is blank it colours the cell green (meaning available). The conditional formatting is all good and working well. I created the conditional formatting using an xlookup function.
What I want to be able to do now is for a comment to be automatically added to the corresponding site number cell on sheet 2. That comment data comes from a lookup from sheet 1. The comment is to be the camper name, which is column B sheet 1. Of course if that camper name on Sheet 1 changes or is deleted the comment will need to update or be deleted accordingly.
Example, if on sheet 1, site 10 has the camper name 'John' then I want a comment added to site 10 on sheet 2. When I select site 10 in sheet 2 I want a comment box to pop up that says John. If there is no camper associated with site 10 I dont want the comment box created.
It should be noted that this is just a sample set of data it could well be that we are dealing with 500 sites or more all up, meaning on sheet 1 columns A & B could run down to row 500 or more.
Can this be done via VBA ?
Look forward to any help you can provide.
With thanks