Excel dynamically fill cell with data Solved/Closed

Question

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

Configuration: Windows 7 / Opera 9.80

venkat1926 · Answer

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

liton · Answer

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

venkat1926 · Answer

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.

liton · Answer

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".

venkat1926 · Answer

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

Excel dynamically fill cell with data

5 responses

Similar discussions

Subscribe To Our Newsletter!