Hello, I hope someone can help me with this problem: (example worksheet link below)
https://1drv.ms/x/s!AsKdy7Nfg_FbggvKMtsK7R5Dvp2V
I have two sheets, Sheet1 is filled by hand, and the data from Sheet1 is used to form an automatic summary report on Sheet2. Each row in Sheet1 and Sheet2 accounts for a specific contract, and columns account for information about that contract, such as Date, Object, Amount, Buyer, etc. Sheet2 has columns A-G, where cells are filled with formulas, so as data is changed in Sheet1, cells in these columns are updated. Column H is called "Comments" and data is only entered by hand. I have set up columns A-G on Sheet2 so that it lists only contracts based on criteria, and in a specific order. (This order (sorting formula and the sorted list) is in Sheet1.)
So, when I change information on Sheet1 and Sheet2 updates, the order (position) of contracts on Sheet2 (columns A-G) changes, whereas the order (position) of comments in column H stays the same. What i need is for the position of comments in column H to change according to the position of contracts in columns A-G.
I'll give you an example:
________My initial report____________After data is changed in Sheet 1_____What I want it to look like after data change
___A-G_____________H_______________A-G____________H__________________A-G__________H
Contract_1_____Comment_1-------->___Contract_3____Comment_1--------->___Contract_3___Comment_3
Contract_2_____Comment_2-------->___Contract_1____Comment_2--------->___Contract_1___Comment_1
Contract_3_____Comment_3-------->___Contract_4____Comment_3--------->___Contract_4___Comment_4
I should mention, that the number of contracts in the report on Sheet2 changes, so data range is dynamic. That said, there is never more than 100 contracts in the report on Sheet2.
I guess it is not possible to do without VBA, but a solution involving pressing a button every time to update the order would not suffice. It want the macro to update the position of cells in Column H each time data is changed (assuming it is not too time consuming).
If anyone could help me solve this problem i would really appreciate it, it would also help me with similar problems i have with static vs dynamic cells. Thank you!
Bookmarks