Sheet cell reference and INDIRECT()

**Lithium** · 06-09-2009, 10:18 AM

Hi all,

this is the first time I'm here, and I'm sorry that I'm starting to ask for answers right away.

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. I tried everything I know, but I need the help of the experts.

**NBVC** · 06-09-2009, 10:37 AM

What exactly is in Sheet2!B12 and Sheet2!B9?

**Lithium** · 06-09-2009, 10:41 AM

the value "W" (as text) and the value "X" (as text).

**NBVC** · 06-09-2009, 10:48 AM

Try:

Code:

=COUNTIFS(INDIRECT("Blad1!"&B12&":"&B12);"1";INDIRECT("Blad1!"&B9&":"&B9);"H")+COUNTIFS(INDIRECT("Blad1!"&B12&":"&B12);"6";INDIRECT("Blad1!"&B9&":"&B9);"H")

but not sure if the "1" & "6" should be in quotes (are they text too?)...

..if no:

Code:

=COUNTIFS(INDIRECT("Blad1!"&B12&":"&B12);1;INDIRECT("Blad1!"&B9&":"&B9);"H")+COUNTIFS(INDIRECT("Blad1!"&B12&":"&B12);6;INDIRECT("Blad1!"&B9&":"&B9);"H")

**Lithium** · 06-09-2009, 10:51 AM

The quotes need to stay, as the "referencee" is text.

##EDIT: Anyway, it works! Question answered, problem solved, thanks!

**Lithium** · 06-09-2009, 12:02 PM

I have a second question.

I'd like to search for text and get the column character where the cell with the found text.

The combination the two 'demands'/formulae into one are giving me problems.

**NBVC** · 06-09-2009, 12:04 PM

The original question is Solved... please post the new question in a new thread..

You can refer to this thread if desired.

and also elaborate with an example or two.