Excel dynamically fill cell with data

Solved/Closed
liton - Mar 8, 2012 at 08:54 PM
 liton - Mar 9, 2012 at 08:03 AM
Hello,

I have worksheet ( AllUsers ) with two column A ( user id ) and B ( date ). Column A is a long list of user names and B is a list of dates that are related to each user and being changed everyday. I need to create another worksheet with a column A ( user id ) and B ( date ) and have the date dynamically appear if an existing user id is typed in a cell in column A. Is it achievable? Thanks in advance and please let me apologize if this is not the right place to ask this kind of questions.

Regards,
Liton

5 replies

venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Mar 8, 2012 at 09:43 PM
sample data sheet1

name daate
a 1-Jan-12
s 2-Jan-12
d 3-Jan-12
f 4-Jan-12

right click tab o f shseet 2 and click view code and paste this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Target.offet(0, 1).FormulaR1C1 = "=TEXT(VLOOKUP(RC[-1],Sheet1!R[-1]C[-1]:R[98]C,2,0),""dd-mmm-yyyy"")"
Application.EnableEvents = True
End Sub


save the fiel
enter any name in col A of sheet 2 and see what happens
0
venkat1926,

Thanks a lot for your reply. I am not sure if I am doing it wrong but, I cannot get this to work. Is there possibly a way to share the file I created with your code so you can have a look, if you have time. Sorry for asking so much. Thanks again.

All the best,
Liton
0
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Mar 9, 2012 at 03:43 AM
upload your file (not necessarily all the row, extract is enough)
to 3rd part;y like speedysahre.com and post the web page for downloading
explain again what you want.
0
Here is the link to the file: http://speedy.sh/zekeZ/New-Microsoft-Office-Excel-Worksheet.xlsm or [code]http://speedy.sh/zekeZ/New-Microsoft-Office-Excel-Worksheet.xlsm/code

On worksheet1 I have a database of users ( a,s,d,f and so on ) on column A and each user has a corresponding date on next cell which is column B. On the worksheet2 I want to type in a user for example "f" and have the corresponding date to appear automatically on the next cell which should be "4-jan-2012".
0

Didn't find the answer you are looking for?

Ask a question
venkat1926
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
805
Mar 9, 2012 at 06:15 AM
small spelling mistake. did you copy paste the macro or typed it
anyhow does not matter
remove the old macro and replace it with (right click sheet 2 tab and click view code
remove old code
replace with this


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 1).FormulaR1C1 = "=TEXT(VLOOKUP(RC[-1],Sheet1!R[-1]C[-1]:R[98]C,2,0),""dd-mmm-yyyy"")"
Application.EnableEvents = True
End Sub
0
Thank you!! Now it's working!
0