Microsoft Excel Mad Tekkers (Do you have what it takes?)

[theprawnidentity]

Does anyone from Blonde have mad excel tekkers?  I currently have a load of excel sheets with loads of data it and I need to extract the data from the indiviual sheets into one sheet.  I'm pretty sure I need to use some kind of Visual Basic function or formula but I'm currently floating down a river of excrement without any means of propulsion.

If you have any advice that could help me out, or could offer me a direct solution, there would be a cash reward.

[Evilpengwinz]

As long it's one spreadsheet with several sheets in it, then it's as follows:

Let's say you have 3 sheets called Sheet1, Sheet2 and Sheet3. You want to take Cell A1 from Sheet1, and the Cell E5 from Sheet2, and put them both somewhere on Sheet3. Doesn't matter where.

If you use the formula ='Sheet1'!A1, it will take the value in the A1 cell on Sheet1 and put it into whichever cell you have typed that formula.

For Cell E5 from Sheet2, it's the same, just change the name and the cell to match. ='Sheet2'!E5

If you rename your sheets - For instance, my cash records have a sheet for each level, so instead of Sheet1, you type whatever you renamed that sheet - e.g. ='50NL'!E5

[Jon MW]

The best way would depend on whether it's a one-off or an ongoing task you want repeated and also on what kind of thing you want doing with the data after it's all on the same sheet.

[RED-DOG]

I'm currently floating down a river of excrement without any means of propulsion.

Beautifully put.

[theprawnidentity]

There are about 50-60 seperate excel documents with 1-5 sheets in each of them. All the sheets are formatted in the same way.  Is it possible to merge all the sheets together in one big document then go for ='sheet1'! Etc... (Though that feels like it might get complicated with there probably been in excess of 100 sheets)

All data is going into a pivot table that I have already set up. I just need to get all the data into the data sheet.

[Jon MW]

If it was a recurring job I might write a vb script to help automate it - if it was a one off I'd just use a big formula to put them together (file1.sheet1.cellwhatever + file1.sheet2.cellwhatever + file2.sheet1.cellwhatever ....) except I'd use a page in excel just to construct the formula rather than type and re-type every element.

[ZZZZZZZROPE]

use a freelance website and get someone to do it for you for like $50

[theprawnidentity]

use a freelance website and get someone to do it for you for like $50

Hey dude.  Yeh great advice, I've thrown it on elance.  By the looks of it there will defo be someone there who can help me.  Thanks!!!

[tikay]

This is so simple.

Just send "MereNovice" a PM, & he will sort it for you in a trice, & enjoy doing so. He is the absolute nuts Spreadsheet geek.

Job done.

[ACE2M]

copy and paste?

[theprawnidentity]

copy and paste?

Defo underestimating the volume of the data.  I think theres in the region of 5000 lines spread over about 200 documents.  I've got someone helping me out now, thanks for all the advice.

[h]

 if I understood problem correctly
I would import excel sheets in to Microsoft Access  combine resulting tables and export as one excel sheet
shouldnt take more than 20 mins if you don't resolve let me know and I will give it a go for you

[SubZERO]

if I understood problem correctly
I would import excel sheets in to Microsoft Access  combine resulting tables and export as one excel sheet
shouldnt take more than 20 mins if you don't resolve let me know and I will give it a go for you

MS Access? WTF its not 1990!!

This is a good idea though, just use mysql instead

[theprawnidentity]

Thanks for the suggestions.  I went with a freelance website and they have sorted it for me, only cost a few $ as well.