Linking comment cells to regularly changing info

[Dan5977]

Hi all,

I'm really after ideas if anyone could be so kind as to point me in the right direction. This is proving to be rather complicated....at least for me.

I have a spreadsheet where I am pulling live info from our CRM (a bespoke SQL database) using GetData. This populates a table called 'tblJob' in a tab called 'tblJob'.

I then summarise and sort that data on a tab called 'Order Status'. The data in this tab is not a table, it is formula driven and reading from 'tblJob'.

Our production team need to be able to add comments against each job. 4 different departments will each need a field. So I have the job no in column B on 'Order Status' and I have currently also repeated this in column R, so that I can put comments adjacent to it in columns S-V.

The problem is that the comments aren't in any way linked to those job numbers, so when the sheet refreshes, they will not be against the correct job numbers. Also, when jobs are completed, they drop off the table and the comments should also disappear with them.

What clever ideas do you have for linking comments to a job no so that they will stay alongside if the number still exists or disappear if it doesn't?

I have tried a 'CommentStorage' tab and VBA but have been unable to get it to work. I have also tried adding it to a scripting dictionary. Is there an easier way or any suggestions on how I might get this to work?

I appreciate this is rather vague but I'm really open to ideas at this stage.

Many thanks.

 

[Dan5977]

Anyone? I forgot to say I'm using Office365.

 

[bobsan42]

A couple of ideas:
Simplest is to store comments separately on a sheet/table only connected to the job number and department. Can be done without code but will include some usage inconveniences and is prone to mistakes.
Correct way is to actually save this comments into the database. Why would you have to go through excel if you have a CRM system? Isn't it possible to be adapted to your needs

You can use VBA to do the following:

• on worksheet_change event - if a comment is entered get all comment changes / new comments and edit/store them in a separate comments table, saving also department and job number
• next time the joblist is refreshed - take all comments from the comments table and put them against the correct jobid/department
• however, comments are a temporary thing - the idea is for them to be resolved, so the idea may get a bit more complicated if we go through this in a very detailed way ...

 

[Dan5977]

A couple of ideas:
Simplest is to store comments separately on a sheet/table only connected to the job number and department. Can be done without code but will include some usage inconveniences and is prone to mistakes.
Correct way is to actually save this comments into the database. Why would you have to go through excel if you have a CRM system? Isn't it possible to be adapted to your needs

You can use VBA to do the following:

• on worksheet_change event - if a comment is entered get all comment changes / new comments and edit/store them in a separate comments table, saving also department and job number
• next time the joblist is refreshed - take all comments from the comments table and put them against the correct jobid/department
• however, comments are a temporary thing - the idea is for them to be resolved, so the idea may get a bit more complicated if we go through this in a very detailed way ...

I just wanted to thank you for responding.

There are various reasons for not adding this functionality into the CRM.

I am pleased to say I finally have the system working as intended. It took me way longer than I expected but it's functioning correctly now.

I took your advice to store the comments in a separate table. They were previously just in a worksheet. My functions required a lot of work to get working correctly but all worth it in the end.