Excel help and making my life easier - Corsa Sport - for Vauxhall and Opel Corsa B, Corsa C and Corsa D

Not logged in [Login - Register]

You Are Not Registered Or Not Logged In

Corsa Sport » Message Board » Off Day » Geek Day » Excel help and making my life easier

New Topic

New Poll

Subscribe | Add to Favourites

You are not logged in and may not post or reply to messages. Please log in or create a new account or mail us about fixing an existing one - register@corsasport.co.uk

There are also many more features available when you are logged in such as private messages, buddy list, location services, post search and more.

Melville

Member

Registered: 4th Jun 03
Location: Newcastle upon Tyne
User status: Offline

26th Oct 06 at 10:26

At work we receive daily excel files from one of our clients that have there takings figures on. I need to make a summary of a 3 month period.

Is there a way to make a "template" that can somehow automatically pick up the figures from say 90 different excel files? The takings figures are always in the same cell each day.

The client changing the way in which they do it is out of the question.

It might not sound like a lot of work but there are 5 figures per day (Tabacco, Std rated, zero rated, exempt, 5%) that need to summarised and takes me half a day to do it at present.

Any help would be greatfully appreciated.

Thanks, Mark

AndyKent

Member

Registered: 3rd Sep 05
User status: Offline

26th Oct 06 at 12:42

The only way I would know how to do it is to save all of the Excel sheets as CSV files and import them into a database.

Excel can be set up to pull data from a database so would work but it'll mean you have to re-save excel 90 files - not very practical.

Thats my only thought???

Planty02

Member

Registered: 5th Mar 05
Location: Burslem, Stoke-on-Trent
User status: Offline

26th Oct 06 at 12:53

='\path\[spreadsheet.xls]SheetName'!Cell

should retrieve data from the specified sheet/cell into whichever cell u put the formula in - just depends if the 90 spreadsheets change name each time you recieve them or are in different folders

If each daily spreadsheet has the same name and is in the same folder there shouldnt be any problems

Planty02

Member

Registered: 5th Mar 05
Location: Burslem, Stoke-on-Trent
User status: Offline

26th Oct 06 at 12:53

p.s. i think the path can be left off if all spreadsheets are in teh same folder

Melville

Member

Registered: 4th Jun 03
Location: Newcastle upon Tyne
User status: Offline

26th Oct 06 at 13:02

The spreadsheets are named the date ie 26.10.06 and I put them all in the same folder

I will give it a try thanks

Planty02

Member

Registered: 5th Mar 05
Location: Burslem, Stoke-on-Trent
User status: Offline

26th Oct 06 at 13:43

give this a try

each day enter this:

[26.10.2006.xls]Sheet1'!$C$3
(in this example it gets the value from cell C3 of sheet1 from 26.10.2006.xls)

and use the fill tool to copy it

Melville

Member

Registered: 4th Jun 03
Location: Newcastle upon Tyne
User status: Offline

26th Oct 06 at 13:57

It works BUT an "update Values" box opens and you have to navigate to the workboot and it will update it.

[Edited on 26-10-2006 by Melville]

Melville

Member

Registered: 4th Jun 03
Location: Newcastle upon Tyne
User status: Offline

26th Oct 06 at 13:59

Foget that, just me being stupid. It works with a test Ive made so I will give it a try with the real thing

Planty02

Member

Registered: 5th Mar 05
Location: Burslem, Stoke-on-Trent
User status: Offline

26th Oct 06 at 14:00

lol ok mate

hope it works out