trying to Consolidate a number of sheets into 1

[chinosai]

Hello everyone,

This forum has basically taught me everything i now know in Excel and for that I am very grateful.

I seem to have run into an issue i cant figure out and cant seem to find the answer to, although i am probably not asking google the right questions.

What i am trying to do is consolidate data from a number of different sheets on the same book, into one sheet with all the info stacked on top of each other.

All the sheets have the same titles in row 1, however each sheet will have a different amount of information and therefore a different amount of rows.. some columns may even be empty as well as a few cells scattered around.

the one constant on each sheet would be Column C. which isalso be a good indication on how many rows are being used on each sheet.

I was attempting to use a formula that will reference column C on sheet2, and if the cells in Column C are not empty, to return whatever is in Column A, onto Sheet1, A1 and fill down.

i came up with:

=IF(Sheet2!C2<>"", Sheet2!A2)

I type the above into A2 on sheet1 (as A1 will have the title) then i drag down.

i then do the same thing with B2 in sheet1 and adjust the formula to

=IF(Sheet2!C2<>"", Sheet2!B2)

and so on.

this works great for sheet 2. but what I want it to do, is start to reference the top of Sheet3 once the C column in sheet2 shows a blank.

I thought this would work:

=IF(sheet2!C2<>"",sheet2!A2,IF(sheet3!C2<>"",sheet3!A2))

but by the time i get down to where column C is showing a blank in Sheet2, it is returning the information on sheet3 from Row130 instead of the top.

is there any way to make it reference the top? or is there a completely different formula all together?

I understand there might be a way to use Macro to achieve this, but i am not very good at macros and doubt id be able to fix anything if it broke, or recreate it if i needed to.

Any help would be very much appreciated.

Thanks in advance,

James

[bebo021999]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[sandy666]

[duplicated from bebo]

[FlameRetired]

Hi chinosai. Welcome to the forum.

How many sheets are there. What is the maximum number of rows anticipated?

[chinosai]

Thank you all for your replies.

Hi Bebo,

as requested, I have attached(I think i did it right) an example of what I am trying to achieve with some additional comments.

Hi FlameRetired, Thanks for the welcome.

there will be 8 Sheets with 38 Columns in each and the rows will range from anything between 20 rows to 350 rows. the Data in the 8 sheets will change daily and so
I am hoping that the formula in the "master sheet" will be able to handle dynamic data.

Thanks again all and please advise if any further details are required.

regards,

chinosai

[sandy666]

here is your example maded with PowerQuery (free add-in from MS for Ex2010) - no formulas, no VBA, only 5 seconds of your time

[MarvinP]

Hey Sandy,

You're giving Power Query a bad name. In testing your answer, it has only 88 rows instead of the 150 (approx.) it should have. Try doing your problem again and check your answer. Did you get all the rows in the "all" sheet? Take more than 5 seconds to make sure the answer is correct, please.

[sandy666]

Hi Marvin
Did you see on sheet1 how OP want to see OP's data consolidated?

edit
sheet1 is a result - not part of consolidation

[bebo021999]

I try to use helper columns from column 39 (right of data range)

See file attached, sheet "all"

[MarvinP]

OK - you're right. I thought the sheet1 was supposed to be part of the "all" sheet. BUT - it took more than 5 seconds to do your PQ answer.

[sandy666]

Use keyboard shortcuts, will be faster

[chinosai]

Thanks Bebo,

This is what I wish i could do. I was looking through your example and find it hard to understand what is happening. if you ever have a spare moment, are you able to give a brief(or lengthy) explanation as to how it works?

In the meantime, i have installed Power Query as suggested by Sandy and its working perfetly.

Thanks again for your response.

regards,

[chinosai]

Thank you Sandy for your suggestion.

I got Power Query and its working perfectly. cuts down the time it took me to run this report by quite a bit.

Still need to figure out these keyboard shortcuts.

your response is very much appreciated.

Regards,

Chino

[sandy666]

Alt, A, PT (it may be a different on Ex 2010, but usual shortcuts are the same)
but first you need to reconfigure PQ setup to "only create connection" as default
the rest is just click, click, and click and you must be faster than popup windows
switch between tabs: Ctrl, PgUp

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it - ignore it.
Thank you.