Excel VBA to update one cell based on a value of another cell

In my attached Excel file on worksheet named "Main" there is a table where numeric values are keyed in.

In worksheet named "Series1", you'll see another table that has values that populate through a VLOOKUP function that assesses the table on worksheet "Main" to return the correct value in "Series1!F7" for example.

As it stands write now, updates entered in Main will be reflected on Series1 by means of the vlookup.  However, updates entered in Series1!F17 will NOT be reflected in Main!G19 for example (this is obvious, because there is no formula linking them together).  

What I would like to do is, when a value is entered in a cell within the table on Series1 worksheet, I want that value to populate in the corresponding cell in the table on Main worksheet.  Then after it populates the correct cell in Main, the cell in Series1 will return to its original state (note, its original state contained a VLOOKUP formula that was obviously over written when a new value was entered).  

I think this question should be worth 1,000 points, but unfortunately 500 is the max.  Thanks for any helpful feedback.  =)

UpdateExample.xls
KP_SoCalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello KP Socal,

see what you can do with the attached. It does what you describe above.

cheers, teylyn
Q-25632767-UpdateExample.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KP_SoCalAuthor Commented:
Wow, thanks!  This looks like it is going to accomplish what I need.  I'll test it out tonight and hopefully be able to re-post my results tonight, too.  One question first, what's "COLUMN(B1),FALSE)"?  Just curious.  Thanks again, you have been a big help to me!!! =)
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
In your original sheet you had four different Vlookup formulas. The only difference was which column they return. Using Column(B1), which returns a 2, the Column reference will change from B1 to C1 to D1 to E1, resulting in 2 , 3, 4, 5, when copied to the right, so I don't have to worry about four different Vlookup formulas.

It's a handy technique of incrementing numbers when formulas are copied across instead of down.

cheers, teylyn
0
KP_SoCalAuthor Commented:
Outstanding, thanks!
0
KP_SoCalAuthor Commented:
Thanks for the great solution to this.  I was able to tailor most of it to my needs.  I created another post hinging around the "Sheets("Series1").Select" segment of the code.  Thanks again! :-)

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25634447.html?fromWizard=true
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.