Add sheet reference in CELL() content

[sassaboss]

Hello, I've been trying to insert a Sheet reference inside CELL() formula because I want to reffer to another Sheet, not in current Sheet, where I write given formula.
Cell reference that is bold AH$1 and AH$19000 should refer to Sheet named "AAA". Right now I am getting #REF in every case I've tried.
I tried to use INDIRECT but didn't have much luck.


Any help is more than welcome

=SUMIFS(
INDIRECT("FAKT_"&D$1&"!"&CELL("address";AH$1)&":"&CELL("address";AH$19000));
INDIRECT("FAKT_"&D$1&"!$S1:$S19000");$B12;
INDIRECT("FAKT_"&D$1&"!$C1:$C19000");$A$7
)

 

[jasonb75]

Do you want CELL("address") or INDIRECT()?

The first is as simple as

=CELL("address",AAA!AH$1)

However you should note that the CELL function only returns the top left cell of a range.

 

[RoryA]

Why? The address of a cell is the same regardless of which sheet it's on.

 

[sassaboss]

Do you want CELL("address") or INDIRECT()?

The first is as simple as

=CELL("address",AAA!AH$1)

However you should note that the CELL function only returns the top left cell of a range.

I have tried that approach, but I am getting #REF as a result.

=SUMIFS(
INDIRECT("FAKT_JAN!"&CELL("address";AAA!AH$1)&":"&CELL("address";AAA!AH$19000));
INDIRECT("FAKT_"&D$1&"!$S1:$S19000");$B12&"*";
INDIRECT("FAKT_"&D$1&"!$C1:$C19000");$A$6
)

 

[sassaboss]

Why? The address of a cell is the same regardless of which sheet it's on.

Yes. It doesn't matter wich sheet is in use.

 

[sassaboss]

Do you want CELL("address") or INDIRECT()?

The first is as simple as

=CELL("address",AAA!AH$1)

However you should note that the CELL function only returns the top left cell of a range.

And if I put formula (CELL("address",AAA!AH$1)) in blank cell the message is

 

[jasonb75]

Best guess, it looks like this is what you need. The CELL function appears to be irrelevant.

=SUMIFS(
INDIRECT("FAKT_"&D$1&"AH$1:AH$19000);
INDIRECT("FAKT_"&D$1&"!$S1:$S19000");$B12&"*";
INDIRECT("FAKT_"&D$1&"!$C1:$C19000");$A$6
)

 

[sassaboss]

Best guess, it looks like this is what you need. The CELL function appears to be irrelevant.

=SUMIFS(
INDIRECT("FAKT_"&D$1&"AH$1:AH$19000);
INDIRECT("FAKT_"&D$1&"!$S1:$S19000");$B12&"*";
INDIRECT("FAKT_"&D$1&"!$C1:$C19000");$A$6
)

Unfortunatelly Excel returns #REF and it asks for " in the code (bold and underlined bellow in code)
=SUMIFS(
INDIRECT("FAKT_"&D$1&"AH$1:AH$19000 " );
INDIRECT("FAKT_"&D$1&"!$S1:$S19000");$B12&"*";
INDIRECT("FAKT_"&D$1&"!$C1:$C19000");$A$6
)

 

[RoryA]

You're missing an exclamation mark before the first range address (AH$1)

 

[sassaboss]

You're missing an exclamation mark before the first range address (AH$1)

Exactly
It works now