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