I NEED A HERO!!

kbrownforcally · Apr 24th, 2002, 09:29 PM

I am creating a database of companies and products purchased and I am trying to figure out how to use one worksheet in a workbook as a GUI form to capture data that is stored in database format in another worksheet in the same workbook.

I know the excel template wizard does this already but the data is captured in a separate workbook. Is there a way to modify this?

Thanks for any assistance.

Mark O'Brien · Apr 24th, 2002, 10:54 PM

What exactly are you asking for here?

What are you looking for this pseudo GUI to do?

You're saying that the data is in the same workbook one minute and the next you're saying that the data is in a separate workbook. Which is it?

kbrownforcally · Apr 24th, 2002, 11:51 PM

Mark,

Thanks for the post and forgive my novice terminology.

Allow me to clarify.

I am wanting to use worksheet #1 for data entry. The worksheet is setup as a template with check boxes, pulldown menu's etc.

After inputing the data in the template I would like click a button or run a macro to send it to another worksheet in the same workbook but in database format(column headings)

So, it would work just like the template wizard but it wouldn't put the data into a separate workbook.

I hope that is more clear.

Thanks again.

Tom Schreiner · Apr 25th, 2002, 01:05 AM

That would not be difficult to do...
But to get a helpful code example here, you will need to list your controls and exactly where on the other sheet the data from each needs to be sent...

I don't mean to be vague, but these are details which would be needed to really help you out...

Thanks,
Tom

kbrownforcally · Apr 25th, 2002, 08:38 PM

The layout of the template worksheet is in a list so the cells for data entry would be something like

NAME:C5
ADDRESS: C6
CITY: C7
STATE: C8
ZIPp C9
ETC...

The worksheet holding the data will be in datbase format so the corresponding cells would be in rows like:

NAME:A2
ADDRESS: B2
CITY: C2
STATE: D2
ZIPp E2

Once again the template wizard with database tracking has the exact functionality I am looking for except I don't want to send the data to a worksheet in a separate workbook.

Thanks for any help!

Tom Schreiner · Apr 25th, 2002, 09:01 PM

With my limited knowledge, here is what I could help you do...
Place a button on the data entry sheet called "Add Record"...
Would check to make sure all the data has been entered, send to the next available row on your database sheet, and then sort the database.
Have you included a primary key on your data entry sheet? A unique identifier for each record?
Phone numbers are usually ok.
Last names are generally not a good idea because of duplicates.
Will you be querying this database?
Tom

Tom

kbrownforcally · Apr 25th, 2002, 09:16 PM

Tom,

What you propose sounds great. I do not have a unique identifier like you said the phone number would work.

I would like to query the database. I figured auto-filters would work well or another spreadsheet that uses vblookup.

Thanks,

Kevin

Tom Schreiner · Apr 25th, 2002, 09:49 PM

All I need is a sample sheet for the data entry or at least the columns and data types in each...Dates, Text, Numbers, ect...
Or just send me sample...
Also, which cells in a record are mandatory for entry or not(For Validation)
By which column do you want to sort your database?
TsTom@Hotmail.com
Thanks,
Tom

Apr 24th, 2002, 10:54 PM	#2
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	What exactly are you asking for here? What are you looking for this pseudo GUI to do? You're saying that the data is in the same workbook one minute and the next you're saying that the data is in a separate workbook. Which is it? __________________ Mark O'Brien Columbus Ohio Celtic Supporters Club

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 24th, 2002, 09:29 PM	#1
kbrownforcally New Member Join Date: Apr 2002 Posts: 5	I am creating a database of companies and products purchased and I am trying to figure out how to use one worksheet in a workbook as a GUI form to capture data that is stored in database format in another worksheet in the same workbook. I know the excel template wizard does this already but the data is captured in a separate workbook. Is there a way to modify this? Thanks for any assistance.

Apr 24th, 2002, 11:51 PM	#3
kbrownforcally New Member Join Date: Apr 2002 Posts: 5	Mark, Thanks for the post and forgive my novice terminology. Allow me to clarify. I am wanting to use worksheet #1 for data entry. The worksheet is setup as a template with check boxes, pulldown menu's etc. After inputing the data in the template I would like click a button or run a macro to send it to another worksheet in the same workbook but in database format(column headings) So, it would work just like the template wizard but it wouldn't put the data into a separate workbook. I hope that is more clear. Thanks again.

Apr 25th, 2002, 01:05 AM	#4
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	That would not be difficult to do... But to get a helpful code example here, you will need to list your controls and exactly where on the other sheet the data from each needs to be sent... I don't mean to be vague, but these are details which would be needed to really help you out... Thanks, Tom

Apr 25th, 2002, 08:38 PM	#5
kbrownforcally New Member Join Date: Apr 2002 Posts: 5	The layout of the template worksheet is in a list so the cells for data entry would be something like NAME:C5 ADDRESS: C6 CITY: C7 STATE: C8 ZIPp C9 ETC... The worksheet holding the data will be in datbase format so the corresponding cells would be in rows like: NAME:A2 ADDRESS: B2 CITY: C2 STATE: D2 ZIPp E2 Once again the template wizard with database tracking has the exact functionality I am looking for except I don't want to send the data to a worksheet in a separate workbook. Thanks for any help!

Apr 25th, 2002, 09:01 PM	#6
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	With my limited knowledge, here is what I could help you do... Place a button on the data entry sheet called "Add Record"... Would check to make sure all the data has been entered, send to the next available row on your database sheet, and then sort the database. Have you included a primary key on your data entry sheet? A unique identifier for each record? Phone numbers are usually ok. Last names are generally not a good idea because of duplicates. Will you be querying this database? Tom Tom

Apr 25th, 2002, 09:16 PM	#7
kbrownforcally New Member Join Date: Apr 2002 Posts: 5	Tom, What you propose sounds great. I do not have a unique identifier like you said the phone number would work. I would like to query the database. I figured auto-filters would work well or another spreadsheet that uses vblookup. Thanks, Kevin

Apr 25th, 2002, 09:49 PM	#8
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	All I need is a sample sheet for the data entry or at least the columns and data types in each...Dates, Text, Numbers, ect... Or just send me sample... Also, which cells in a record are mandatory for entry or not(For Validation) By which column do you want to sort your database? TsTom@Hotmail.com Thanks, Tom