Merging sheets within a single workbook

[vampcatt]

I have a document composed of 17 identical sheets (same exact fields, just different data in each sheet).
I want ONE worksheet, I want to merge ALL the sheets into one ( very long!) sheet.

HOW can I do this?

ACTUALLY.. I ALSO have all 17 sheets, saved separately, if there is a way to merge all of them from separate files, into ONE file, that would be even better!
Either way, same result.

Thanks in advance for the help!

[ This Message was edited by: vampcatt on 2002-02-23 00:51 ]

[Anne Troy]

Merge? Do you mean you want to SUM all of the data in all of the 17 cells, for instance B2, of all of the worksheets?

If that's the case:

Click on B2 of the *summary* sheet, which MUST be the first or last sheet of the workbook. Type =sum(
Then, click on the sheet tab of the first worksheet you're adding from, hold your shift key, click on sheet tab of last worksheet you're adding from, now click on cell B2. Type a ) and hit enter. It will look something like:

=sum(sheet1!:sheet5!B2)

[vampcatt]

Actually. I want all documents to retain all the original data, but I want it all in ONE page.
Maybe I should give an example, lets say they were pages with sample fields like :

Name Age Location Number

Lets say that EACH page is set up EXACTLY the same, no variances, except the text.
Basically.. instead of having 1-200 entries downthe page EACH..
I'd rather have 1 - 2800 entries in one page.

(If there were only 4 fields, this wouldnt be that hard to cut and paste, but the fields are from A - AR) so.. its quite large.)

I am probably wording this funky, not conveying exactly what I am trying to say..
BUT.. instead of separate files, I want them all in one huge file.

Help.. I know there has to be a way to do this, other than the manual "cut and paste" that would take forever.

Thanks!

[Anne Troy]

Actually, perhaps you're not aware of certain shortcuts. Try this:

Open one of your worksheets.
Click on cell B1.
Hit Shift + Ctrl + Right arrow. This should select all of your first record.
Now, hit Shift + Ctrl + Down arrow. This should select all of your records.
Copy.
Go to main workbook, click on A1. Hit Ctrl-Down arrow. Hit Down arrow once more. Hit Paste.
If this does not work, perhaps your worksheets are not set up in an efficient manner? (No offense intended!)

[vampcatt]

That seems to work just as hitting the corner square ( highlighting ALL cells) and cutting and pasting it .

I DID combine all 17 files into one workbook, as separate sheets, BUT.. this is going to be a progressing task.

I will do this once a week probably, and if I can avoid cutting and pasting 17 + entire documents into one file, that would be great.
If there isnt a way to just merge different documents together, then I will just ahve to do manual cut and pastes..

thanks!

[vampcatt]

I think that will work well enough, thank you for the new shortcuts.

I have another question
I don't know if I should start a new thread, or just ask in here, since you've been pretty good about replying

Is there a way ( other than "find/replace" ) to change decimals to letters? )

What I have is 7 columns of numbers.
and 0.1 0.2 0.3 = P
0.4 0.5 0.6 0.7= BA
Is there a way to tell excel that certain ranges of numbers equals a certain letter value?

Thank you so much !

[Rocky E]

You could use VBA, but if the list can be ordered, or sorted you can use VLOOKUP or HLOOKUP or INDEX to find strings or numbers. With and ordered list if will return predictable results even without an exact match. I use this to look up prices all the time. The price table is based on quantity. For example 9, 99, 999, 9999 If you do a lookup for 300 you will get the price associated with 999 witch of course this the 100 to 999 price.

HTH

Rocky...

[ This Message was edited by: Rocky E on 2002-02-23 21:39 ]