Indirect with sheetname and formula

[glerwell]

Hi

Is there a way that I can get this formula:

=SUMPRODUCT(--(TEXT(INDEX(B:B,12):INDEX(B:B,$AO$21),"ddmmmYY")=TEXT(AE14,"ddmmmYY")))

to use the sheetname in L6 as the sheet to where the formula looksup the data. Maybe by using INDIRECT.

I've tried:

=INDIRECT("'"&$L$6&SUMPRODUCT(--(TEXT(INDEX(B:B,12):INDEX(B:B,$AO$21),"ddmmmYY")=TEXT(AE13,"ddmmmYY"))))

but I'm getting an error.

Any ideas?

Cheers

 

[texasalynn]

indirect requires the sheet to be open - will that be the case?

 

[glerwell]

The sheet is open.

I just noticed a mistake with my formula and what I want to achieve:

What I want is this:

=SUMPRODUCT(--((TEXT(INDEX("sheetname in L6" B:B,12):INDEX("sheetname in L6" B:B,$L$15),"ddmmmYY")=TEXT(B10,"ddmmmYY"))))

So basically, I want the INDEX part of the formula(range B:B,12 and B:B,$L$15) to set as the sheetname in L6.

Is this possible?

Thanks

 

[texasalynn]

without your data I'm not able to help, but the formula will look something like this

=SUMPRODUCT(--((TEXT(INDEX(INDIRECT(L6&"!B:B"),12):INDEX((INDIRECT(L6&"!B:B"),$L$15,"ddmmmYY")=TEXT(B10,"ddmmmYY"))))))

 

[glerwell]

Hi, I've tried that and also something along the same lines but I keep on getting "the formula you typed contains an error" message.

 

[texasalynn]

so why not post a sample of the data with those references?

 

[glerwell]

There's too much data to post a sample, due to the data being spread over numerous sheets.

The formula works if I directly use a sheet reference ie.

=SUMPRODUCT(--((TEXT(INDEX('BANKER'!B:B,12):INDEX(' BANKER'!B:B,$L$15),"ddmmmYY")=TEXT(B10,"ddmmmYY"))))

but not indirectly.

I'll keep trying, it must be achievable!

thanks

 

[Aladin Akyurek]

Hi

Is there a way that I can get this formula:

=SUMPRODUCT(--(TEXT(INDEX(B:B,12):INDEX(B:B,$AO$21),"ddmmmYY")=TEXT(AE14,"ddmmmYY")))

to use the sheetname in L6 as the sheet to where the formula looksup the data. Maybe by using INDIRECT.

I've tried:

=INDIRECT("'"&$L$6&SUMPRODUCT(--(TEXT(INDEX(B:B,12):INDEX(B:B,$AO$21),"ddmmmYY")=TEXT(AE13,"ddmmmYY"))))

but I'm getting an error.

Any ideas?

Cheers

Does this...

=SUMPRODUCT(--(TEXT(INDEX(INDIRECT("'"&L6&"'!B:B"),12):INDEX(INDIRECT("'"&L6&"'!B:B"),$AO$21),"ddmmmYY")=TEXT(AE14,"ddmmmYY")))

work?

 

[glerwell]

Aladin, thats works!!!

Cheers

Texas thanks for the help too!

 

[texasalynn]

it was so close -
Aladin is a champ!