COUNTIF With An INDIRECT

Jun 4, 2009

I need to have a cell count the number of cells that contain a certain text found in cell $b3. (the 3 is relative the B is not).

The data will be found on multiple sheets called "Game x" where X is an integer. (Game 1, Game 2, etc...) the cells are between $b$65 and $b$73.

i tried =COUNTIF(INDIRECT("'Game " & 1:$A$1 & "'!$b65:$b73"),$B3)
but it did not work.

also after i get that working I need to do the same thing, but i need to count the number of times the Name in $b3 appears in the List with the word "Win" in the "D" column (next to the $b65:b$73)

Again i tried =COUNTIF(AND(INDIRECT("'Game " & 1:$A$1 & "'!$b65:$b73"),$B3),(INDIRECT("'Game " & 1:$A$1 & "'!$b65:$D73"),"Win"))

ADVERTISEMENT

Countif Using Indirect With Concatenate Inside?

Oct 4, 2011

I have this formula in a cell which gives me a #REF error:

=COUNTIF(INDIRECT(CONCATENATE("'Working area'!",ADDRESS(6,13),":","'Working area'!",ADDRESS(6,742))),"N")

The concatenated text string calculates to

'Working area'!M6:ABN6

In that range I would like to count the occurrences of the letter N.

(I have simplified the ADDRESS function to ADDRESS(6,13). In my real spreadsheet, the number 6 is obtained by some more function-combinations.)

View 1 Replies View Related

COUNTIF, INDIRECT And Dynamic Named Ranges

Nov 22, 2006

The following formula produces the desired result:

=COUNTIF(INDIRECT(TEXT(A8,"mmm")&"!B2:Z100"),"SK")
but replacing the range of cells with a dynamic named range returns #REF!:

=COUNTIF(INDIRECT(TEXT(A8,"mmm")&"!Data"),"SK")
where A8 is the date 01/01/07. I'm trying to count items within the range Jan!Data.

I'm not sure if I'm trying to do the impossible, or if I'm missing something.

View 9 Replies View Related

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.

View 3 Replies View Related

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.

View 5 Replies View Related

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.

View 6 Replies View Related

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.

View 9 Replies View Related

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".

View 3 Replies View Related

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?

View 11 Replies View Related

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.

View 9 Replies View Related

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?

View 3 Replies View Related

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.

View 7 Replies View Related

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.

View 10 Replies View Related

Indirect (Address ...)

Nov 6, 2007

Address(5,$Z$5+60) appears to refer to the cell I want; however, I'm trying to use the Address function inside a Rank function and have tried it with and without the Indirect function (as shown below) and it doesn't work --

=Rank($BE$5,Indirect(Address(5,$Z$5+60)):Indirect(Address(1000,$Z$5+60)))

The range always comes back as 0.

View 9 Replies View Related

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

View 9 Replies View Related

INDIRECT REF TO DEFINED NAME

Aug 14, 2007

=INDIRECT("'" & A2 & "'!" & B2)

I am trying to use this formula to get to total of each month depending on A2. Cell A2 will have drop down of months names, that is Tabs names. I want B2 to have total of each month rather than cell reference, because Total may not be always in the same cell, we add rows if we add new account number or cost center.

So, Can I use name function instead of cell reference, for example April-total, May-total, June-total etc.

View 9 Replies View Related

Sum Indirect With Variables

Sep 28, 2007

I have several cells with defined names throughout a workbook which I need to be able to sum. The defined names all have the same naming convention (i.e. Assets575Total, Assets349Total, Assets286Total) where the numeric is an account #.

I have tried the follwing formulas below using Indirect, however, none seem to work:

{=SUM(INDIRECT("Assets*"&"*Total"))}

{=SUM(INDIRECT("Assets"&%&"Total"))}

{=SUM(INDIRECT("Assets"&"*"&"Total"))}

{=SUM(INDIRECT("Assets"&*&"Total"))}

{=SUM(INDIRECT("Assets"&%&"Total"))}

View 9 Replies View Related

Indirect Using Various Sheets

Nov 16, 2007

I have 40 sheets with info and 2 summary sheets. One summary sheet will summarise the data on sheets 1 to 20 and the other 21 to 40.

Im using the following:

=INDIRECT(ROW()-9&"!X10")
This works fine for my 1st summary sheet enabling me to display the vlaue of X10 in sheets 1 to 20 in D10:D29.

However in the 2nd summary sheet I wish to display X10 in D10:D29 but only using sheets 21 to 40.

