ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Create way to change data in list from another page (https://www.excelbanter.com/excel-programming/325963-create-way-change-data-list-another-page.html)

tom at arundel

Create way to change data in list from another page
 
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?

Tom Ogilvy

Create way to change data in list from another page
 
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?




Tom Ogilvy

Create way to change data in list from another page
 
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?






tom at arundel

Create way to change data in list from another page
 
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?







Tom Ogilvy

Create way to change data in list from another page
 
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?









All times are GMT +1. The time now is 03:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com