Create database in excel?
 

Hi -
I could really use some help. I am trying to create a database of
non-profit organizations in Excel. My thought is to create a workbook
with Sheet1 being a Master List and Sheet2 through Sheet200 (for
example) being the information for each individual non-profit
(Organization Name, Address, Contact, Service Area, Phone #, Mission
Statement, etc.). The Master List would display information from all
non-profits listing down (with column headings listed at the top).
Each individual sheet would be a template of all the individual
non-profit information in a page view (with headers listing down the
left of the spreadsheet and information in the corresponding cell to
the right). I am assuming I would be able to reorganize the Master
List in a number of differrent ways should I desire -- alphabetically
by name, by service type (drop down menu?), by area, etc..

Assuming that this is a viable method, here are my stumbling blocks
...

1. I need a way to create 200 worksheets of my template (which I have
not yet created, but am sure I can figure out with the aid of Excel
Help) in the workbook without having to add them one by one via
InsertSheet.

2. I would need the information from Sheets 2-200 to be automatically
entered into the Master List. I am able to link the cells by entering
=Sheet2!B1 for example into the cell on the appropriate master list
cell. Is there a way to automate the filling of subsequent cells
below it with =Sheet3!B1, =Sheet4!B1, =Sheet5!B1, ... =Sheet200!B1? I
have tried with Fill/Series, but to no avail.

I am sure there are more stumbling blocks ahead, but these are the two
I have immediately identified. I began this process in an Appleworks
Database, but will need many people to have access to it in the future
and know a majority will not be Mac users. This last month has been my
first experience with Excel and I am finding it a very adaptable
program. I plan to continue using it in the future. Any help would be
greatly appreciated.

Thanks - Tom


--
wirthless
------------------------------------------------------------------------
wirthless's Profile: http://www.excelforum.com/member.php...o&userid=28423
View this thread: http://www.excelforum.com/showthread...hreadid=480183

Create database in excel?
 

