Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation vs VLOOKUP - Linking to data in a seperate file
Hi,
I need a solution to the following problem. I have a template for each member of staff [e.g John Doe Time Tracking.xls] (for about 50 people) to track time spent on particular activities (projects) and which company they are employed by. Because the lists of Companies, Projects and EMployment Status may change from time to time, I would like to maintain this in a centralised spreadsheet rather than in each of the 50 worksheets that each member of staff uses. So on every template, I would like the following: Cell: $F$5 to select from Range A1:A6 in Lookup.xls'Organisation' Cell: $F$6 to select from Range A1:A3 in Lookup.xls'Employment Status' Cells: $L5:$L100 to select from Range A1:A12 in Lookup.xls'Projects' Is there a simple way of doing this so that the variables can be held and maintained in a seperate excel file? Regards Sharon |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation vs VLOOKUP - Linking to data in a seperate file
Hi
The easiest way to do this: 1. In every user file, add a sheet Links; 2. On sheet links, design 3 cell ranges, where cells are linked with cells in Lookup.xls tables Organisation, Employment Status, and Projects; P.e. Links!A1=IF('C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Organisation'!A1="","",'C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Organisation'!A1) , and copy down into range A1:A6 Links!D1=IF('C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Employment Status'!A1="","",'C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Employment Status'!A1) , and copy down into range D1:D3 Links!G1=IF('C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Projects'!A1="","",'C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Projects'!A1) , and copy down into range A1:A12 3. Define ranges Links!A1:A6, Links!D1:D6, and Links!G1:G12 an named ranges Organisation, Emploiment, and Projects respectively; 4. In your Data validation lists, refer to those named ranges; 5. Hide the sheet Links (there is no reason the user must have direct access to it). -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Sharon" wrote in message ... Hi, I need a solution to the following problem. I have a template for each member of staff [e.g John Doe Time Tracking.xls] (for about 50 people) to track time spent on particular activities (projects) and which company they are employed by. Because the lists of Companies, Projects and EMployment Status may change from time to time, I would like to maintain this in a centralised spreadsheet rather than in each of the 50 worksheets that each member of staff uses. So on every template, I would like the following: Cell: $F$5 to select from Range A1:A6 in Lookup.xls'Organisation' Cell: $F$6 to select from Range A1:A3 in Lookup.xls'Employment Status' Cells: $L5:$L100 to select from Range A1:A12 in Lookup.xls'Projects' Is there a simple way of doing this so that the variables can be held and maintained in a seperate excel file? Regards Sharon |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation vs VLOOKUP - Linking to data in a seperate fil
Hi Arvi,
thanks so much for the response.. I knew there was a simple solution... Can you help me with one more thing? With the Projects lookup, is there any way to populate other columns based on what is selected at project level or vice versa? For example: In the Projects column(Column L) , a drop down box offers a selection of items (linked to Lookup.xls). Say I select "Project A" from the Project Drop down, I want Column M to display the Project Code, and Column N to display an accounting code. Is this possible? "Arvi Laanemets" wrote: Hi The easiest way to do this: 1. In every user file, add a sheet Links; 2. On sheet links, design 3 cell ranges, where cells are linked with cells in Lookup.xls tables Organisation, Employment Status, and Projects; P.e. Links!A1=IF('C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Organisation'!A1="","",'C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Organisation'!A1) , and copy down into range A1:A6 Links!D1=IF('C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Employment Status'!A1="","",'C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Employment Status'!A1) , and copy down into range D1:D3 Links!G1=IF('C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Projects'!A1="","",'C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Projects'!A1) , and copy down into range A1:A12 3. Define ranges Links!A1:A6, Links!D1:D6, and Links!G1:G12 an named ranges Organisation, Emploiment, and Projects respectively; 4. In your Data validation lists, refer to those named ranges; 5. Hide the sheet Links (there is no reason the user must have direct access to it). -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Sharon" wrote in message ... Hi, I need a solution to the following problem. I have a template for each member of staff [e.g John Doe Time Tracking.xls] (for about 50 people) to track time spent on particular activities (projects) and which company they are employed by. Because the lists of Companies, Projects and EMployment Status may change from time to time, I would like to maintain this in a centralised spreadsheet rather than in each of the 50 worksheets that each member of staff uses. So on every template, I would like the following: Cell: $F$5 to select from Range A1:A6 in Lookup.xls'Organisation' Cell: $F$6 to select from Range A1:A3 in Lookup.xls'Employment Status' Cells: $L5:$L100 to select from Range A1:A12 in Lookup.xls'Projects' Is there a simple way of doing this so that the variables can be held and maintained in a seperate excel file? Regards Sharon |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Data Validation vs VLOOKUP - Linking to data in a seperate fil
Hi
In Lookup.xls on sheet Projects you must have those columns (ProjectCode, N). On sheet Links, into columns right to Projects column, add link formulas to retrieve project code and N values from Lookup.xls. Define an additional named range, p.e. ProjectTable=Links!$G$1:$I$12. Now, when p.e. in cell C2 you have a dropdown to select a project, into any other column on same row you can enter the formula =VLOOKUP($C2,ProjectTable,2,0) or =VLOOKUP($C2,ProjectTable,3,0) , and according project code or N is displayed. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Sharon" wrote in message ... Hi Arvi, thanks so much for the response.. I knew there was a simple solution... Can you help me with one more thing? With the Projects lookup, is there any way to populate other columns based on what is selected at project level or vice versa? For example: In the Projects column(Column L) , a drop down box offers a selection of items (linked to Lookup.xls). Say I select "Project A" from the Project Drop down, I want Column M to display the Project Code, and Column N to display an accounting code. Is this possible? "Arvi Laanemets" wrote: Hi The easiest way to do this: 1. In every user file, add a sheet Links; 2. On sheet links, design 3 cell ranges, where cells are linked with cells in Lookup.xls tables Organisation, Employment Status, and Projects; P.e. Links!A1=IF('C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Organisation'!A1="","",'C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Organisation'!A1) , and copy down into range A1:A6 Links!D1=IF('C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Employment Status'!A1="","",'C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Employment Status'!A1) , and copy down into range D1:D3 Links!G1=IF('C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Projects'!A1="","",'C:\Documents and Settings\Profile\My Documents\[Lookup.xls]Projects'!A1) , and copy down into range A1:A12 3. Define ranges Links!A1:A6, Links!D1:D6, and Links!G1:G12 an named ranges Organisation, Emploiment, and Projects respectively; 4. In your Data validation lists, refer to those named ranges; 5. Hide the sheet Links (there is no reason the user must have direct access to it). -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Sharon" wrote in message ... Hi, I need a solution to the following problem. I have a template for each member of staff [e.g John Doe Time Tracking.xls] (for about 50 people) to track time spent on particular activities (projects) and which company they are employed by. Because the lists of Companies, Projects and EMployment Status may change from time to time, I would like to maintain this in a centralised spreadsheet rather than in each of the 50 worksheets that each member of staff uses. So on every template, I would like the following: Cell: $F$5 to select from Range A1:A6 in Lookup.xls'Organisation' Cell: $F$6 to select from Range A1:A3 in Lookup.xls'Employment Status' Cells: $L5:$L100 to select from Range A1:A12 in Lookup.xls'Projects' Is there a simple way of doing this so that the variables can be held and maintained in a seperate excel file? Regards Sharon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation w/ Dependant Lists on seperate Wookbooks | Excel Worksheet Functions | |||
Linking Spreadsheets for Data Validation | Excel Discussion (Misc queries) | |||
Data Validation on Vlookup | Excel Discussion (Misc queries) | |||
Linking data from Rows to columns in seperate worksheets | Excel Worksheet Functions | |||
Linking Data Validation | Excel Discussion (Misc queries) |