I am storing my data as a list on a separate worksheet and want to be able to
view and change that data from another worksheet. The Data Form function
would work, but I have too many fields and I want to be able to control the
options entered through data validation. I can show the data as is on a
separate worksheet using vlookup functions as each row has a unique
identifier number as the first entry. Now I want to be able to copy the
changed information and then paste it over the correct row in the data list.
Any ideas?

You can get the offset into your list with

Dim rng as Range, rng1 as Range
set rng = Your list
' assume unique value is in the first column
res = application.Match(uniquevalue,rng.columns(1),0)
if not iserror(res) then
set rng1 = rng(res)
' rng1 is now the cell in the list with the unique identifier

End if

--
Regards,
Tom Ogilvy

"tom at arundel" wrote in message
...
I am storing my data as a list on a separate worksheet and want to be able
to
view and change that data from another worksheet. The Data Form function
would work, but I have too many fields and I want to be able to control
the
options entered through data validation. I can show the data as is on a
separate worksheet using vlookup functions as each row has a unique
identifier number as the first entry. Now I want to be able to copy the
changed information and then paste it over the correct row in the data
list.
Any ideas?

typo
change
set rng1 = rng(res)
to
set rng1 = rng.columns(1).cells(res)

or [simpler]

set rng1 = rng.cells(res,1)
--
Regards.
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
You can get the offset into your list with

Dim rng as Range, rng1 as Range
set rng = Your list
' assume unique value is in the first column
res = application.Match(uniquevalue,rng.columns(1),0)
if not iserror(res) then
set rng1 = rng(res)
' rng1 is now the cell in the list with the unique identifier

End if

--
Regards,
Tom Ogilvy

"tom at arundel" wrote in message
...
I am storing my data as a list on a separate worksheet and want to be
able
to
view and change that data from another worksheet. The Data Form
function
would work, but I have too many fields and I want to be able to control
the
options entered through data validation. I can show the data as is on a
separate worksheet using vlookup functions as each row has a unique
identifier number as the first entry. Now I want to be able to copy the
changed information and then paste it over the correct row in the data
list.
Any ideas?

Thanks Tom. I will try this -- but since I am early in learning VBS, success
will probably depend a lot more on me than on the quality of your solution!

"Tom Ogilvy" wrote:

typo
change
set rng1 = rng(res)
to
set rng1 = rng.columns(1).cells(res)

or [simpler]

set rng1 = rng.cells(res,1)
--
Regards.
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
You can get the offset into your list with

Dim rng as Range, rng1 as Range
set rng = Your list
' assume unique value is in the first column
res = application.Match(uniquevalue,rng.columns(1),0)
if not iserror(res) then
set rng1 = rng(res)
' rng1 is now the cell in the list with the unique identifier

End if

--
Regards,
Tom Ogilvy

"tom at arundel" wrote in message
...
I am storing my data as a list on a separate worksheet and want to be
able
to
view and change that data from another worksheet. The Data Form
function
would work, but I have too many fields and I want to be able to control
the
options entered through data validation. I can show the data as is on a
separate worksheet using vlookup functions as each row has a unique
identifier number as the first entry. Now I want to be able to copy the
changed information and then paste it over the correct row in the data
list.
Any ideas?

Post your code if you run into troubles.

--
Regards,
Tom Ogilvy

"tom at arundel" wrote in message
...
Thanks Tom. I will try this -- but since I am early in learning VBS,
success
will probably depend a lot more on me than on the quality of your
solution!

"Tom Ogilvy" wrote:

typo
change
set rng1 = rng(res)
to
set rng1 = rng.columns(1).cells(res)

or [simpler]

set rng1 = rng.cells(res,1)
--
Regards.
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
You can get the offset into your list with

Dim rng as Range, rng1 as Range
set rng = Your list
' assume unique value is in the first column
res = application.Match(uniquevalue,rng.columns(1),0)
if not iserror(res) then
set rng1 = rng(res)
' rng1 is now the cell in the list with the unique identifier

End if

--
Regards,
Tom Ogilvy

"tom at arundel" wrote in
message
...
I am storing my data as a list on a separate worksheet and want to
be
able
to
view and change that data from another worksheet. The Data Form
function
would work, but I have too many fields and I want to be able to
control
the
options entered through data validation. I can show the data as is
on a
separate worksheet using vlookup functions as each row has a unique
identifier number as the first entry. Now I want to be able to copy
the
changed information and then paste it over the correct row in the
data
list.
Any ideas?