topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Thursday March 28, 2024, 5:45 am
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Excel help: Showing multiple, seperate ranges on top of one another  (Read 12061 times)

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
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

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,984
    • View Profile
    • Donate to Member
Re: Excel help: Showing multiple, seperate ranges on top of one another
« Reply #1 on: November 06, 2008, 06:42 PM »
ASAP utilities will allow you to do this... and it's FREE! Free is good...

ASAP Combine Sheets.pngExcel help:  Showing multiple, seperate ranges on top of one another

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: Excel help: Showing multiple, seperate ranges on top of one another
« Reply #2 on: November 06, 2008, 06:48 PM »
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

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,984
    • View Profile
    • Donate to Member
Re: Excel help: Showing multiple, seperate ranges on top of one another
« Reply #3 on: November 06, 2008, 06:53 PM »
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

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,984
    • View Profile
    • Donate to Member
Re: Excel help: Showing multiple, seperate ranges on top of one another
« Reply #4 on: November 06, 2008, 06:58 PM »
Sorry, superboyac. The addin is Spreadsheet Assistant  :-[ Works a treat, too, but it's pricey.

Darwin

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,984
    • View Profile
    • Donate to Member
Re: Excel help: Showing multiple, seperate ranges on top of one another
« Reply #5 on: November 06, 2008, 07:00 PM »
Here Spreadsheet Assistant is:

Spreadsheet Ass.pngExcel help:  Showing multiple, seperate ranges on top of one another

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

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: Excel help: Showing multiple, seperate ranges on top of one another
« Reply #6 on: November 06, 2008, 07:20 PM »
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

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: Excel help: Showing multiple, seperate ranges on top of one another
« Reply #7 on: November 10, 2008, 07:17 PM »
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

  • Supporting Member
  • Joined in 2008
  • **
  • Posts: 134
    • View Profile
    • Donate to Member
Re: Excel help: Showing multiple, seperate ranges on top of one another
« Reply #8 on: November 11, 2008, 12:55 AM »
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.
I'll have what she's having

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: Excel help: Showing multiple, seperate ranges on top of one another
« Reply #9 on: November 14, 2008, 03:34 PM »
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:
=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:
=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:
=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

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: Excel help: Showing multiple, seperate ranges on top of one another
« Reply #10 on: November 14, 2008, 03:39 PM »
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

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Excel help: INDEX function referencing a named range
« Reply #11 on: November 14, 2008, 05:07 PM »
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?
« Last Edit: November 14, 2008, 05:10 PM by superboyac »

city_zen

  • Supporting Member
  • Joined in 2008
  • **
  • Posts: 134
    • View Profile
    • Donate to Member
Re: Excel help: Showing multiple, seperate ranges on top of one another
« Reply #12 on: November 14, 2008, 10:00 PM »
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  :D  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
=INDEX(INDIRECT(G2),1,1)

Let us know if that solves the problem


I'll have what she's having
« Last Edit: November 14, 2008, 10:14 PM by city_zen »

superboyac

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 6,347
    • View Profile
    • Donate to Member
Re: Excel help: Showing multiple, seperate ranges on top of one another
« Reply #13 on: November 14, 2008, 10:56 PM »
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.