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.)
I'm trying to get a date entered into A1 on Tab 1 be automatically entered into A1 Tab 2. I can get it to automatically fill in the date in Tab 2 but if I change the position of A1 in Tab 1 (sorting) the data in A1 Tab 2 changes to the data which is now in A1 Tab 1.
I've tried all the configurations of cell references both absolute, relative or mixed.
I can't seem to get this figured out. Thanks for your help.
In Excel Sheet1!A1 is a fixed address. You can refer to that address in Sheet2!A1 and you will see in Sheet2!A1 whatever is currently in Sheet1!A1 - as you have found out. I am repeating the principle because you can use it to your advantage.
In this case, however, you don't want the value in Sheet1!A1. Instead, there is a value elsewhere in Rows(1) to which the date is related. You wish to preserve this relationship, and that gives you a clue to the answer.
Say, you have a date and and order number. What you want tin Sheet2 is the date that does with the order number, regardless of where it appears in Sheet1. To achieve this you need a unique value (such as an order number) which appears in both sheets. You can then use a number of options to retrieve that date by looking for the order number.
The most commonly used one would be VLOOKUP() but that would require that the lookup value (the order number) be to the left of the date which wouldn't be possible if the date is in Columns("A"). You might look at the Index/Match combination if the arrangement on your sheet is really unsuitable for VLOOKUP().
