• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sumifs indirect referencing a worksheet name

M

maku

New Member
There is a post in the forums that sort of addresses what my question is, http://chandoo.org/forums/topic/lookups-referencing-tab-names but I'm not sure if I should ask in that thread my question or ask separately.


There is a formula that is given that kind of works with my file, but the problem is it does not update dynamically.


=SUMIFS(INDIRECT("'"&A6&"'!O:O")


The INDIRECT is referencing cell A6 which contains the worksheet name to search the SUM RANGE of column O:O. Is it possible to syntax this so that the column O:O is outside of the " " double quotes so that if I change column O:O by inserting or deleting a column on the worksheet, this cell will update?


For example, if I long write the worksheet name in that formula like below, when I change column O:O, this formula will auto-update with the correct column. The formula with the INDIRECT referencing the cell (which is the worksheet name) won't auto-update the column O:O because it's inside the quotes.


=SUMIFS('worksheet name'!O:O,...rest of formula)


I tried the following but excel didn't find the formula acceptable: =SUMIFS(INDIRECT("'"&A6&"'!"O:O)

=SUMIFS(INDIRECT("'"&A6&"'!")O:O


thank you,

-Mark
 
If there's something in col O that we are looking for, or is it that last column being used? Either of those would help tremendously.
 
Hi, maku!


SUMIFS function has at least 3 (and preferrable 5 o 2n+1 arguments) that are:

- first, range to be summed

- second, range criteria 1

- third, criteria 1

- fourth, range criteria 2

- fifth, criteria 2

...


So using INDIRECT function as you wrote it will only be suitable to define first parameter, you're missing the needed pair second/third and eventually further pairs.


And about your question of making O:O variable, I should say that INDIRECT builds a string, so if you manage to change the reference to that column, it's possible yes. But let's go by parts, first use proper syntax for function SUMIFS.


Regards!
 
sorry, I didn't post the remainder of the formula with the constraints. Here it is:


=SUMIFS('Meadowview Estates'!O:O,'Meadowview Estates'!L:L,parameters2!$C$4,'Meadowview Estates'!K:K,'Summary Data (2)'!$B$1)


Column O:O is the data I'm retrieving. And in the above formula, if I change column O:O by inserting or deleting a column in worksheet 'Meadowview Estates'!, then column O:O will change dynamically.


Now, back to the original post and question, I'm wanting to insert the worksheet name in this formula and tried using INDIRECT. This formula works, but again, if I change column O:O, then this doesn't change because it's inside "".


=SUMIFS(INDIRECT("'"&A6&"'!O:O"),INDIRECT("'"&$A6&"'!L:L"),parameters2!$C$4,INDIRECT("'"&$A6&"'!K:K"),'Summary Data (2)'!$B$1)


Thank you for any direction! The purpose I'm doing this is, I have many worksheets and want to copy the formula rather than manually type in 50+ worksheet names.
 
Hi Mark,

Would you be able to use the INDEX function?

You could name your data range, and then use the INDEX function to get to specific columns.


For example:

=INDEX(datarange, 0, 2) returns the entire second column from datarange.


You can construct the "datarange" as a named range, or using the INDIRECT function, etc.


You could use various methods to determine which column to use... for example, instead of hardcoding the value 2, one could use COLUMN(Q1) to get the column number of reference "Q1". If you then add or remove columns, the reference would get adjusted.


Hope this helps.


-Sajan.
 
Let's say you're always looking for column with header "Total Value".


This will reference the moving column:

=INDEX(INDIRECT("'"&A6&"'!A:Z"),,MATCH("Total Value",INDIRECT("'"&A6&"'!1:1"),0))


For use in your SUMIFS, I would recommend defining this formula as a named Range and then using that in the SUMIFS function, for ease of reading.
 
@sthomas- I'm not sure that will work, because I need to constrain the data being retrieved by multiple constraints. In my example, I am pulling Column O [which is labeled debt]. But, the "debt" changes and has multiple line entries for different years, months, and quarters. So, on the SUMIFS formula I'm using, I'm pulling the "Debt" from column "O" for a specific "Year" and "Quarter".


I'm a novice at excel, so SUMIFS might not be the best formula for this purpose, but it worked.


@Luke- I tried your formula, and I don't understand the last part of the formula that has the 1:1. What does the 1:1 reference?


Also, Will it be possible to define a named range when the range will be on different named worksheets?
 
@maku

formula is looking for the column name "Total Value" in the 1st row of whichever sheet you pick, hence the 1:1 reference. Change this to whatever row contains the actual header.

Yes, can store this as a Named Range. Named Ranges don't have to be static addresses, can be defined as a dynamic formula.
 
Hi Mark,

If I understand you correctly, you are trying to aggregate a column based on criteria contained in a few other columns.


As long as the column you are aggregating is the same size as the columns you are evaluating for various criteria, you could still use the INDEX function Luke and I suggested above, along with the SUMIFS function.


For example:

=SUMIFS(INDEX(datarange,,1), INDEX(datarange,,2),"OneCriteria", INDEX(datarange,,3), "AnotherCriteria")

would still work.


You can use the approach that Luke suggested to locate a column when the column header location is not known. Otherwise, you could use the suggestion I provided to refer to the column directly (e.g. COLUMN(Q1))


Using Named ranges make it easy to write the formulas. You could also use include the reference in the "SUMIFS(INDEX(...)) formula.

For example: INDIRECT("Sheet1!A1:W200") can be substituted for the named range "datarange" above. Since the INDIRECT function uses a string as its argument, you could construct the argument to be whatever you need it to be.


Cheers,

Sajan.
 
Thank you Luke and Sajan...that's gives me a good direction...I'll work with those examples you gave me and see if I can get it to work. I see if I can use the named range it will really clean the formula up too.


Thanks! I'll let you know if I get it :)
 
You guys are geniuses! Thank you so much. Hopefully my question will help someone else too!


Here's the end formula I used that worked.

=SUMIFS(INDEX(INDIRECT("'"&$A6&"'!A:Z"),,MATCH("DSCR",INDIRECT("'"&$A6&"'!1:1"),0)),INDEX(INDIRECT("'"&$A6&"'!A:Z"),,MATCH("PERIOD",INDIRECT("'"&$A6&"'!1:1"),0)),parameters2!$C$4,INDEX(INDIRECT("'"&$A6&"'!A:Z"),,MATCH("YEAR",INDIRECT("'"&$A6&"'!1:1"),0)),'Summary Data (2)'!$B$1)


A6 = equals my worksheet name. DSCR, PERIOD, YEAR are column headings on the respective worksheets being called. The period AND year are the criteria that i'm finding the data for DSCR.


It works! Thanks again!
 
Hi Mark,

Glad to help! And yes, Luke is very clever, as is obvious from all of his responses to various folks!


By the way, just as you can use the INDEX function to return a whole column, you could also get it to return a whole row.

For example, instead of INDIRECT("'"&$A6&"'!1:1") you could also use

INDEX(datarange,1,0) anchoring your lookup on the data range, as opposed to hardcoding row 1.

(Here "datarange" is a Named Range referring to the worksheet with all of your data).

This will ensure that if your datarange shifts down a few rows (for example, if you added a couple of heading rows), your formula will still work.


Cheers,

Sajan.
 
You must log in or register to reply here.
Back
Top