Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Automatically change referenced cell on another worksheet

0

Hello,

I am having trouble with getting my macro to do what i want. I want it to lookup the data from another worksheet using the date has a reference, when the data is retrieved from the other worksheets and brought to the schedule sheet i want to able to change data in the cells and it automatically updates in the worksheet it was retrieved from.

if it makes sense.

Answer
Discuss

Answers

0

It does make sense - but not enough.

Depending upon what you want to look up and where, you would probably use VBA's Find function to retrieve the data. Basically, you would replicate VLOOKUP, looking for the date in one column and returning the value from another column in the same row (works for HLOOKUP in analog fashion). You wouldn't use VLOOKUP because you need to replicate the search when you want to update the cell in reverse.

 Find returns a range, in this case a single cell, the value of which you can read and use or set. Use the worksheet's Change event to trigger the update.

In the attached workbook I have reviewed your code and formatted it for better readability. I didn't test it, nor checked its logic but I corrected and improved the syntax and added some comments for your reading pleasure.

If you wish to pursue your original question in this thread please don't explain what you want in any comments - edit your question so that it provides the required information. My feeling is that you should first try to implement the answer I provided here and post any questions that probably arise in a new thread when you come to them.

Discuss

Discussion

Thank you for having a look at this for me, its very much appericiated, what im wanting is a rota type database, that stores the data i enter on the schedule sheet into sheet 2019, 2020 ect. under the correct date. i then want to be able to retreive the data by using the arrorws in the schedule just incase i need to change it.

Thankyou again. 
slevo79 Dec 17, '19 at 1:12 pm
Correspondence in this discussion format will not get us anywhere. Please re-read the last paragraph of my above answer.
IMHO there is a fundamental flaw in your project design. It's in the data capture. I can't see what your data are but they seem to be like Date / Time / Value or perhaps Date / Location / Value. Looking at your 2019 sheet, the date is in A1, time (location) in A2:Axxx and Value in B2:Bxxx. These data should be captured in a dedicated sheet with just 3 columns. It follows that your sheets 2019, 2020 etc. are, in fact, reports which you can extract from the data. Make one template. Enter the year at the top, and the data are filled in. Likewise, the Schedule sheet should be compiled from the data, not from the annual reports.
Data should be correct and not ever be modified, and most certainly not by the user. Given that you may have reasons I don't know about, please consider that data flows from the DB to the reports. Obviously, reversing the process is a different task. I suggest you don't attempt to deal with it in the same breath as creating your reports.
Variatus (rep: 4889) Dec 17, '19 at 10:28 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login