# INDIRECT In SUMPRODUCT For Sheet Name

Dec 18, 2006

In book3.xls, worksheet 'statistics_by_class_day', how can I rewrite the formula in C7 such that the count is based on number chosen in A3 and C3?

=SUMPRODUCT((INDIRECT("'attendance"&\$A\$3&"'!C:C")=\$A\$7)*((INDIRECT("'attendance"&\$A\$3&"'!I:I")<>0)))

In the formula, I:I refer to Day 4 of worksheet attendance9 (ie Column I), can I use INDIRECT() by referring to C3?

ADVERTISEMENT

## Sumproduct With An Indirect?

Nov 16, 2009

The following formula sorts for specifics in the sheet named 200910 in the specified ranges in columns A and D to return a total found in column AB. This works just fine.

=SUMPRODUCT(('200910'!\$A\$2:\$A\$1777="Countrywide")*('200910'!\$D\$2:\$D\$1777="Claims-All Products"),('200910'!AB2:AB1777))

What I am looking to do, instead of telling excel what sheet to go to, is insert this: =INDIRECT(TEXT(Y10,"yyymm")&"!ab1749") to find the matching sheet name to the date that resides in cell Y10.

These both work separately on their own to return the needed value. How do I put them into one formula without telling excel what sheet to go to (1st formula) and specifically what cell to go to (2nd formula) because the cell location may change and I want to completely automate this?

## Sumproduct & Indirect

Apr 20, 2007

=SUMPRODUCT(--(A2:A1000=B1))

Column A has got random numbers, my formula attemts to capture the count of numbers as specified in B1, is there a way I can nest an indired t formula to mention in cell C1 ( > or < ) so it can look for numbers greater than the number in B1 or lesser as indicated

## Sumproduct & Indirect Functions

Jul 15, 2006

Can someone help with this formula,

Cell \$A\$24 = A cell formatted as Month and Year = July06
Cell \$B\$1 = a date 1/7/06 linked to \$A\$24

Trying to use the indirect function to ref a sheet called July06 and other ranges here a example of one range =July06!\$D\$2:\$D\$247

This is what I've got

=SUMPRODUCT(--(INDIRECT(TEXT(\$A\$24,"mmmmyy")&"!\$D\$2:\$D\$247<="&\$B\$1)*(INDIRECT(TEXT(\$A\$24,"mmmmyy")&"!\$Y\$2:\$Y\$247>= "&\$B\$1)*(INDIRECT(TEXT(\$A\$24,"mmmmyy")&"!\$C\$2:\$C\$247="&\$A2)))))

## How To Use Indirect Function Within Sumproduct

Dec 29, 2009

I have the following formula, which works; however I need to make it dynamic.

## SUMPRODUCT With Indirect Formula

Jul 15, 2013

I am trying to sum the same data across several tabs but I am using information in the column and row headers to find it. For some reason this formula wont work and I can't figure out what I'm doing wrong.

=SUMPRODUCT(N(INDIRECT(""&Sheets&"!"&ADDRESS(MATCH(\$A\$12,\$A:\$A,0),MATCH(\$G\$3,3:3,0)),FALSE)))

I'm getting #REF! and when I step through it, it fails when it reaches the Indirect statement.

## SUMPRODUCT INDIRECT & SUMIF Formula

Jul 12, 2006

I have a set of data as attached where the productivity of each staff is recorded on a daily basis.However, the cell in which their name is located on each tab of the day of the month might not be the same. I am thankful that the forum actually provided me with the formula located in cell c15.

1)However, I would appreciate if someone could translate what the formula means as I am not so sure what the formula represents.

2)The reason is I would like to calculate how many training days (TR), Medical Leave(ML) and Emergency Leave(EL) for the month of January for each staff.I tried modifying the formula but it didnt work.

3) Is it possible to use a vlookup function for this solution?

## SUMPRODUCT / INDIRECT Functions Not Working With ROW Reference When Combined

Apr 4, 2014

Basically I have an equation:

=SUMPRODUCT(G9:G11,H9:H11)/SUM(G9:G11)

