Random Excel question

[Sam_2.0]

Google wasn't much help, so I will ask you all.

I have a workbook with a tab for every workday of the year. Unfortunately the data isn't consolidated Is there an easy way to pull from every sheet within a workbook without naming each individual sheet? If I have to go that route I will, especially since we can then copy it to use for other years, but would like to know if there's an easier way around it first

- all worksheets are in the same format, so I am pulling the same range on each sheet (they copy each day to make the next)
- I tried using the "consolidated" function, but that is requiring the name of each tab as far as I can tell

[Pants]

Sam, I don't know of a way to do it without specifying each sheet. Sorry!

[Abby Normal]

Instead of using multiple sheets, would it work to create a pivot table?

[Pants]

I imagine it can be done, but you'd probably have to write a macro for it. I've never gotten into excel at the VB level, so I can't help, but there's probably a way to make it work.

For pivot tables, you have to specify the data pull.

[The Captain]

Alpha:Omega

Works only if the data is aligned exactly the same on each tab in the workbook.

Create two blank tabs.  Name one "alpha" and put it at the beginning of the workbook.  Name the second blank tab "omega" and put it at the end of the workbook (so all the populated tabs are in between the alpha and omega tabs)

Now right  click on any of the tabs in between the two, select move or copy, make a copy and move it to the beginning of the workbook.

Name the tab that you just copied "consolidation" or something else meaningful.  You now have your formatted tab

In every cell where you want to add all the data across all the tabs enter the following formula:

=SUM(ALPHA:OMEGA!K21)

(Where K21 is the cell you want to add all the data).

I consolidate 130 entities in excel doing this. 

I hope this helps.  Let me know if it works for you.

[Abby Normal]

Jan 14, 2015 18:16:41 GMT -5 The Captain said:

Alpha:Omega

Works only if the data is aligned exactly the same on each tab in the workbook.

Create two blank tabs.  Name one "alpha" and put it at the beginning of the workbook.  Name the second blank tab "omega" and put it at the end of the workbook (so all the populated tabs are in between the alpha and omega tabs)

Now right  click on any of the tabs in between the two, select move or copy, make a copy and move it to the beginning of the workbook.

Name the tab that you just copied "consolidation" or something else meaningful.  You now have your formatted tab

In every cell where you want to add all the data across all the tabs enter the following formula:

=SUM(ALPHA:OMEGA!K21)

(Where K21 is the cell you want to add all the data).

I consolidate 130 entities in excel doing this. 

I hope this helps.  Let me know if it works for you.

DAYUM! Brilliant.  

[Sam_2.0]

Jan 14, 2015 18:16:41 GMT -5 The Captain said:

Alpha:Omega

Works only if the data is aligned exactly the same on each tab in the workbook.

Create two blank tabs.  Name one "alpha" and put it at the beginning of the workbook.  Name the second blank tab "omega" and put it at the end of the workbook (so all the populated tabs are in between the alpha and omega tabs)

Now right  click on any of the tabs in between the two, select move or copy, make a copy and move it to the beginning of the workbook.

Name the tab that you just copied "consolidation" or something else meaningful.  You now have your formatted tab

In every cell where you want to add all the data across all the tabs enter the following formula:

=SUM(ALPHA:OMEGA!K21)

(Where K21 is the cell you want to add all the data).

I consolidate 130 entities in excel doing this. 

I hope this helps.  Let me know if it works for you.

Thank you!!! I knew someone would have a trick. You are my hero

[Pants]

I bow down. Teach me more, oh wise mistress of the tabs!

[taz157]

Jan 14, 2015 21:08:36 GMT -5 Pants said:

I bow down. Teach me more, oh wise mistress of the tabs!

[Sam_2.0]

Jan 14, 2015 21:08:36 GMT -5 Pants said:

I bow down. Teach me more, oh wise mistress of the tabs!

We totally need a sticky thread somewhere for Excel help!

[The Captain]

(Blushing) glad to be able to help. Truth is I used to be wicked good at excel but sadly have gotten rusty as I've spent less time doing and more time reviewing.

Let me know if it works for you.

[Pants]

Jan 14, 2015 23:33:30 GMT -5 The Captain said:

(Blushing) glad to be able to help. Truth is I used to be wicked good at excel but sadly have gotten rusty as I've spent less time doing and more time reviewing.

Let me know if it works for you.

Perhaps I should have said:

Oh Captain, my Captain!

[Sam_2.0]

It worked perfectly, The Captain. Thank you so much!!

[Lizard Queen]

Jan 15, 2015 9:10:08 GMT -5 Pants said:

Jan 14, 2015 23:33:30 GMT -5 The Captain said:

(Blushing) glad to be able to help. Truth is I used to be wicked good at excel but sadly have gotten rusty as I've spent less time doing and more time reviewing.

Let me know if it works for you.

Perhaps I should have said:

Oh Captain, my Captain!

-rofl-perfect!



( and  Robin Williams)

[The Captain]

Jan 15, 2015 9:14:14 GMT -5 Sam_2.0 said:

It worked perfectly, The Captain. Thank you so much!!

I am so glad to hear that!  Thanks for letting us know.

[The Captain]

Jan 14, 2015 21:08:36 GMT -5 Pants said:

I bow down. Teach me more, oh wise mistress of the tabs!

"Approach students. Close the circle at the feet of the master. You have come to me asking that I be your guide along the path of tae-kwon-leep, but be warned. To learn its ways, you must learn the ways of your own soul. Let us meditate on this wisdom now. "

(If you don't get this reference you really need to google it and watch the youtube video...