Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create a summary page that will take a list and remove empty rows | Excel Discussion (Misc queries) | |||
Create Summary List Page | Excel Discussion (Misc queries) | |||
Excel formula to change page # when data entered in other page | Excel Discussion (Misc queries) | |||
Want to Create a List in Excel 2002; Don't see List in Data Menu? | Excel Discussion (Misc queries) | |||
Does Excel 2002 have a List>Create List option under Data? | Excel Discussion (Misc queries) |