Excel Table Relationships
 

I would like to build an Excel 'Database'. I know Access is the better
programme to use, however I have zero experience in it and have been
frustrated with what I've tried to learn, and I have a looming deadline.

I understand how to create a table, but i'm having trouble making data
validation in one table referencing another.

For example I have created and named a 'Client_Table' with ID#, Name,
Adrress, etc, etc and I have created a second table named 'Loan_Table'. As I
enter the date of a new loan into the Loan_Table, I have a column named
'client ID'. In that column I would like to have a dropdown list showing the
available ID numbers in the client table (which will be dynamic and continue
to grow). As I select an ID number, I would like the client name from that
table to show in the loan table.

Suggestions?

Excel Table Relationships
 

Select your Client ID column and go to Insert/Name/Define. Give it an easy
name like ClientList. Use Data Validation (Data Validation List) . Ensure
you have ignore blanks selected. Enter =ClientList as your Source

"Jim" wrote:

I would like to build an Excel 'Database'. I know Access is the better
programme to use, however I have zero experience in it and have been
frustrated with what I've tried to learn, and I have a looming deadline.

I understand how to create a table, but i'm having trouble making data
validation in one table referencing another.

For example I have created and named a 'Client_Table' with ID#, Name,
Adrress, etc, etc and I have created a second table named 'Loan_Table'. As I
enter the date of a new loan into the Loan_Table, I have a column named
'client ID'. In that column I would like to have a dropdown list showing the
available ID numbers in the client table (which will be dynamic and continue
to grow). As I select an ID number, I would like the client name from that
table to show in the loan table.

Suggestions?

Excel Table Relationships
 

You will want to set up a dynamic named range on your client ID... Here is
info on doing that...

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

That will allow you to create your drop down. Now once the drop down field
has been populated you will need formulas to populate the remaining fields.
Thsi can be done with VLookups or better yet Index/Match formulas. I would
use static named ranges for your lookups that reference a larger area than
you reasonably anticipate using. Dynaic named ranges will make your functions
volatile and slow down calculations.

It is important to note that this file is going to bloat up very large if
your database gets even moderately big. As you know Access is the better
choice and at some point this whole project could become unmanagable in XL...
--
HTH...

Jim Thomlinson


"Jim" wrote:

I would like to build an Excel 'Database'. I know Access is the better
programme to use, however I have zero experience in it and have been
frustrated with what I've tried to learn, and I have a looming deadline.

I understand how to create a table, but i'm having trouble making data
validation in one table referencing another.

For example I have created and named a 'Client_Table' with ID#, Name,
Adrress, etc, etc and I have created a second table named 'Loan_Table'. As I
enter the date of a new loan into the Loan_Table, I have a column named
'client ID'. In that column I would like to have a dropdown list showing the
available ID numbers in the client table (which will be dynamic and continue
to grow). As I select an ID number, I would like the client name from that
table to show in the loan table.

Suggestions?

Excel Table Relationships
 

Jim,

I'm enrolled in an Access course in the fall, I'll start the correct
software for my database after that. In the meantime, I'm stuck with Excel.
The little bit of fooling around with Access I did, I found creating the
tables fairly strait forward, which I can then import in Excel and mess
about. What that be a better route that using Excel exclusively?

Thanks

JIM

"Jim Thomlinson" wrote:

You will want to set up a dynamic named range on your client ID... Here is
info on doing that...

http://www.cpearson.com/excel/excelF.htm#DynamicRanges

That will allow you to create your drop down. Now once the drop down field
has been populated you will need formulas to populate the remaining fields.
Thsi can be done with VLookups or better yet Index/Match formulas. I would
use static named ranges for your lookups that reference a larger area than
you reasonably anticipate using. Dynaic named ranges will make your functions
volatile and slow down calculations.

It is important to note that this file is going to bloat up very large if
your database gets even moderately big. As you know Access is the better
choice and at some point this whole project could become unmanagable in XL...
--
HTH...

Jim Thomlinson


"Jim" wrote:

I would like to build an Excel 'Database'. I know Access is the better
programme to use, however I have zero experience in it and have been
frustrated with what I've tried to learn, and I have a looming deadline.

I understand how to create a table, but i'm having trouble making data
validation in one table referencing another.

For example I have created and named a 'Client_Table' with ID#, Name,
Adrress, etc, etc and I have created a second table named 'Loan_Table'. As I
enter the date of a new loan into the Loan_Table, I have a column named
'client ID'. In that column I would like to have a dropdown list showing the
available ID numbers in the client table (which will be dynamic and continue
to grow). As I select an ID number, I would like the client name from that
table to show in the loan table.

Suggestions?