It's just a simple percentage calculator for my purposes and works fine as is. However, I want to make it dynamic whereby from a user input the length of the array will increase or decrease, e.g. G9-G11 will become G9-G12 if there are four rows occupied with data. For all my other SUMming equations I have solved this and even came up with what I think is a perfectly valid solution for this one. The one problem is that it doesn't work when combined into a single equation.

My solution for the upper part of the fraction is this:

"A1"=SUMPRODUCT(INDIRECT("G9:G"&MIN(ROW(G9)+A8-1)),INDIRECT("H9:H"&MIN(ROW(H9)+A8-1)))

Where the user inputs the number of rows used in A8.

The solution for the lower part of the fraction is this:
"A2"=SUM(INDIRECT("G9:G"&ROW(G9)+A8-1))

[code]....

## INDIRECT Not Working For Dynamic Named Range In SUMPRODUCT

Feb 11, 2012

The formula is =SUMPRODUCT((Group="A")*(Project_Description="Long Term")*(Profile="B")*(INDIRECT(B9)="x"))

B9 through M9 have header text Jan_2012, Feb_2012...Dec_2012. Basically my aim is to get the above formula working before I drag it across so that the named ranges get picked up automatically from the headers.

Jan_2012 thru Dec_2012 are dynamic named ranges using INDEX (and not OFFSET as someone mentioned OFFSET is a volatile function).

The above SUMPRODUCT formula is giving me a #REF! error for the (INDIRECT(B9)="x") part. I know that because when I replace it with (Jan_2012="x") it works fine.