Don't bother with Sheets 2 through 200. To provide a page for each one, set
it up as a Word mail merge. I just did this last night, with 3 "companies"
per page. You can have just one or two or however many you like. If you use
Excel, you'll constantly be changing the data and creating new worksheets.
This way, you just run the merge again, perhaps for a specific record only
(like the "new" record number 201 that you just received the information on.
Here's mail merge:
http://www.officearticles.com/word/m...osoft_word.htm
Also, by doing this, you've already got your data ready to print labels or
anything else you can think of.
************
Anne Troy
www.OfficeArticles.com

"wirthless" wrote
in message ...

Hi -
I could really use some help. I am trying to create a database of
non-profit organizations in Excel. My thought is to create a workbook
with Sheet1 being a Master List and Sheet2 through Sheet200 (for
example) being the information for each individual non-profit
(Organization Name, Address, Contact, Service Area, Phone #, Mission
Statement, etc.). The Master List would display information from all
non-profits listing down (with column headings listed at the top).
Each individual sheet would be a template of all the individual
non-profit information in a page view (with headers listing down the
left of the spreadsheet and information in the corresponding cell to
the right). I am assuming I would be able to reorganize the Master
List in a number of differrent ways should I desire -- alphabetically
by name, by service type (drop down menu?), by area, etc..

Assuming that this is a viable method, here are my stumbling blocks
..

1. I need a way to create 200 worksheets of my template (which I have
not yet created, but am sure I can figure out with the aid of Excel
Help) in the workbook without having to add them one by one via
InsertSheet.

2. I would need the information from Sheets 2-200 to be automatically
entered into the Master List. I am able to link the cells by entering
=Sheet2!B1 for example into the cell on the appropriate master list
cell. Is there a way to automate the filling of subsequent cells
below it with =Sheet3!B1, =Sheet4!B1, =Sheet5!B1, ... =Sheet200!B1? I
have tried with Fill/Series, but to no avail.

I am sure there are more stumbling blocks ahead, but these are the two
I have immediately identified. I began this process in an Appleworks
Database, but will need many people to have access to it in the future
and know a majority will not be Mac users. This last month has been my
first experience with Excel and I am finding it a very adaptable
program. I plan to continue using it in the future. Any help would be
greatly appreciated.

Thanks - Tom


--
wirthless
------------------------------------------------------------------------
wirthless's Profile:
http://www.excelforum.com/member.php...o&userid=28423
View this thread: http://www.excelforum.com/showthread...hreadid=480183

Create database in excel?
 

Thanks Anne, but I'm not sure how this works. I was looking to enter
information on the individual sheets and have it update the Master
Sheet. The site you reference achieves the opposite, Excel spreadsheet
to Word document. Also, it is obvious to me how to set up a template to
export from in Excel, but I'm not sure how to achieve that in Word.
Sorry if I am missing something obvious. I appreciate the assistance.

Tom


--
wirthless
------------------------------------------------------------------------
wirthless's Profile: http://www.excelforum.com/member.php...o&userid=28423
View this thread: http://www.excelforum.com/showthread...hreadid=480183

Create database in excel?
 

Tom: You can EASILY change the layout of ONE document. But what are you
going to do when you want to add a field? Change 200 worksheets?
************
Anne Troy
www.OfficeArticles.com

"wirthless" wrote
in message ...

Thanks Anne, but I'm not sure how this works. I was looking to enter
information on the individual sheets and have it update the Master
Sheet. The site you reference achieves the opposite, Excel spreadsheet
to Word document. Also, it is obvious to me how to set up a template to
export from in Excel, but I'm not sure how to achieve that in Word.
Sorry if I am missing something obvious. I appreciate the assistance.

Tom


--
wirthless
------------------------------------------------------------------------
wirthless's Profile:
http://www.excelforum.com/member.php...o&userid=28423
View this thread: http://www.excelforum.com/showthread...hreadid=480183

Create database in excel?
 

Anne -
Point well taken.

It appears that linking isn't the answer then. I guess all my inputing
will have to be hand entered into a Master Sheet by me instead of linked
or cut and pasted from a form. That is what I was trying to avoid.
From your first response it sounds like once the Master Sheet is filled
exporting in almost any format to Word is relatively simple.

Am I misunderstanding?

Thanks for your time and wisdom.
Tom


--
wirthless
------------------------------------------------------------------------
wirthless's Profile: http://www.excelforum.com/member.php...o&userid=28423
View this thread: http://www.excelforum.com/showthread...hreadid=480183

Create database in excel?
 

Hi Tom

In addition to Anne's suggestion using Word to do a mailmerge type
operation, you could stay within Excel if you wanted.
Supposing your Master sheet has Organisation Name in Column A, and the rest
of your data are in columns B to M (or as many required for the items you
are recording).

On Sheet2, copy this list of headings to cells A1:A13 by copying A1:M1 from
Sheet 1, select A1 on Sheet2 and Paste SpecialTranspose.
You only need to go down to the row number equivalent to the last column on
your Master sheet.
Make column A wide enough for the widest heading you have.

On Sheet2, in cell B2 enter
=VLOOKUP($B$1,Master!$A$1:$M$250,Row(),0)
Copy the formula down through B3:B13
(If your Master sheet is not actually named as such, but is Sheet1, then
substitute Sheet1! for Master! in the formula.

Now, any Organisation Name entered into B1, would have their respective data
pulled across from the Master Sheet.

To make it easy to select an Organisation, create a Named List of
organisations by InsertNameDefine and in the Name pane type Names and in
the Refers to pane enter =OFFSET(Master!$A$1,0,0,COUNTIF(Sheet2!A:A,"<"))

On Sheet2, apply Data Validation to cell B1 by selecting the cell,
DataValidation select List from the Allow dropdown, and in the pane
marked Source enter =Names.

You will now have a dropdown on cell B1 to allow you to select any
organisation, and all the relevant data will be shown down the page as you
require.

For more help on Data Validation take a look at
http://www.contextures.com/xlDataVal01.html
and for more information on Vlookup also on Debra's site
http://www.contextures.com/xlFunctions02.html

Regards

Roger Govier


wirthless wrote:
Anne -
Point well taken.

It appears that linking isn't the answer then. I guess all my inputing
will have to be hand entered into a Master Sheet by me instead of linked
or cut and pasted from a form. That is what I was trying to avoid.
From your first response it sounds like once the Master Sheet is filled
exporting in almost any format to Word is relatively simple.

Am I misunderstanding?

Thanks for your time and wisdom.
Tom

Create database in excel?
 

Apologies Tom

Typo in formula for named range.
NOT =OFFSET(Master!$A$1,0,0,COUNTIF(Sheet2!A:A,"<"))
but instead
=OFFSET(Master!$A$1,0,0,COUNTIF(Master!A:A,"<"))

Regards

Roger Govier


Roger Govier wrote:
Hi Tom

In addition to Anne's suggestion using Word to do a mailmerge type
operation, you could stay within Excel if you wanted.
Supposing your Master sheet has Organisation Name in Column A, and the
rest of your data are in columns B to M (or as many required for the
items you are recording).

On Sheet2, copy this list of headings to cells A1:A13 by copying A1:M1
from Sheet 1, select A1 on Sheet2 and Paste SpecialTranspose.
You only need to go down to the row number equivalent to the last column
on your Master sheet.
Make column A wide enough for the widest heading you have.

On Sheet2, in cell B2 enter
=VLOOKUP($B$1,Master!$A$1:$M$250,Row(),0)
Copy the formula down through B3:B13
(If your Master sheet is not actually named as such, but is Sheet1, then
substitute Sheet1! for Master! in the formula.

Now, any Organisation Name entered into B1, would have their respective
data pulled across from the Master Sheet.

To make it easy to select an Organisation, create a Named List of
organisations by InsertNameDefine and in the Name pane type Names and
in the Refers to pane enter
=OFFSET(Master!$A$1,0,0,COUNTIF(Sheet2!A:A,"<"))

On Sheet2, apply Data Validation to cell B1 by selecting the cell,
DataValidation select List from the Allow dropdown, and in the pane
marked Source enter =Names.

You will now have a dropdown on cell B1 to allow you to select any
organisation, and all the relevant data will be shown down the page as
you require.

For more help on Data Validation take a look at
http://www.contextures.com/xlDataVal01.html
and for more information on Vlookup also on Debra's site
http://www.contextures.com/xlFunctions02.html

Regards

Roger Govier


wirthless wrote:

Anne -
Point well taken.

It appears that linking isn't the answer then. I guess all my inputing
will have to be hand entered into a Master Sheet by me instead of linked
or cut and pasted from a form. That is what I was trying to avoid.
From your first response it sounds like once the Master Sheet is filled
exporting in almost any format to Word is relatively simple.
Am I misunderstanding?
Thanks for your time and wisdom.
Tom

Create database in excel?
 

You are making a very common mistake by confusing data storage with
user communication.

You have the right idea of storing pertinent information about every
NPO in one worksheet -- well, as long as you can do that using one and
only one row per NPO.

For interactive display / update purposes, use something like Data |
Form... John Walkenbach has an add-in that supports enhanced
capability on his web site at
http://www.j-walk.com/ss/dataform/index.htm. Fair warning: I haven't
used it; hence, cannot comment on it.

For print purposes, use Word's Mail Merge capability. It's pretty
straightforward and requires little effort to print one letter/form per
NPO.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

Hi -
I could really use some help. I am trying to create a database of
non-profit organizations in Excel. My thought is to create a workbook
with Sheet1 being a Master List and Sheet2 through Sheet200 (for
example) being the information for each individual non-profit
(Organization Name, Address, Contact, Service Area, Phone #, Mission
Statement, etc.). The Master List would display information from all
non-profits listing down (with column headings listed at the top).
Each individual sheet would be a template of all the individual
non-profit information in a page view (with headers listing down the
left of the spreadsheet and information in the corresponding cell to
the right). I am assuming I would be able to reorganize the Master
List in a number of differrent ways should I desire -- alphabetically
by name, by service type (drop down menu?), by area, etc..

Assuming that this is a viable method, here are my stumbling blocks
..

1. I need a way to create 200 worksheets of my template (which I have
not yet created, but am sure I can figure out with the aid of Excel
Help) in the workbook without having to add them one by one via
InsertSheet.

2. I would need the information from Sheets 2-200 to be automatically
entered into the Master List. I am able to link the cells by entering
=Sheet2!B1 for example into the cell on the appropriate master list
cell. Is there a way to automate the filling of subsequent cells
below it with =Sheet3!B1, =Sheet4!B1, =Sheet5!B1, ... =Sheet200!B1? I
have tried with Fill/Series, but to no avail.

I am sure there are more stumbling blocks ahead, but these are the two
I have immediately identified. I began this process in an Appleworks
Database, but will need many people to have access to it in the future
and know a majority will not be Mac users. This last month has been my
first experience with Excel and I am finding it a very adaptable
program. I plan to continue using it in the future. Any help would be
greatly appreciated.

Thanks - Tom


--
wirthless
------------------------------------------------------------------------
wirthless's Profile: http://www.excelforum.com/member.php...o&userid=28423
View this thread: http://www.excelforum.com/showthread...hreadid=480183