fill a form with specific data

[Carla]

This is difficult to ask...
I want to create a form in excel on one tab with a spreadsheet of data in
another tab. On form tab I would like to simply type in the line # for which
I would like the data to populate from the data tab.

Form tab:
A1 type 1 (to retrieve data from line 1 on data tab)
A2 return Column A of Row 1 from data tab
A3 return Column B of Row 1 from data tab
A4 return Column C of Row 1 from data tab

Thank you all you wonderful forum helpers out there!
Carla

 

[FSt1]

hi
this might work for you. it is worksheet code. right click the sheet tab
then click view code. paste the below code into the code window.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range
Dim ro As Range
Dim n As Long
n = Range("A1").Value
Set r = Sheets("Form").Range("B1")
Set ro = Sheets("Data").Range("A" & n)
If Intersect(Target, r) Is Nothing Then
r.Value = ro.Value
r.Offset(0, 1).Value = ro.Offset(0, 1).Value
r.Offset(0, 2).Value = ro.Offset(0, 2).Value
End If
End Sub

regards
FSt1

 

[Carla]

So, with this code, can I then type in 2 and will the form update with the
info from Row 2 of the data sheet? So, I want to be able to type in any row
number and the form tab return that data rows info.

Thank you,
Carla

 

[FSt1]

hi
yes type in any row number in A1 and B1,C1 and D1 will populate the data
from the other sheet.

Regards
FSt1

 

[zvkmpw]

I want to create a form in excel on one tab with a spreadsheet of data in

another tab.  On form tab I would like to simply type in the line # forwhich
I would like the data to populate from the data tab.

Form tab:
A1 type 1 (to retrieve data from line 1 on data tab)
A2 return Column A of Row 1 from data tab
A3 return Column B of Row 1 from data tab
A4 return Column C of Row 1 from data tab

One way is to put
=OFFSET(data!$A$1,$A$1-1,ROW()-2)
in form!A2 and copy down as far as needed.

You might want to add some checking in case something strange is
entered in form!A1.

 

[Carla]

That works perfectly for a clean form. If I were to have fields in various
places on the form tab (not in a nice clean column down), can that still be
achieved with a nice formula?

Thank you,
Carla

 

[Carla]

So, now I add the question...Can I put fields in various places on the form
or do they have to stay in that nice neat column?

Thank you,
Carla

 

[zvkmpw]

That works perfectly for a clean form. If I were to have fields in various

places on the form tab (not in a nice clean column down), can that still be
achieved with a nice formula?

What determines what the various places are? An example would help.

If the places are predictable, one possibility is to try variations of
this:
=INDIRECT("data!"&CHOOSE($A$1,"B3","E5","A12"))