Excel 2007 : compile a list from data on other sheets in the same workbook

[aff219]

What I have:
a workbook comprised of 12 sheets, "Totals", "First" (hidden placeholder sheet), sheets labeled 1-10, and "Last" (hidden placeholder sheet)

Each sheet (excluding first and last) have the same layout. sheets 1-10 hold data (inventory list) in (potentially) B10-B500. Some sheets only contain data in B10-B30, others from B10-B100, etc.

For each inventory item, when an item has been sold, it is marked with a value at the next column over.

Example:
(B10) Product 1 | (C10) *BLANK* |
(B11) Product 2 | (C11) 1 |
(B12) Product 1 | (C12) 1 |
(B13 Product 3 | (C13) *BLANK* |

What I want:
for the "Totals" sheet to use B10-Bxxxx to list out the data in B10-B500 from all sheets between "First" and "Last" that do not contain data in the adjacent C column,and it should also remove blank cells OR know to stop listing from that sheet once it reaches a blank B cell and go to the next sheet to continue process until its done.

It should also be able to automatically re-build this list when something is added or removed

Bonus wants:
-if it could list them alphabetically that would be awesome but is not expected.

-if it could merge similar items together with an inventory count for merged items in the next column over.
For example, if i have Product 1 listed on sheet 1 and 2, instead of it listing "Product 1" in B10 and B11 in the totals, to read [on the "Totals" sheet] "Product 1' in B10, with the value of "2" in C10.


I've been researching this for a good 4 days now, it looks like my only option is VB code, but I'm pretty crappy with that at this point so I figured I'd ask for help

thanks in advance

[aff219]

Desperate bump for help

[Kyle123]

Can you post a sample workbook so we can have a look? It should contain before and after (as in what you want it to look like)

[aff219]

Can you post a sample workbook so we can have a look? It should contain before and after (as in what you want it to look like)

attatched is an example

[aff219]

another desperate bump T_T another 2 days of research has yielded no results worth mentioning

[aff219]

=/ bump 3 for help

[aff219]

=/ bump 3 for help

well this isnt promising at all

[aff219]

well this isnt promising at all

T_T bamps again.

i cant believe no one has an answer to grab data from multiple sheets...

[Miraun]

Is there importance for the First and Last sheets? Or are they just placeholders?

[Kowgal]

I have just upgraded to Excel 2007 and not used it for many things yet. I need to find out how to take totals from four of my work sheets and put them on my Summary page and then when I'm working on those separate pages have the totals on the Summary change with each new entry.

[aff219]

Is there importance for the First and Last sheets? Or are they just placeholders?

they are jus tplace holders so when im doing formulas with the many sheets within the workbook i can easily just type First:Last instead of 1:13293747 etc

I have just upgraded to Excel 2007 and not used it for many things yet. I need to find out how to take totals from four of my work sheets and put them on my Summary page and then when I'm working on those separate pages have the totals on the Summary change with each new entry.

1. please dont hijack my thread.
2. =SUM(Sheet1:Sheet10!E2)

assuming when you said "worksheet" and didnt mean "workbook"

if its through multiple workbooks use the consolidate tab on the top ribbon, select the workbooks you plan to use and make sure to checkbox the create links option in the bottom left