CODEWITHSUNDEEP
excelvbaexcel-formula
10101
10101

Reputation: 2412

ActiveSheet in Excel formula

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!

Upvotes: 0

Views: 128

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

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...

Upvotes: 2

Related Questions