Help me make this one work

[Spurious]

I get #VALUE on that one.

ADDRESS(ROUND(ROW(F7)/10;0)*10+2;COLUMN(F7))

What am I doing wrong? Thanks

 

[baitmaster]

I get $F$12

I had to change";" to "," but I'm guessing that's just a regional variation?

 

[jasonb75]

I get $F$12 from that formula, and from

=ADDRESS(ROUND(ROW(F7);-1)+2;COLUMN(F7))

I have a suspicion that you have only provided us with part of your formula, and not the part that is producing the error.

What is the rest of it?

 

[Spurious]

I get $F$12

I had to change";" to "," but I'm guessing that's just a regional variation?

Regional thing, sorry about that.

I get $F$12 from that formula, and from

=ADDRESS(ROUND(ROW(F7);-1)+2;COLUMN(F7))

I have a suspicion that you have only provided us with part of your formula, and not the part that is producing the error.

What is the rest of it?

=PRODUCT(ADDRESS(ROUND(ROW(F7);-1)+2;COLUMN(F7));$A7)

That's the error producing one.

 

[jasonb75]

Address returns a text string, not a valid range, for what you want to do you need to qualify the address as a valid range using the Indirect function.

=PRODUCT(INDIRECT(ADDRESS(ROUND(ROW(F7);-1)+2;COLUMN(F7))))

Or use a non volatile alternative like

=PRODUCT(INDEX(F:F;ROUND(ROW(F7);-1)+2;1))

 

[gsbelbin]

ADDRESS returns a string representing the address. You need to convert this using INDIRECT.

 

[Spurious]

Yeah, thanks, I used INDIRECT and got the forumla to work.

Somehow it works singularly in a cell, but not when I incorporate it into a much bigger formula.

I get an error at ROUND. Anyone know why this could happen?

 

[jasonb75]

What error are you getting?

And what exactly are you trying to do? There may be a much simpler way to get the desired results.

 

[Spurious]

That's the complete formula

=IF(A$1="Gesamtergebnis";$A6-$AB6;IF(B$1="Gesamtergebnis";SUM($B6:$AA6);IF(ISTNV(INDEX('GS Auswertung - 1'!$A$2:$AC$200;MATCH($A6;'GS Auswertung - 1'!$A$2:$A$200;0)+1;COLUMN(B$1)+1));IF(ROUND(PRODUCT(INDIRECT(ADDRESS(FLOOR(ROW(B6)/10;0)*10+2;COLUMN(B6)));$A6);0)<>0;ROUND(PRODUCT(INDIRECT(ADDRESS(FLOOR(ROW(B6)/10;0)*10+2;COLUMN(B6)));$A6);0);"");INDEX('GS Auswertung - 1'!$A$2:$AC$200;MATCH($A6;'GS Auswertung - 1'!$A$2:$A$200;0)+1;COLUMN(B$1)+1))))

I am German, so dont get weirded out by the sheet names. There might be a formula in there still in German, so expect it to have the correct name (not necessary function).

 

[Spurious]

Ok, I got more than 7 functions working within each other, that's not possible. ****. I worked on that sheet for 1.5 days.