1

How do you create formulas that will return the totals, averages, counts, min, max, etc from different worksheets? The columns (that exist) are consistent in their header information, but may or may not be different from one sheet to the next, column number wise. Let's say I have 7 tabs of data, some or all of them may contain a column heading of beans, for example, but the column numbers may or may not be consistent across all tabs, where present. Their actual locations within the worksheet are completely dynamic.

My spreadsheet has several tabs of raw data that I want to compare against in a summary/report sheet. I'm sure this can be done but I am afraid it is a little too far beyond the capability of this old brain.

I've thought about and toyed with a bunch of formulas using indirect, index, match and just can't figure it out. Your help would be most appreciated. Thanks

L42
  • 19,427
  • 11
  • 44
  • 68
user3752758
  • 35
  • 6
  • If each sheet data reside in a `table`, that will be easy. If not, it can be done by Index + Match combination. Or you'll need to automate via `VBA`. It is hard to actually help you get what you want without seeing where you are currently. – L42 Jul 12 '17 at 03:29
  • Not familiar with tables in Excel. Still using Excel 2000 which I probably should have pointed out. Automating in VB seems like it would be a lot more work, so I'd like to stick with functions to solve this. Thanks for your input. One of the problem with the functions, for me anyhow, is partly the concatenation required, and also the intricacy of having to nest so much. My old brain doesn't handle that kind of stuff well anymore. Sucks getting old! lol – user3752758 Jul 12 '17 at 04:51
  • Why not just paste a link to the info you need to bring it into the current workbook then deal with it there? Ok, it may not be the shortest / sweetest most elegant solution but it may be a clearer solution. If it works... Some of us (me...) are not programmers and just need a solution. – Solar Mike Jul 12 '17 at 05:59
  • Well, the goal is to have a summary sheet which does a comparison on each of the sheets for trend spotting among other things. I think it's best to keep the data separate from the reporting for ease of maintenance. Once the formulas are figured out it should all be a piece of cake. Actually, for an excel wiz this should be a piece of cake anyhow which is why I am here. lol. – user3752758 Jul 12 '17 at 06:28
  • `Sucks getting old` Is it that bad? I'm a little worried now since we're all going there lol. Anyways, posted a possible solution but I did this in Excel 2013. If the functions used exist in 2000 then we're all good (I haven't had time to research what functions are available in 2000, my bad). But I hope it gets you going. – L42 Jul 12 '17 at 06:52

1 Answers1

2

Here's a possible function only solution. The final result will look like:

enter image description here

We will use four(4) primary functions:

  1. ADDRESS
  2. INDIRECT
  3. MATCH
  4. LEFT

Note: Just follow the link if you have question on how to use these functions

The formula: This is in cell C4 Above

=AVERAGE(
         INDIRECT(
                  "'"& C$3 & "'!" &                                          
                  LEFT(ADDRESS(1,MATCH($E$2,INDIRECT("'"&C$3&"'!1:1"),0),4,1),1)
                  & ":" &
                  LEFT(ADDRESS(1,MATCH($E$2,INDIRECT("'"&C$3&"'!1:1"),0),4,1),1)
                  )
        )

Edit1: To handle the comment below for headers beyond 26, change this

LEFT(ADDRESS(1,MATCH($E$2,INDIRECT("'"&C$3&"'!1:1"),0),4,1),1)

to this:

SUBSTITUTE(ADDRESS(1,MATCH($E$2,INDIRECT("'"&C$3&"'!1:1"),0),4,1),"1","")

