Excel help: Showing multiple, seperate ranges on top of one another

[superboyac]

I can't figure out how to do this.  I have an Excel Workbook with multiple sheets.  They all have the same columns, but the number of rows is different in each column.  I'd like to, in a separate sheet, put all the rows from all the sheets on top of one another in one long list.  How do I do this?  I want the big list to be live-linked to the other cells, I don't want to simply copy them.  Any changes to the original cells will be shown in the big sheet.  The big sheet is simply a summary sheet for display purposes.

The ranges in each sheet is named using the Named Ranges feature.  And I've somehow figured out how to make the ranges dynamic (I don't remember how).  That means whenever a row is added to a sheet, the named range automatically adds that row to the range.

I tried using pivot tables, but I'm not very advanced with them.  I also tried consolidating the ranges.  The problem with both of these seem to be that it assumes you want to do some calculation on the data, like summing them or averaging them.  I don't want to do that.  I simply want to just copy the cells and combine them all in one big list.  No calculation, or function or anything like that.

Thanks!!

[Darwin]

ASAP utilities will allow you to do this... and it's FREE! Free is good...

[attachthumb=#]

[superboyac]

Oh sweet!  Thanks Darwin!  Yeah, I use ASAP a lot, I guess I didn't look hard enough...there are so many things after all!

[Darwin]

Crumb! The tool has been changed (and/or so has Excel 2007) - I used to use this feature a lot to combine different sheets but the option to have the columns stacked or added to the side has been removed and now you just get a sheet with the sheets added to the right... I'll investigate further as I know I've done this.

[Darwin]

Sorry, superboyac. The addin is Spreadsheet Assistant  Works a treat, too, but it's pricey.

[Darwin]

Here Spreadsheet Assistant is:

[attachthumb=#]

You can see the combined result in the background... bit of a rush job on my part - off to dinner!

[Target]

looks like one answer is paste special - copy the records in the first sheet then go to the target sheet and do paste special .  There's a LINKS button at the bottom left of the dialog box

obviously this is a manual process, and you'll need to do it for each sheet in the book, but it should get you started...

One potential issue is if the number of records within each sheet increases then those changes won't be reflected (if it decreases you will get blanks...)  

[superboyac]

Is there a way to use pivot tables to take all different named ranges and combine them all into on pivot table?  I assume it would use the "consolidate multiple ranges" in the pivot table options.  However, is there a way to set up pivot tables so that they don't do any kind of calculations on the data (like sum, count, average, etc.) but instead, just lists the contents of the data?

[city_zen]

Is there a way to use pivot tables to take all different named ranges and combine them all into on pivot table?  I assume it would use the "consolidate multiple ranges" in the pivot table options.  However, is there a way to set up pivot tables so that they don't do any kind of calculations on the data (like sum, count, average, etc.) but instead, just lists the contents of the data?

I'm not sure if this is what you want to accomplish, but this page looks relevant. As an alternative method, they suggest using MS Query to combine multiple named ranges from an Excel file.

[superboyac]

One more question if anyone can help:
I have a named range, called "vh".  The named range itself is a dynamic range, using a formula like this:

Code

=OFFSET('Vahik Haddadian'!$A$2,0,0,COUNTA('Vahik Haddadian'!$A:$A),5)

Now, I want to reference individual cells in this named range using the Index function, like:

Code

=INDEX(vh,1,1)

And that works fine.

However, in that index function, I want to generate the "vh" part using a reference instead of directly typing it in:

Code

=INDEX(G2,1,1)

where G2 is the cell whose content is "vh".
The problem is that when the "vh" argument is referenced, it doesn't work properly.  Only when "vh" is directly typed does it work properly.  Can anyone help me figure out why?

[superboyac]

Oh I know what the problem is.  The index function is assuming G2 is the range.  But I want it to assume that the content of G2 (vh) is the name of the reference to the range I want.  Ah!  How do I do that?

[superboyac]

I can't seem to figure this out.  I'm using the INDEX function but instead of typing the named range in the argument directly, I'm using a formula to do it (vlookup).  So I checked the formula by auditing, and the problem is this:  instead of calculating INDEX(vh,1,1) which would be correct, it ends up with INDEX("vh",1,1).  How can I do it so the quotation marks don't appear?

[city_zen]

Have you tried nesting the functions?
i.e. =INDEX(OFFSET('Vahik Haddadian'!$A$2,0,0,COUNTA('Vahik Haddadian'!$A:$A),5),1,1)

Not blindingly elegant, mind you, but maybe effective

Update: took me a while, but I think I've just understood the problem    Ignore my previous post

I think you may be missing an INDIRECT function somewhere there, so that G2 gets evaluated.
The formula would probably have to be

Code

=INDEX(INDIRECT(G2),1,1)

Let us know if that solves the problem

[superboyac]

You know, I thought of the indirect function also, and I tried it and it didn't work.  I have no logical explanation for it.  Like you, I could have sworn that was the right solution.  More on this Monday when I'm back at work.