ActiveSheet in Excel formula

0
votes

I am not sure if I am even trying this with the right approach. I have a Template worksheet from where I am copying cells to another Worksheets. On each Worksheet where I am copying is "helper" cell to identify Worksheet (1, 2, 3, 4). I would like to refer to different cells on third worksheet and based on value create an IF statement.

This formula is on Template Worksheet:

=IF(C462="";"";IF(IF(C1=1;OtherData!$M$24;IF(C1=2;OtherData!$M$104;IF(C1=3;OtherData!$M$105;IF(C1=4;OtherData!$M$106))))="Fixed price";0%;30%))

I am copying it to Sheet "Calculation 1" where value in C1 is 1. As a result after Copy - Paste operation it looks like (on Worksheet Calculation 1):

=IF(C55="";"";IF(IF(#REF!=1;OtherData!$M$24;IF(#REF!=2;OtherData!$M$104;IF(#REF!=3;OtherData!$M$105;IF(#REF!=4;OtherData!$M$106))))="Fixed price";0%;30%))

Any ideas how to fix that? Other options are also welcomed!

1
excelvbaexcel-formula
Would you like to replace #REF with C1? If yes, use absolute reference in the template Worksheet: $C$1=2 instead of C1=2. otherwise, Excel tries incrementing (decrementing, in fact, in your example) and it cannot decrease the row reference with 411 rows... Of course, you must proceed in the same way with C1=3, C1=3 , too... – FaneDuru
@FaneDuru yes, preferably by some clever formula. I am also not sure if this nested IF formula is any good in terms of performance. If I have 100 of them on one worksheet. – hatman
In order to come with other options we must know what you want accomplishing... I just tried helping in keeping the lost reference... – FaneDuru
You can add it as an answer. I can ask another question as you have solved my problem for what has been asked in this one. – hatman

1 Answers

2
votes

In order to replace #REF with C1, please, use absolute reference in the template Worksheet: $C$1=2 instead of C1=2.

Otherwise, Excel tries incrementing (decrementing, in fact, in your example) and it cannot decrease the row reference with 411 rows... Of course, you must proceed in the same way with C1=1, C1=3 , too...