Create dynamic cells that change to the data from a table, but also allow for writing new data

[CarmelloExcel]

I am having a hard time figuring out the best way to achieve the following. I tried to use AI, but it wasn’t quite understanding. I have a sheet called ‘Planned’ and a sheet called ‘DataStorage’.

In sheet ‘Planned’, there is a drop-down menu with a list of names in cell C2. In cells A3 through A12 are a list of actions. I want to be able to enter data in cells C3 through C12 (underneath the Dropdown) for each action. When I change the dropdown to a new name, I want to be able to enter new data for that person. If I go back to the first person in the drop-down, it should retain the data I previously entered for them.

All the data I’m entering for each person in the drop-down should be getting stored in a table in the ‘DataStorage’ sheet. I’m going to use this table to create calculations.

Here’s another way to think of it. Imagine if I had two sheets of grid paper representing my Excel sheets. On sheet ‘Planned’, take a pair of scissors and cut out cells C3 through C12. Now take the data table from the ‘DataStorage’ sheet and put it behind that cut out. When I change the dropdown value on sheet ‘Planned’, the table behind the cut out, should slide to the corresponding value of the name from the dropdown.





I need to be able to write in the cells C3 through C12, and whatever I write needs to be copied to the table. It’s essentially like creating a small window on sheet ‘Planned’ to display 1 column of data from the table in ‘Data Storage’ based on the dropdown value. Does this make sense? Thanks so much!

 

[offthelip]

I don't have time to write the code for you at the moment but this can achieved using the Worksheet change event. You need two routines,
the first copies all the data from your Datastorage worksheet relating to the name in C2 everytime the cell C2 on "Planned" changes, using Index, match or much better a variant array VBA lookup.
the second copies data for the cells that has changed if it is in the range C3 to C12 on "Planned" to the place on "Datastorage" which matches the name in C2 on "planned, once again index, match or a vba lookup to find where to write the data.
Both of these routines would fast fast and fairly simple

 

[CarmelloExcel]

I will give this a try, thanks!