Is there a way to eliminate sheets 1 to 20 and just use 21 to 40.

View 9 Replies View Related

INDIRECT With MIN Function

Jan 25, 2008

I just need to find the MIN of P6 to Px. Where x is a number that is from another sheet. (Lets say B3)

I thought this would be it, but its still not working, gives me #REF! error

=MIN(INDIRECT("P6:"& '[filename.xls]Sheet 1'!$B$3))

View 9 Replies View Related

INDIRECT Across Sheets

Aug 14, 2008

I have a sheet with tabs Jan - Dec

My goal is for a user to specify a 3 letter Month in Cell A1 (i.e. Jun) and for my sheet to calculate all cells C5 from Jan - Jun.

I have tried using the following formula =SUM(INDIRECT("Jan:"&A1&"!C5")), but the indirect function does not seem to span across workbooks!

View 9 Replies View Related

Indirect Fields

Sep 29, 2008

I am trying to figure out a solution and wondering what would work the best. Here is my situation. As an example, I have one big database with fields such as:

Item# Date Qty Price Cost ect...
1 3/4/08 3 $9.00 $7.00
2 9/5/08 5 $8.00 $6.00 ect....

This continues for up to 1000 lines from a database. I have this is a tab called "Database". From the data in the tab "Database", I want to be able to create 4 seperate reports.

The first report might only have the columns "Item #" and "Date".
The second report might only have the columns "Item #" and "Qty".
The 3rd with only "Item #" and "Price"
The 4th with only "Item #" and "Cost"

If I create a new spreadsheet called "Sales" and create the following:

ColA = Item #
ColB = Date

View 10 Replies View Related

Concatenate With Indirect + VBA

Jan 31, 2009

2 questions:

1. How can i put an Indirect function nested with concatenate?

=CONCATENATE(A8,B8,C8,D8,E8,F8,G8,H8,I8,J8,K8,L8,M8)

2. How can i get the formula to adjust to new data range without manually filling down. Assume the formula starts CELL Q8

View 9 Replies View Related

Indirect To Non-volatile

Feb 26, 2010

why Microsoft have not made the indirect function non-volatile. In 1997, they changed the index function to non-volatile.

View 9 Replies View Related

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?

View 5 Replies View Related

Using Indirect Function With Comboboxes

Apr 22, 2011

I totally understand how to make the combobox under form controls now but I am not having any success with the indirect function I was using as a list now that I have a combo box. I have attached the current form I am working on that just shows the list function still. How to convert this over to combo boxes with the indirect function?

I attached a second form with the feature I am asking about. It is just lacking the third list that I now have in place. (on the 1st attachment).

Attached Files

File Type: Corp MASTER (3).xlsx‎
File Type: Quote form (2).xlsx‎

View 8 Replies View Related

Getting Data Using Indirect Function?

Jan 18, 2014

I have some data on a sheet and I want it on other sheet without using lookup function. Only "Indirect function need to be used". find attached the excel sheet.

View 1 Replies View Related

Using INDIRECT Function For VLOOKUP?

May 8, 2014

I am having issues using the INDIRECT function to lookup data from a sheet with the same name as that appearing in a given cell. For example, in cell D27 i have the text "S1_358_810" (Not including quotations). I also have a sheet named "S1_358_810". My formula is as follows;

Formula:

[Code] .....

However this is returning #N/A. There is a list of numbers in sheet S1_358_810 in column N and from that I want the value in column Q (thus 17).

View 11 Replies View Related

Using INDIRECT In Advanced Filter

Aug 5, 2014

I have a condition in advanced filtering as >90%. However, I would like the "90%" to be calculated via a formula in another cell and this condition to reference it. Is it possible?

View 8 Replies View Related

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.

View 5 Replies View Related

INDEX As An Alternative For INDIRECT

Feb 18, 2009

In 1 of my spreadsheet I make use of the function INDIRECT to access cells in another spreadsheet. This works fine but has the disadvantage that both file have to be open.

It seems that INDEX can do the same, but: the sourcefile doesn't have to be open. I have tried it and this works.

However: the directory of the file I'm working on will change in the near future (maybe more than once)
Therefore I want 1 central place with the directory and file names and use these in the INDEX function

This is where I get into trouble
I'm not sure if it is possible, but if it is, some advice is needed on how to do this.

View 7 Replies View Related

INDIRECT(MOD(ROW)) Skipping A Lot Of Rows

Jul 16, 2009

