Are you good at excel? I need some help consolidating sheets...

[Bioture]

Hi there.

I'm currently faced with a rather simple challenge in excel 2000 that I can't seem to do. Here is what I have:

- I have one workbook with multiple sheets
- One sheet is the compliation of all entries, the other 3-4 "standalone" sheets contain information that belongs on the compilation sheet. (needs to be added to the bottom)
- the number of entries on all the pages are dynamic
- there is a header on every page that is 7 cells in width.

Here is what I need:

- An automated way (i.e. similar to merge workbooks) where I can take the data entered from the standalone sheets and add them to the end of the main compilation sheet, or a macro that serves such a purpose (i don't know vb at ALL so whatever this is has to be recorded/simple)

- OR consolidate the 3-4 standalone sheets to another blank sheet where all the information is consolidated, so its easier to manually copy/paste the info onto the main compilation sheet.

So basically I have a workbook with worksheets (1), (2), (3), and (4).
worksheet (1) has rows of information - 1, 2, 3, 4
worksheet (2) has rows of information - 5, 6
(3) has rows of information - 7, 8, 9, 10
worksheet 4 (compilation) should have 1,2,3,4,5,6,7,8,9,10 - but compiled automatically...


I hope someone out there knows what i'm trying to do. its such a simple concept I dont know why I can't find an answer to doing it...

[Mr. Peanuts]

I've just done a whole project for ICT in Excel and fucking hated it.

I think I know what you're trying to do, I used a similar method in my project.

You can use a Cell Reference, it just returns the value entered in a field on another worksheet.

Say in one field on the compilation, you wish to have a cell from sheet2, you would enter this in the field: '=Sheet2!B4' - This looks into Sheet2 and returns the value in cell B4.

If you want to do it in a more brainless way, just select the cell you want on the compilation sheet and hit '='. Then go into the other worksheet and select the cell you want it to return.

I hope that helps.

[Bioture]

thanks peanuts! That did help a little bit - however, the number of entries on each of the standalone sheets varies from time to time (depending on update) so is there a way for me to append the entries one after another, sheet after sheet without white spaces or overlap, which results in lost data?

edit: lets say i want all the rows from 7 until a "a blank row" from sheet 1 to be appended from "a blank row" until all data is copied on sheet 2?

hope this makes sense

[Mr. Peanuts]

Do you mean a row on the comilation sheet remains blank until new data is added into the relating row on the other sheet?

I'm not sure if that is possible without going into using VB code which is something I only use when making buttons on my sheets so I'm not too good with it.

[Bioture]

ok I just figured out the consolidation thing... how do I make a group of cells locked so they can't be deleted/copied etc?

[Mr. Peanuts]

Edit: Select the group of cells you wish to allow users to modify, go to tools > protection > allow users to edit ranges. Make a range, give it a good title so that you know what the hell it is and click Ok. Then go to tools > protection > protect sheet.

The protection will exclude the cells you selected.

I'm sure there's another way but I cant remember :/