Data Validation vs VLOOKUP - Linking to data in a seperate file

[Sharon]

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

 

[Arvi Laanemets]

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 D13

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!D16, 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).

 

[Sharon]

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?

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 D13

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!D16, 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<at>tarkon.ee )

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

 

[Arvi Laanemets]

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<at>tarkon.ee )

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?

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 D13

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!D16, 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<at>tarkon.ee )

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