Referencing external Excel datasource from within Ecel

[Monkeyboy126]

Hi all,

I am looking to develop a timesheeting system in Excel. The idea is for individuals to complete standard timesheets in Excel based on a standard template.

These will be sent to an administrator on a weekly basis and I would like the admin person to own a master timesheet that would import the individual data (booking codes and hours) to by automatically populated in the master.

I would prefer that the solution is a one button affair, perhaps a macro or something but it must be simple.

Any ideas of the best approach to a solution??

Many thanks in advance

Paul

 

[AnotherHiggins]

You will definitely need a macro, so please create a new thread in forum707.

Bear in mind that you'll need to do some of the heavy lifting on this yourself. This site is a place for professionals to help one another learn. Chances are slim-to-none that anyone here will write the entire code for you.

That having been said, if you clearly state your situation and provide specific requirements along with any code you have already written (or recorded via Excel's Macro Recorder [Tools > Macro > Record New Macro]) then chances are very good that you'll be guided in the right direction.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

Some general thoughts.

The way you should STORE your data is most likely different than the way you will want to REPORT or DISPLAY or INPUT your data.

Your data should be stored in normalized data structures.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Not to do so, will make future data analysis and data reporting very difficult.



Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[AnotherHiggins]

Oh, you also might want to google for [google]excel time sheet[/google]. There are many available for free download. You'll still need to deal with having the admin compile all the data (and I'd advise storing the data to a normalized table as Skip suggests), but at least you won't have to start from scratch in designing the time sheet itself.

Notable hits I noticed from the Google search:

Templates from microsoft's website:

http://office.microsoft.com/en-us/templates/CT101172771033.aspx

Some tips from Chip Pearson:

http://www.cpearson.com/excel/overtime.htm

A downloadable timesheet from John Walkenbach:

http://www.j-walk.com/ss/excel/files/timesht.htm

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Also check out this FAQ...

faq68-5827

In particular, recognize the situation where a shift might start on one day and end on the next, and that the Date portion of Date/Time must accompany the Time portion of Date/Time, in order to calculate a duration properly.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[xlbo]

And your easierst way to "suck in" data from other excel workbooks will probably be using ADO and SQL. Lots of threads about these subjects in the archives here...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question