Spreadsheet Front-end

[OtisAir]

I'm searching online but figured I'd ask ya'll too cuz' ya'll are smart... I'm helping out a friend catch up on a bunch of office trip expense reports that are from 2008. right now, it's a big mess and the spreadsheet being used is setup like this:

1 Workbook
5 Worksheets
- Summary
- Misc Expense
- Home Office
- Domestic Travel
- International Travel

Each worksheet has pretty much the same basic template:

Columns:

Date Recipt Info LocalAmount Currency USD Value


The Local Amount is a number value: e.g. 45
The Currency is what (USD, GBP, Euro's, etc) GBP
The USD Value is based on what th exchange rate is: let's say 2

So that row looks like:

12/11/2008 Newark Manchester return 45 GBP 90

The spreadsheets are all setup fine, I just wanted to give you background. Oh, the AMEX or CC is just a text field on doesn't have any formula's associated with it.



What I am trying to do (and am clueless how to) is create a front end (which I have without code behind it) that will take the info on the form and put it on the correct sheet based on the choices I make. I'll include a picture.

Is this more trouble than it's worth? If I can figure out how to send it to the proper worksheet using the Misc Expenses, Home Office, Domestic, or International radio button, I'll atleast be able to get started sorting out the rest of the stuff. Each receipt will be combined with other "like" receipts (same trip/date). but that's step two.

Please don't try to write an application for me, this is actually fun trying to figure it out, but I thought I'd ask you guru's for guidance along the way. I'll share it once I / We are finished.

Thanks!

 

[EdFred]

Setup is always a lot of trouble, but worth it in the long run. I'd go with a database vs a spreadsheet though.

 

[CJones]

I like Ed's idea. Set up a simple Access Database which has the tools to create a front-end built in.

 

[jesse]

How much time are you wanting to put into this? Building a front end with a backend database takes some time. Do you know how to program or how to use a relational database?

The easiest solution would probably involve forms in Excel. See: http://exceltip.com/st/Create_User_Forms_in_Microsoft_Excel/629.html

The next easiest would be Access.

Who is the intended user? If it is something just to make your job a little easier and you don't mind excel I'd just stick with the forms in Excel. If it is something you want other people to use Access *might* be an OK choice. If it is something you want almost anyone with a web browser to be able to use you'll need to build some sort of webapp with a relational database on the back-end (time consuming process).

 

[OtisAir]

Hey Ya'll - actually it's for an old friend of mine who's retired and now doing some part time volunteer administrative assistant work and she's having to organize and enter a bunch of expense reports from 2008 that were never filed. She'll be the only one using the "front-end", but the output (excel spreadsheets) is what her boss wants and there appears to be no changing it to Access/SQL/SAP/etc. I was just trying to make her job easier as she called me asking if I knew of a more efficient way to enter the data. I told her I'd ask (ya'll) to see if there was an easy answer. Thanks

 

[mikea]

The code behind the form in the Workbook has Worksheet('Name').Cell.([address]).Value = form.box.value (as in the box on the form with the data you want to save.) You'll also have to call a subroutine to find the next cell with no data in it and use that [address] in the formula.

Pick up a copy of the O'Reilly book to see how the coding is done, but take my word for it after I've invested hundreds of hours, Excel will crash, corrupt the workbook and lose your work. Save everything often.

 

[KennyFlys]

Couldn't everything wanting to be accomplished be done in Quicken Home & Business for about $90?

 

[CJones]

Hey Ya'll - actually it's for an old friend of mine who's retired and now doing some part time volunteer administrative assistant work and she's having to organize and enter a bunch of expense reports from 2008 that were never filed. She'll be the only one using the "front-end", but the output (excel spreadsheets) is what her boss wants and there appears to be no changing it to Access/SQL/SAP/etc. I was just trying to make her job easier as she called me asking if I knew of a more efficient way to enter the data. I told her I'd ask (ya'll) to see if there was an easy answer. Thanks

Hmmm.. I can't help you with Excel. I'm just getting my feet wet with it now. I would 'assume' there is some way to put a nice GUI on data entry for it, though. I'll keep pondering it for a bit...

 

[EdFred]

Access will export to excel, thus satisfying the pinhead boss.

 

[jesse]

Hmmm.. I can't help you with Excel. I'm just getting my feet wet with it now. I would 'assume' there is some way to put a nice GUI on data entry for it, though. I'll keep pondering it for a bit...

You can create forms with Visual Basic. See my above link. Like Mike said though--trusting excel is basically a waste of time.

 

[Cap'n Jack]

You can create forms with Visual Basic. See my above link. Like Mike said though--trusting excel is basically a waste of time.

I have a different experience with Excel- If I had decent interent access here I'd post something that can be used as I've done this many times.

Access- a very different matter. I don't trust that much.

In addition to the O'Reilly books, I'd suggest looking up the WROX books as these are generally well written.

 

[EdFred]

Why dont you trust access? Ive been running a database for the last 11 years in Access with no issue, and it is by no means a simple database. Around 50-75 tables, 100 plus queries (it might be over 200), thousands and thousands of lines of VB code along with numerous forms and reports. Imports from outlook, exports to out look, imports from excel, exports to excel. I have never had an issue with it.

 

[jesse]

Why dont you trust access? Ive been running a database for the last 11 years in Access with no issue, and it is by no means a simple database. Around 50-75 tables, 100 plus queries (it might be over 200), thousands and thousands of lines of VB code along with numerous forms and reports. Imports from outlook, exports to out look, imports from excel, exports to excel. I have never had an issue with it.

It sort of works OK for stuff like that. I personally wouldn't trust it with any real business data--but that is just me--I like things like binary logging, point-in-time restore, database check summing, database replication, etc.

The other problem with Access is the fact that it doesn't scale at all. Put it under any load and you end up with a complete mess. It does work for real small scale deals (small small business)--but once again--you better back up and back up often. To each their own