Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default INDIRECT informulas

I have a cell which has the code

=INDIRECT("'" & Summary!A18 & "'!" & "A23") in it.

I want to use this value in a COUNTIFS

what should the syntax be?


Thanks in advance




--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-

  #2   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by no_name View Post
I have a cell which has the code

=INDIRECT("'" & Summary!A18 & "'!" & "A23") in it.

I want to use this value in a COUNTIFS

what should the syntax be?


Thanks in advance
You don't make it clear in your question exactly what you're trying to do.
Do you want to use the value that results from the INDIRECT formula above as the value to count in the COUNTIF, or to identify the range in which to count a value??

Explain more of what you're trying to do and an answer will be provided much quicker.

S.
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 829
Default INDIRECT informulas

"no_name" wrote:
I have a cell which has the code
=INDIRECT("'" & Summary!A18 & "'!" & "A23") in it.
I want to use this value in a COUNTIFS
what should the syntax be?


Perhaps:

COUNTIFS(INDIRECT("'" & Summary!A18 & "'!" & "A1:A1000"),B1,C1:C1000,D1)

or

COUNTIFS(B1:B1000,INDIRECT("'" & Summary!A18 & "'!" & "A23"),C1:C1000,D1)

Provide a concrete example that explains what you want to do. The more
specific your question, the more specific the answer.

But in any case, be careful what you wish for.

Since INDIRECT is a volatile function, your COUNTIFS formula will be
recalculated, along with any directly or indirectly dependent formulas,
every time you edit __any__ cell in __any__ worksheet in the workbook.

This can be slow things down significantly, especially if you use
whole-column references (not recommended) in the COUNTIFS formula, like:

COUNTIFS(B:B,INDIRECT("'" & Summary!A18 & "'!" & "A23"),C:C,D1)

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions Mike Barlow Excel Worksheet Functions 7 May 21st 23 07:42 PM
Indirect puiuluipui Excel Discussion (Misc queries) 2 November 14th 09 10:47 PM
INDIRECT INDIRECT Excel Worksheet Functions 6 July 14th 08 05:38 PM
Use of 'INDIRECT LN Excel Worksheet Functions 1 June 18th 08 04:27 PM
INDEX("a"&COLUMNS(INDIRECT(A1):INDIRECT(A2)):A30,4) Dave F[_2_] Excel Discussion (Misc queries) 3 September 20th 07 08:36 PM


All times are GMT +1. The time now is 10:16 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"