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

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" wrote:

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

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" wrote:

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" wrote:

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

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

Regards
FSt1

"Carla" wrote:

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" wrote:

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" wrote:

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

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

"FSt1" wrote:

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

Regards
FSt1

"Carla" wrote:

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" wrote:

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" wrote:

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

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

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.

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


"zvkmpw" wrote:

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

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

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