Consolidating Columns from Multiple Sheets

[The Phil]

After the help I've gotten in other threads I'm almost done my project!!! I will end up with multiple sheets that automatically pull data from a master budget. Each sheet will vary in length and will allow for automatic detection of new accounts on the master budget. The reason for having separate sheets for each section is to that I can quickly verify the totals for each section in case the automated list doesn't balance. Now what I want to do is make a sheet that consolidates all of the other sheets. I included a simple attachment to show you what I am trying to do. I made some attempts at variations of INDEX and a few other things but I could not get it to work.

Any suggestions?

Thank you.

[JBeaucaire]

If there will be more than 1 or 2 data sheets, I would use a macro so that it self-expands. Is that OK?

[JBeaucaire]

This is what I mean about the simplicity. This macro would cause the Consolidated sheet to update itself every time you look at it, even if you add 100s and 100s of Data sheets.

Please Login or Register  to view this content.

1) Right-click on the Conslidated tab and select VIEW CODE
2) Copy and Paste in your code (given above)
3) Get out of VBA (Press Alt+Q)
4) Save as a macro-enabled workbook

Now work on all your data sheets, and each time you look at the Consolidated sheet, it will be current.

[The Phil]

WOW thanks. It's the end of the day so I'm gonna go home but I'll give that a try on Monay

[martindwilson]

well its sunay here but monay will be good lol

[somesoldiers]

well its sunay here but monay will be good lol

it's only satay (has that got peanuts in) in Ireland..well we normally see what Britain does and follow suit so I'll expect an update from the government anytime soon....***waiting with bated breath***

[The Phil]

The code seems to work like I asked but there are 2 tweaks that would be helpful.

Is it possible to make it pull in Columns A + B only?

Also, since the self populating sheets (not the consolidated one) have formulas that extend down past where the data itself manifests, the consolidation sheet has a lot of white space between the data that it pulls in. Is it possible to eliminate that?

Thanks!!!

[JBeaucaire]

Are columns A and B filled in on all rows of concern? Not some rows have "A" but no "B", all the rows you want have both "A" and "B"...
...or even simpler, do all the rows you want at least have an "A" value? We can grab all cells that have values in column A and their partner cell in "B", too.

Are the values text? Numbers? Both?
Are ALL the values the results of formulas?

A sample sheet with before/after assistance makes this much simpler.

[The Phil]

Are columns A and B filled in on all rows of concern? Not some rows have "A" but no "B", all the rows you want have both "A" and "B"...
...or even simpler, do all the rows you want at least have an "A" value? We can grab all cells that have values in column A and their partner cell in "B", too.

Are the values text? Numbers? Both?
Are ALL the values the results of formulas?

A sample sheet with before/after assistance makes this much simpler.

When I refer to the budget spreadsheet/workbook it is refering to a different workbook.

The end result will be a master list of accounts for a general ledger in row A with their totals from the budget workbook in row B. Therefore, every value in row A will have a corresponding value in row B. It will never be just column A or B in a row, it will always be both. So yes, you can grab every value in column A and its' partner value in column B as well.

Values in column B are always numbers. Values in column A are sometimes numbers and sometimes text. It's something I'm trying to standardize in the master budget spreadsheet but at this point column A could be either.

ALL of the values in column A and B are from formulas. Those formulas may point to that same worksheet that they are on, or they may point to the budget workbook.

There is also a "Controls" spreadsheet where I enter variables. For example, all of the formulas refer to a named cell for the location of the budget workbook. So in the middle of the VLOOKUP and MATCH functions, instead of an address I have INDIRECT(FDR) or something of the sort, where "FDR" is a formula that combines the variables from my control sheet to give me the directory, file name, sheet name, and data range that I want to access. That way if I ever have to change what file I'm looking in, or if the range of values is changed, I only need to change it in one place. That particular sheet has columns A and B left blank.

I guess the easiest way to sum up what I want is; Can the consolidation sheet do the equivalent of cutting and pasting the "values only" of columns A and B from all of the other sheets as values, with no white space? In the end I will save it as a tab delimited file which will give me values only, so I can bring in either the formulas or the values themselves, it makes no difference to me.

If you still need an example sheet let me know. I can't just upload our budget but I can cut and paste some parts of it to give you an idea.

Thanks!

[The Phil]

UPDATE!!!

It would actually be columns A, B and C that I would need. Only C would be numbers, the others could be numbers or text. And they would all be filled on the rows, there would be now rows with only 1 or 2 of those columns.

Sorry, just realized it.

[JBeaucaire]

I guess the easiest way to sum up what I want is; Can the consolidation sheet do the equivalent of cutting and pasting the "values only" of columns A and B from all of the other sheets as values, with no white space?

Yes, not a problem, I don't imagine. A fully demonstrative sample workbook should make it simple to devise.

I can't just upload our budget but I can cut and paste some parts of it to give you an idea.

It shouldn't take that long to dummy down a copy of the workbook, stripping out the read sensitive info leaving dummy data and a working layout.

[The Phil]

A fully demonstrative sample workbook should make it simple to devise.

If you save the attached "Budget Example" into C:\Temp2 then it will work. You need to open the budget example file first, (don't need to update it) and then open the autoit file and update it. Everything about it should be automatic, with the exception of the ability to change the variables on the Controls sheet. The consolidation sheet has the code you posted earlier, but it doesn't seem to get values properly from "Parks". To restate what I want, I need the consolidated sheet to have the values of columns A, B, and C only, with no white space between. Having them all come in as numbers would be great but if that's too much work and some come in as text I can always just copy and paste.

Thanks!!

[JBeaucaire]

Having the values appear as numbers mean the leading zeros will disappear.

[The Phil]

Having the values appear as numbers mean the leading zeros will disappear.

It is not necessary for the leading zero to be there. I am using this consolidated list to import the budget into our accounting software. If the activity number (column B) is 010010 then the software will also recognize 10010, so either way. It does have to be a number though because I will be saving it as a tab delimited file, so if it is text it puts the quotations around it and then the accounting software won't recognize it. However, if it is easier for you to help me by importing it as is, whether text or a number, then I can just cut and paste to make it numerical.

Thanks!!

[JBeaucaire]

Try this:

Please Login or Register  to view this content.

[The Phil]

You are so awesome! Works great.

When I get help on formulas I can understand what's going on and manipulate them if necessary. I'm lost when it come to macros though. Do you have any suggestions for beginner guides online?

[JBeaucaire]

Nothing specific. I Google my questions and find all the samples and examples related to the issue I'm dealing with, I do it daily. Nothing replaces good old "I have an issue to resolve" for learning new stuff.