I realized while writing this that it may be because the named range in Jan_2012 is not the same size as that of other arrays. [but it is the same size - I've re-confirmed just now]

## Use INDIRECT To Refer Sheet Name

May 9, 2014

I have multiple sheets as Name1 ; Name2 ; Name3 and each sheet have a value on cell A1 for any number let say between 0 to 100. On another sheet call SheetName, I have A1=Name1 ; A2=Name2 ; A3=Name3.

I have formula as:

[Code] .....

The INDIRECT formula work to refer each cell A1 in each sheet, however if the sheet name include a space then it won't work anymore. How to make it work with the sheet name with space?

## Indirect Addressing To A Sheet

Nov 16, 2008

I want to do is copy data to my working sheet (say sheet-1) from other worksheetx (say sheet-2, sheet-3). That's easy enough, but I want to be able to indirectly address "sheet-2" or "sheet-3" from a cell in sheet-1.

Look at the attachment. The data under cost A, cost B, cost C is from other sheets in the same workbook. I want to able to type in "sheet-2" in the first column and Excel to automatically copy over the data in columns 2,3,4.

I do not want a VBA solution. I know this can be done with built-in Excel functions because I did it before. Unfortunately, I lost that spreadsheet and I can't recall how it was done. I tried using Indirect function, but it returns a ref# error.

## Lookup Across A Sheet VBA Indirect?

Dec 12, 2011

I've tried to find the solution and the options I've tried I just can't get the code or syntax correct...

I have a workbook with 11 sheets in, 10 data sheets, and a summary

On sheet call "CB", I want to select any cell, then, via VBA, on sheet "Summary", in cells b4 to b14, where a4 to a14 have the sheet names, I want to reference the cell I selected, using the Indirect function....

something like

Code:

SelCell = Activecell.Value
Range("b4").Select
Activecell.FormulaR1C1 = "=INDIRECT(""'""&R[0]C[-1]&""'!SelCell"")"

I know it's incorrect.

## INDIRECT And Sheet Name Starting With C01V

Mar 2, 2009

I am using the INDIRECT function to get data from sheets with different names, with the format INDIRECT(SheetName&"!\$A\$1"), with SheetName defined as the contents of a particular cell which contains the name of the sheet. This works fine with the exception of sheets which have names starting with C01V. Is there something special about these names? If I change the 0 to O or the C to A, B or D, there is no problem. Sheet names C0, C01 also don't have any issues.

## Vlookup In A Closed Sheet, By Using Indirect

Jun 21, 2007

IF(ISNA(VLOOKUP(\$B\$2;INDIRECT("'G:2007Projekt opfølgning[OMKLIST.xls]kreditor!'"&J24&":"&L24);2;FALSE));"0";VLOOKUP(\$B\$2;INDIRECT("'G:2007Projekt opfølgning[OMKLIST.xls]kreditor!'"&J24&":"&L24);2;FALSE))

It is the part regarding til indirect that gives me problems

This form I can get it to work

IF(ISNA(VLOOKUP(\$B\$2;INDIRECT("[OMKLIST.xls]kreditor!"&J25&":"&L25);2;FALSE));"0";VLOOKUP(\$B\$2;INDIRECT("[OMKLIST.xls]kreditor!"&J25&":"&L25);2;FALSE))

But here I need the Omklist.xls to be open. And that I dont want.

The what i want is that the user can use the sheet with out oping the data sheet..

## Indirect Function: Use Cell For Sheet Name

Dec 7, 2006

Cell F4 contains a worksheet name which was “calculated” by an IF function (IF something, then ‘T2’, otherwise ‘T3’).

Cell F5 must then look at either sheet T2 or sheet T3 (depending on what it is told to do by reference to cell F4 just above) and report what it finds in cell A1 there.

Clearly I must use the INDIRECT function. But I have tried every imaginable combination of single and double quotation marks to produce the equivalent of
=T2!A1
all to no avail.

Is it something to do with letter and number combinations?

Alternatively, how else should I achieve what I want?

PS F4 could have been “calculated” by any other means, e.g. =MONTH(TODAY()).

## Indirect Formula For Sheet Name & Range

Jan 10, 2008

I have a formula: ='Jan 7-11'!D10. I want to replace the tab name 'Jan 7-11' to change based on the content of F6 (which is a data validation for all tabs in my workbook).

## Develop An Indirect Indirect Validation Drop Down List

Aug 18, 2009

I am trying to develop an Indirect Indirect Validation drop down list. Example, Building - Floor - Room, i.e. Select Building from a Validation drop down list. Then based upon the Building selected, select only the Floors applicable to the Building Selected. I am able to achieve this via an Indirect Validation drop down. However, when I attempt to then select the Rooms applicable to the Floor of the Building I selected, I can not produce an Indirect Validation off a previous Indirect Validation.

In the attachment, I have used Plant - Location - Room. I have name ranged the selections, and have used Validations Lists for Plant, and Indirect Validations for Location. The error occurs where I attempt to do an Indirect Validation for Room.

## Sheet Cell Reference And INDIRECT Formula

Jun 9, 2009

I have this formula in excel 2007:

=COUNTIFS(Blad1!W:W;"1";Blad1!X:X;"H")+COUNTIFS(Blad1!W:W;"6";Blad1!X:X;"H").

Blad is Dutch for sheet, by the way.

In this formula, I want to let the ranges in Sheet1 be dependent of values in Sheet2. The formula itself is in Sheet2.

W must be replaced by the value of Sheet2!B12 and X to be replaced by the value of Sheet2!B9.

I tried this by using the INDIRECT formula, but the quotation marks of the search values are giving errors.

## Indirect: Reflect The Data In The Active Sheet Or Give Me A #VALUE Error

Jan 16, 2009

I am running the formula =VALUE(RIGHT(CELL("filename"),15)) to read the tab name in a workbook with 90 sheets. When a new account is opened, the operator inserts a new sheet from a template (which contains the above formula), and labels the tab to the account number. The formula then 'reads' in the account number and performs look-ups based on it.

The problem is that the sheets are only working when the sheet is active. They all seem to reflect the data in the active sheet or give me a #VALUE error. Where am I going wrong? How do I fix the problem?

## Reference Sheet Names With Indirect Function To Make Formula?

May 21, 2014

Attached I have a document where I am wanting all of the individual sheet names on the Total page. Rather than having to change each formula to match the sheet name I believe there is a way to reference the sheet name column (AO) in the formula so you can drag it down to fill in the columns. I am basic with Excel and do not know how to add this indirect function into my current formulas.

## SUMPRODUCT-data From Another Sheet

Dec 17, 2008

I am trying to get a conditional total (example below - add up the attendees in E2:E50000 if the criteria in B and C columns are met).

The data is on another sheet from the SUMPRODUCT. I keep getting #VALUE! and looked up possible causes but can't see what I'm doing wrong is it an issue of combining text and numbers? All columns are in the "General" format.

=SUMPRODUCT((pasted!\$B\$2:\$B\$50000="Meeting")*(pasted!\$C\$2:\$C\$50000="Dallas")*(pasted!\$E\$2:\$E\$50000))

## Pull From Another Sheet And Sumproduct And TEXT

Oct 16, 2007

I have sheet 1 which has titles set up in column A. These titles are pulled from Sheet 2 (same column). I also have a date listed at the top of sheet 1 (in the same row as dates in sheet 2, but sheet 2 contains multiple columns for each quarter). I would like to set something up where if the date in sheet 1 matches the date in sheet 2 it pulls the corresponding values from the named rows in sheet 1. I tried using sumproduct but its not working b/c it wont take TEXT. Plus I want it to search across all of the columns, not just one.

Below is sheet 1 and I would like to be able to pull 1Q08 values from sheet 2 (which contains the values for every quarter). This is my formula (the first part is the date, the second is row titles, the third is the value (although its only pulling from one column, it s/b pulling from all) :

=SUMPRODUCT((Sheet2!B1:B6500=sheet1!B6)*(Sheet2!A1:A6500=Sheet2!A7)*(Sheet2!BA1:BA6500))

Is there someway to fix this, it would be a huge time saver.
--------------------------------------
1Q08
Revenue
Direct Costs
Gross Profit
Indirect Costs
Stock Based Compensation
Restructure Charge ....................

## Using SUM And INDIRECT

Nov 18, 2013

I am using these formula

=SUM('18'!N:N)
=COUNT('18'!A:A)

I need to enter the sheet name in cell A1 instead of the sheet number 18 in the formula using INDIRECT for both the formulas.

## How To Use INDIRECT

Mar 28, 2014

I have the following formula:

= SUM ('03:08'!D4, '03:08'!E4)

the 01, 02 ... 020 are the names of the sheets. How can I modify the formula so that I can use other sheet names. Name sheets whose cells I want to be myself in C4 and D4. I tried INDIRECT but I don't know for several sheets.

## INDIRECT And IF In One

Jul 19, 2008

I am using the below formula to return values from a seperate worksheet.

=INDIRECT("'[Test File.xls]Test Data'!A"&A4)

These values are text, numerical and dates. Sometimes there are no dates in the source worksheet to return, and I end up with 00/01/1900.

Is it possible to leave the cell blank if there is no value to return???

I had a stab at trying to nut it out, but it was a Friday afternoon, head was mush, and the pub was calling.

## Lookup With Indirect?

Dec 5, 2013

How could I create a formula that would look up based on month, category, and most importantly an indirect? I have attached a spreadsheet, the indirect is in K13 (and could be Quarter 1, Quarter 2, Quarter 3, Quarter 4) with matching data in "Sheet 2".

## Alternative For INDIRECT

Jan 26, 2009

I have used the function INDIRECT in 1 of my files.

The disadvantage is that both files (source and target) have to be open.

Is there a substitute for INDIRECT that works with a closed source file?

## Indirect Sum Through Worksheets

Sep 4, 2009

I am trying to sum through multiple worksheets but maintain flexibility using INDIRECT but it is not working!

I have a worksheet for each month of the year Jan - Dec with a financial result. In order to get a Year To Date figure I would have a formula such as:

=sum(Jan:Jul!B3) for a July YTD.

However, I want to maintain flexibility such that I can enter the worksheet name in cell A1, e.g. Sep and then have a formula such as:

=sum(INDIRECT("Jan:"&A1&"!B3"))

Thus allowing me to generate the correct YTD at any point. All I get is a #REF error.

## Indirect Sum Between Two Tabs

Mar 14, 2014

I am having some trouble with a handy formula I learned over this forum and its application between two tabs.

Referencing the attached workbook, the formulas in cell C6 & C7 are working for the end range I want, but the first section doesn't want to work. I'm not sure if it has something to do with the quotes (") or not.

## Can You Use INDIRECT In 3-D References

Jul 13, 2006

For example
=SUM(INDIRECT(C10))
where C10 would contain
="Sheet2:"&"Sheet3!"&"A"&ROW()
always returns #REF!.

However, ="Sheet3!"&"A"&ROW() as the Cell C10 entry will work fine.

Copyrights 2005-15 www.BigResource.com, All rights reserved