Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Graphing 3 Relationships | Charts and Charting in Excel | |||
can you create relationships in excel | Excel Discussion (Misc queries) | |||
Relationships / Calculations | Excel Discussion (Misc queries) | |||
Recording macros with relative row and column relationships | Excel Discussion (Misc queries) | |||
Table relationships in Excel? | Excel Discussion (Misc queries) |