Basically what is does is:

  • Find where the header Bean is located using MATCH function. To do that, you will have to incorporate the use of INDIRECT since your sheet name is dynamic (in this example, your sheet name is in C3 - Data1). (I assumed all your headers are in 1st row, I use 1:1 in there). Note: In this example, Bean is in column H of Data1 sheet

    MATCH($E$2,INDIRECT("'"&C$3&"'!1:1"),0) <~~ returns 8

  • After finding where it is, we need to get the entire column address so we can apply counting and summing functions (e.g. SUM,COUNT,AVERAGE etc...). To do that we use the ADDRESS function combined with LEFT to get the column letter.

    LEFT(ADDRESS(1,MATCH($E$2,INDIRECT("'"&C$3&"'!1:1"),0),4,1),1) <~~ returns H

  • Then we just repeat that formula and connect it with : to get H:H

  • Now that we have the column address, we simply connect it again with the sheet name and apply the final INDIRECT function plus the counting and/or summing function of your choice.

Sample formula of Total in C5:

enter image description here

Sample formula of Max in C6:

enter image description here

Finally, all you have to do is copy the formula across all columns with sheet name.
Just add sheet names beside the last one and copy formula. That's it. I hope this is close to what you want and I hope that all the functions I used are available in Excel 2000.

L42
  • 19,427
  • 11
  • 44
  • 68
  • You sir are a gem. A true credit to the human race! Thank you so much for all of your effort. It works perfectly. I appreciate the thought you put into breaking everything down as well. I'll refer to this in the future for sure. Thanks again, and take care!! – user3752758 Jul 12 '17 at 07:36
  • @user3752758 Glad it worked for you. Btw, [this is how we say thanks in Stackoverflow](https://stackoverflow.com/help/someone-answers) :) – L42 Jul 12 '17 at 07:42
  • for some reason it won't let me upvote your answer, though I was able to mark it as the answer. I had mistakenly upvoted my own post (there's the age thing again!!). NOt sure if it has anything to do with it though. Any ideas? – user3752758 Jul 12 '17 at 07:55
  • @user3752758 Ah you need at least 15 [reputation](https://stackoverflow.com/help/whats-reputation) to upvote. You hava 8 reputation now. – L42 Jul 12 '17 at 07:57
  • Nice tutorial material. – Gary's Student Jul 12 '17 at 12:09
  • 1
    Everything is working now. I had to make an adjustment to the formula though. Anytime a column was >Z it returned invalid and unpredictable results. I pre-pended an if test to check if the column was > 26, and then inserted the formulas for each. Other than that the answer was perfect, and through this exercise I am getting more comfortable using the formula's components. It has been a great timesaver and learning experience. I can't thank you enough! – user3752758 Jul 13 '17 at 04:22
  • new formula if anyone is interested... one or both reference cells were modified...=IF(MATCH($A$3,INDIRECT("'"&C$3&"'!1:1"),0)<26, AVERAGE(INDIRECT("'"&C$3 & "'!" & LEFT(ADDRESS(1,MATCH($A$3,INDIRECT("'"&C$3&"'!1:1"),0),4,1),1) & ":" & LEFT(ADDRESS(1,MATCH($A$3,INDIRECT("'"&C$3&"'!1:1"),0),4,1),1))), AVERAGE(INDIRECT("'"&C$3 & "'!" & LEFT(ADDRESS(1,MATCH($A$3,INDIRECT("'"&C$3&"'!1:1"),0),4,1),2) & ":" & LEFT(ADDRESS(1,MATCH($A$3,INDIRECT("'"&C$3&"'!1:1"),0),4,1),2)))) – user3752758 Jul 13 '17 at 04:35
  • just a correction - - the if test should be <27 , not 26. Not sure why it wouldn't let me edit it.. – user3752758 Jul 13 '17 at 10:15
  • 1
    @user3752758 great job, If you have `SUBSTITUTE` function available thought, you can use that instead of `LEFT`. See my edit. My bad for not anticipating your row data would go beyond the 26 single letter column mark :). – L42 Jul 13 '17 at 23:36
  • thanks. When I get a little time I'll check that out. There are so many functions that it is impossible to keep up with them all, unless maybe if you work with this stuff every day! It's always challenging! Thanks again!! – user3752758 Jul 14 '17 at 16:21