Dynamic Spreadsheet - Sales IDs

[ZackG]

Hey everyone,

Help would be much appreciated!

I am trying to "stick" a column of comments section to their ID sales number. Every month, Sales Reps add comments to an excel spreadsheet. When the month is over, more ID sales numbers are pasted into this spreadsheet. This is where the problem occurs. The comments stay in their place, while the ID Sales numbers are moved around, since more are added (and the ID #s are sorted alphabetically.) Basically, I am trying to give the "Comments" column the ability to "stick" to their own unique ID sales number.

Here is the exact process that takes place:
1. Data comes in (put in "Data" Sheet)
2. Data is then calculated on ETC sheet
3. Salesmen input comment on these calculations on the ETC sheet
4. New data comes in
5. New data in copy/pasted over "Data" sheet
6. New data is calculated in the ETC sheet
7. Salesmen input their comments in the updated ETC sheet and can also change their previous comments

I give an explanation as well in the excel attachment.

Thanks in advance for all the help!!!!


Zack

[brokenbiscuits]

Hi Zack

I think the best approach would be to keep the BAAN number in column A static, using VLOOKUP to pull back the rest of the data in the row - e.g B213=VLOOKUP(A213,Data!A:B,2,FALSE)

This would then keep the information all in the correct row when new data is pasted over the old data, and thus the correct comments would be attached.

You would then need a procedure to capture any new items, so you can add them to the ETC sheet and pull down the formulae to pick up the data. This could either be done manually typing a formula in col Y of the Data sheet - something along the lines of =IF(ISERROR(MATCH(A2,ETC!A:A,0)),"x","") in cell Y2 and dragged down, which would highlight any new items with an X - or you could use a macro to do this if you feel the process is getting too manual.

You would then need to resort the IDs alphabetically (again, this could be covered within a macro).

Sorry if it seems a little long winded, but like I say with the help of a macro it can all be done automatically. Currently it's impossible for the comments to 'attach' to the data because there is nothing static in the ETC tab.

[ZackG]

Hi brokenbiscuits,

Thanks a bunch for the help

I find that this method is a bit too manual and isn't the best way to go...

Would you be able (or someone else) to come up with a macro that just does this automatically?

Once again, thanks so much!


Zack

[ZackG]

Can anyone help out please???