Lookup and Enter Value to Other Tab on Change in Cell

Joined
Mar 25, 2010
Messages
1
Reaction score
0
I've been trying to piece this one together for a while from various posts on this and other boards but have not been able to get all the pieces to work together.

Attached you will find a dummied up version of what I am working with (names/numbers have been changed to protect the innocent). You will first find a tab (Range Data) that contains a table of policy information that is looked up and used in the second tab (Ee Data).


The Ee Data tab consists of a table of information, organized by employee with each row containing data for one employee. I want to use this table as the primary source for all of the information that is then displayed on the remaining tabs, each named for the employ whose data is displayed on the tab. This is to provide the management reviewing the information with an easy to read snapshot of what the data and decisions made on the Ee Data sheet mean for each employee.

Each of the employee tabs have one cell (B3) that references the employee's name on the data sheet (e.g. on the first employee tab the cell B3 refers to A2, the name field in the first row of data on the data tab, B3 on the second employee tab refers to A3, and so on). All other data displayed on the employee tab comes from doing a vlookup() of B3 against the table on Ee Data.

I need the field (D5) on the employee tab to update the cell that it is pulling lookup data from when changed (and then changed back to a lookup) and think that the way to do it is to use the Worksheet_Change event on that cell.

In an earlier iteration I had used the Indirect() function on the Ee Data tab to take the value from the employee tabs, but then I realized that due to the references in B3 to a static row (and the resulting vlookups), if a change was made on an Ee Data tab and then that data was sorted, the data entered/updated on the employee tab would likely now be matching to a different employee who is now on that row of the data (there are sheets with up to 60 individuals, so in practical use, sorting will happen and this problem will occur).

What I would like the code to do is:

1) when cell D5 on an employee tab is changed, lookup the cell that D5 was displaying previously (the lookup using B3 against the array of data [the data being returned is in column AE] on the data tab) and update the previous value on the data tab to the new value in D5 of the employee tab (I've been trying to use the Match and vlookup functions to no avail)


then

2) insert the vlookup formula back into D5 that was there previously

3) have code that prevents the insertion of the formula back into D5 from triggering the Worksheet_Change event all over again.

This would mean that the user could update this piece of data either on the data tab or on the employee tab with the result being the actual data continuing to be stored on the data tab and reflected on the employee tab, without the user having to worry about the data on any tab becoming static instead of referring to the data tab - this becomes important particularly when people sort the data tab.

This all seems great in theory, but can it work? If it can, my meager VBA programming experience isn’t getting it done… :(

If this is possible, it is something I know I could have used in a number of situations in the past and surely many in the future! I'm just hoping that someone much smarter than me w/VBA can understand my ramblings & point me in the right direction (as I said, I've actually spent a good deal of time trying to get it, but as a dabbler in VBA, I haven't been able to piece together the whole process)...

Please help!!
 

Attachments

  • Test for Post.zip
    51.2 KB · Views: 79

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top