I'm currently working on a report and what I'm trying to do is get a Row of information to pull into 4 rows. My current formula looks like this:
=INDIRECT("'Paste SAP'!H"&IF(MOD(ROW()-1,4)=1,ROUNDDOWN(((ROW())+3/4),0)," "),1)

I change the bolded number to correspond to which row (1,2,3,0) but it's not functioning. I've done it with other but for some reason this one doesn't work. I've attached the template so you can see what it looks like. The problem is with the SAP Tab and the info from the Paste SAP tab.

View 2 Replies View Related

Using INDIRECT For Column Refer Only

Jul 20, 2009

Let's say I have a formula in cell A1 that is =COLUMN(L5). So cell A1 returns the result 12 (for column L).

I now want to create an index formula in another cell:

=INDEX(C1:C12, ........), but I want the 12 in C12 to be picked up from the result of cell A1

So, I tried various things like =INDEX(C1:C&INDIRECT(A1)....etc but I can't work out the correct way of doing this.

View 6 Replies View Related

Copying Down An Indirect Formula

Aug 20, 2009

How can I copy down an indirect formula? When I copy it the lookup reference doesn't change. My formula is: =IF(INDIRECT("Q1")="",INDIRECT("R1"),INDIRECT("Q1"))

but when I copy down the cell reference stays the same (I need to keep the indirect formula because I'm adding columns in column Q but it needs to reference column Q even when columns are added). From reading through some other posts I believe I need to add a ROW() or COLUMN() formula in there somewhere.

View 5 Replies View Related

Use Indirect To Get A Value That I Want To Calculate With The Value Of The Current

Dec 17, 2009

I use the following formula: =1-B6/B7. This formula calculates the difference of 2 values. This percentage is returned. Using the formula with INDIRECT do I get data from another worksheet from another workbook. I use this formula: =INDIRECT("'[Workbook.xls]" & Sheet1!E72 & "'!C2"). This formula gives just the value of C2 of the other worksheet of Workbook.xls

I want a formula that calculates between C2 of the external worksheet of Workbook.xls and B2 that is the current value of the current Worksheet that is not external. I want to return the percentage this way like: =1-C2 other workbook/B6. I want to call the other external workbook the same way as I do with the formule above I have given with INDIRECT.

View 3 Replies View Related

Data Validation Using Indirect

Feb 11, 2014

I've attached a sample file of what I am trying to do.

In row 2 I have data validation in the form of lists. Below row 2 is the data.

Cell B2 has an indirect expression referring to cell A2. In other words, B2 is dependent on A2 (cascading)

What I would like to happen is when a new selection is made in cell A2, B2 populates with "Choose platform", instead of keeping the old information. The drop down populates with the new data but cell B2 still displays the old data.

Book2.xlsx

View 1 Replies View Related

Concatenate & Indirect Logic

Sep 21, 2009

I need your help on the attached sample sheet. I have used the concatenate & indirect combination logic to get the desire comments as outcome , but my problem is that I have 300 rows & 30 sheets in a workbook with this logic , it is increasing the size of my file as I have a total of 30 sheets with same logic. I need it in every sheet. If there is any other alternative or solution.

View 14 Replies View Related

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)))))

View 9 Replies View Related

Combination Of Ifsum And Indirect

Oct 17, 2007

i've attached a worksheet yet removed my attempt at formulas as it would have made most of you cry... what i'd like to do is select an item from a dropdown list (B1) (that i've built and it works, phew) and display the summary data (B3:B10) from the column or sum of columns in array (A12:F21) as explained in the relationship matrix.

I've tried ifsum and dsum (copied in each cell B3 to B10 of course) yet it doesn't extract items from the array with the drop-down selection. nor does it add columns together.

View 14 Replies View Related

Conditional Formatting With INDIRECT And AND

Oct 22, 2007

I'm trying to conditionally format a cell based on the cells around it. There might be a better way to do this but this is what I have done. I'll just show some trials of formatting conditions I've done.

View 7 Replies View Related

Using Index/match & Indirect

Jan 29, 2009

I wonder if you can use the Index- Match feature as part of the Indirect formula to find a column?

View 13 Replies View Related

Replacement For Using Indirect (Volatile)

Oct 30, 2009

What can I use to replace the portion in red because it is volatile?

=IF(A3="N","",RANK(B3,$B$2:INDIRECT("$B$17"),0)+COUNTIF($B$2:B3,B3)-1)

View 6 Replies View Related

ADVERTISEMENT