Do we have a resident expert in Microsoft Excel here?

[Guest]

[Roz]

Because if we do could you give me a hand. I have 12 different files with quite a lot of data in them (even so it's only summary data of what I have collected) and I would like to put them all into one file but on different "sheets" within that file (I think it's called a sheet - i.e. the 12 different files would be on different sheets in the same document and I would click on a tab on the bottom to get the different sheets).

Can I merge them in some way or do I need to manually copy and paste the data in?

Thank you to anyone out there who thinks that they can offer some help.

[Anna]

I would manually copy/paste each file onto its own sheet.  When you do this though, each sheet has a different page setup, so you would have to change the setup on each sheet separately.

If you merged the 12 files before copying/pasting, wouldn't you have to extract the data for the different sheets later on?

Sorry I'm not much help.

[Martyn]

If you right click on the tab's at the bottom of the page (e.g. Sheet1, Sheet2) there is a option to "Move or Copy" you might be to select where you want to move the sheets to (e.g. different worksheet)

I have never tried it before though! Hope that helps

[Roz]

No no that's fine - I was assuming I'd have to do it the manual way - ho hum

You see once they are in one file I should be able to plot one series of data against another (yes I know you can do that with different files but should the file be mis-copied or separated from the other files later on I can see all sorts of nasty error messages emerging).

Thanks Anna

[Roz]

Oh and thanks Martyn - sounds like the best thing for me to do is to have a play around with it.

[allender]

Roz, probably you'll become a quick expert in this after playing around just a little bit. But I know how what seems "natural" to somebody who's used the software since version 1.0 might not seem natural to somebody else. If you get stumped, feel free to send me a couple and I'll show you the result after I did it.  I have all of my stale and new tracking data for POA, POA Update, TSS, LOTR ROTKU, LOTR Trilogy, and a few others in a single file called oddly enough "Track.xls".  It's very handy.

If you don't already have a blank Sheet 2, Sheet 3, etc., use the Edit command "Move or Copy Sheet" and copy the current sheet, ticking the box "Make a Copy" and move to the end. Then you can select all of the columns and delete them, giving you a blank sheet. Then open the other file and select all of the active columns and paste those into your page 2. That should maintain your column widths and formatting.

You can rename the new sheet by clicking on its title at the bottom of the window. You may have to change your "magnification" view percent or your print setting, because those are specific to each sheet.

When you consolidate stuff into one file, you might want to back it up frequently!

[Roz]

Thanks for that - I guess nothing beats the old copy and paste function. Before I embark on this (at 9 am tomorrow morning... hmm that's only 8 and a half hours away) *yawn* I might have a play around to see if there's an "import file as new sheet" function... but failing that I really will have to hit the copy and paste

Thanks everyone

[allender]

Darn, you made me look, and there it was. I never used it before.

Open your "Target File". Open File 2 that you want to move over to it. Select "move or copy sheet". There's a little pull-down menu that lets you move or copy the sheet to any other Excel file that's already open!

It will even move graphs over if you created them on the File-2 sheet.

You should tick the "create a copy" box, or be careful not to save File 2, to save your data in the case of fumble fingers.

J

[Roz]

I finished working and collating the data into 12 excel files this evening (from 244 .dat files to start with) and immediately emailed it to 2 of my email addresses and to one of Mark's and even one of my Mum's.

Given half the chance I'd have emailed it to all 350 forum members for safe keeping

But yes - this data took me 12 months and 30 x 4 hour drives plus some wading in some pretty nasty mud to get hold of (not to mention the blood I must have sweat to get the sensors to record to the field loggers in the first place).

I am taking NO chances with it!

Thanks Jeff, that's excel to "look forward to" in the morning at least I know it's possible now

[Geek Boy]

Hi Roz,

I was going to say the same thing as Allender.  Selecting move or copy then ticking "create a copy" will only do it to the same workbook but if you open an existing spreadsheet or create a new one and then do move or copy, it says "move selected sheets" at the top of the little pop up box and "to book".  Click the drop down menu and you can select the other open workbook.

[Roz]

Yes it really was an excellent technique - well spotted you two. That made sure I didn't copy anything and miss a line or do anything else equally stupid - thanks