Formula too long - Need to change sheet reference

Hi all,

I currently have a formula that's too long. Is there a way to change the sheet reference quickly without resorting to copy pasting the entire code again?

Because right now I'm doing..

IF(A1="A",~long code referencing Sheet 2~,IF(A2="B",~long code referencing Sheet 3 but with same cell references, just different sheets~," "))

This is the code, by the way.

=IF(R20="Wholesale", IF(AND(C20=B134),INDEX(OFFSET('Prices-W'!$B$5:$R$14,(0+(J20-1))*14,0),IF(L20<'Prices-W'!$A$5,0,MATCH(L20,'Prices-W'!$A$5:$A$14))+(COUNTIF('Prices-W'!$A$5:$A$14,L20)=0),IF(K20<'Prices-W'!$B$4,0,MATCH(K20,'Prices-W'!$B$4:$R$4))+(COUNTIF('Prices-W'!$B$4:$R$4,K20)=0)),IF(AND(C20=B135,J20<6),INDEX(OFFSET('Prices-W'!$B$105:$AK$119,(0+(J20-1))*19,0),IF(L20<'Prices-W'!$A$105,0,MATCH(L20,'Prices-W'!$A$105:$A$119))+(COUNTIF('Prices-W'!$A$105:$A$119,L20)=0),IF(K20<'Prices-W'!$B$104,0,MATCH(K20,'Prices-W'!$B$104:$AK$104))+(COUNTIF('Prices-W'!$B$104:$AK$104,K20)=0)),IF(AND(C20=B136,J20<6),INDEX(OFFSET('Prices-W'!$B$202:$AK$216,(0+(J20-1))*19,0),IF(L20<'Prices-W'!$A$202,0,MATCH(L20,'Prices-W'!$A$202:$A$216))+(COUNTIF('Prices-W'!$A$202:$A$216,L20)=0),IF(K20<'Prices-W'!$B$201,0,MATCH(K20,'Prices-W'!$B$201:$AK$201))+(COUNTIF('Prices-W'!$B$201:$AK$201,K20)=0)),IF(AND(C20=B137,J20<7),INDEX(OFFSET('Prices-W'!$B$299:$P$307,(0+(J20-1))*13,0),IF(L20<'Prices-W'!$A$299,0,MATCH(L20,'Prices-W'!$A$299:$A$307))+(COUNTIF('Prices-W'!$A$299:$A$307,L20)=0),IF(K20<'Prices-W'!$B$298,0,MATCH(K20,'Prices-W'!$B$298:$P$298))+(COUNTIF('Prices-W'!$B$298:$P$298,K20)=0)),IF(AND(OR(C20=B138,C20=B139),J20<7),1.3*(INDEX(OFFSET('Prices-W'!$B$299:$P$307,(0+(J20-1))*13,0),IF(L20<'Prices-W'!$A$299,0,MATCH(L20,'Prices-W'!$A$299:$A$307))+(COUNTIF('Prices-W'!$A$299:$A$307,L20)=0),IF(K20<'Prices-W'!$B$298,0,MATCH(K20,'Prices-W'!$B$298:$P$298))+(COUNTIF('Prices-W'!$B$298:$P$298,K20)=0))),IF(AND(C20=B141),INDEX(OFFSET('Prices-W'!$B$379:$P$391,(0+(J20-1))*17,0),IF(L20<'Prices-W'!$A$379,0,MATCH(L20,'Prices-W'!$A$379:$A$391))+(COUNTIF('Prices-W'!$A$379:$A$391,L20)=0),IF(K20<'Prices-W'!$B$378,0,MATCH(K20,'Prices-W'!$B$378:$P$378))+(COUNTIF('Prices-W'!$B$378:$P$378,K20)=0)),IF(AND(C20=B140),1.6*(INDEX(OFFSET('Prices-W'!$B$379:$P$391,(0+(J20-1))*17,0),IF(L20<'Prices-W'!$A$379,0,MATCH(L20,'Prices-W'!$A$379:$A$391))+(COUNTIF('Prices-W'!$A$379:$A$391,L20)=0),IF(K20<'Prices-W'!$B$378,0,MATCH(K20,'Prices-W'!$B$378:$P$378))+(COUNTIF('Prices-W'!$B$378:$P$378,K20)=0))),IF(C20=B143,IF(L20<'Prices-W'!$A$500,0,MATCH(L20,'Prices-W'!$A$500:$A$526))+(COUNTIF('Prices-W'!$A$500:$A$526,L20)=0),IF(K20<'Prices-W'!$B$499,0,MATCH(K20,'Prices-W'!$B$499:$W$499))+(COUNTIF('Prices-W'!$B$499:$W$499,K20)=0),IF(C20=B144,IF(L20<'Prices-W'!$A$532,0,MATCH(L20,'Prices-W'!$A$532:$A$558))+(COUNTIF('Prices-W'!$A$532:$A$558,L20)=0),IF(K20<'Prices-W'!$B$531,0,MATCH(K20,'Prices-W'!$B$531:$W$531))+(COUNTIF('Prices-W'!$B$531:$W$531,K20)=0),IF(C20=B145,IF(L20<'Prices-W'!$A$564,0,MATCH(L20,'Prices-W'!$A$564:$A$590))+(COUNTIF('Prices-W'!$A$564:$A$590,L20)=0),IF(K20<'Prices-W'!$B$563,0,MATCH(K20,'Prices-W'!$B$563:$Z$563))+(COUNTIF('Prices-W'!$B$563:$Z$563,K20)=0),IF(C20=B146,IF(L20<'Prices-W'!$A$596,0,MATCH(L20,'Prices-W'!$A$596:$A$620))+(COUNTIF('Prices-W'!$A$596:$A$620,L20)=0),IF(K20<'Prices-W'!$B$595,0,MATCH(K20,'Prices-W'!$B$595:$U$595))+(COUNTIF('Prices-W'!$B$595:$U$595,K20)=0),IF(C20=B147,IF(L20<'Prices-W'!$A$626,0,MATCH(L20,'Prices-W'!$A$626:$A$652))+(COUNTIF('Prices-W'!$A$626:$A$652,L20)=0),IF(K20<'Prices-W'!$B$625,0,MATCH(K20,'Prices-W'!$B$625:$W$625))+(COUNTIF('Prices-W'!$B$625:$W$625,K20)=0),IF(C20=B148,IF(L20<'Prices-W'!$A$658,0,MATCH(L20,'Prices-W'!$A$658:$A$684))+(COUNTIF('Prices-W'!$A$658:$A$684,L20)=0),IF(K20<'Prices-W'!$B$657,0,MATCH(K20,'Prices-W'!$B$657:$W$657))+(COUNTIF('Prices-W'!$B$657:$W$657,K20)=0),IF(C20=B142,(((K20*L20)/1000000)*290)+(G20*'Prices-W'!$D$688)+(H20*'Prices-W'!$C$689),IF(C20=B149,IF(L20<'Prices-W'!$A$695,0,MATCH(L20,'Prices-W'!$A$695:$A$696))+(COUNTIF('Prices-W'!$A$695:$A$696,L20)=0),IF(K20<'Prices-W'!$B$694,0,MATCH(K20,'Prices-W'!$B$694:$O$694))+(COUNTIF('Prices-W'!$B$694:$O$694,K20)=0),IF(C20=B150,IF(L20<'Prices-W'!$A$702,0,MATCH(L20,'Prices-W'!$A$702:$A$703))+(COUNTIF('Prices-W'!$A$702:$A$703,L20)=0),IF(K20<'Prices-W'!$B$701,0,MATCH(K20,'Prices-W'!$B$701:$O$701))+(COUNTIF('Prices-W'!$B$701:$O$701,K20)=0),IF(C20=B151,IF(L20<'Prices-W'!$A$709,0,MATCH(L20,'Prices-W'!$A$709:$A$710))+(COUNTIF('Prices-W'!$A$709:$A$710,L20)=0),IF(K20<'Prices-W'!$B$708,0,MATCH(K20,'Prices-W'!$B$708:$O$708))+(COUNTIF('Prices-W'!$B$708:$O$708,K20)=0)," ")))))))))))))))))),IF(R20="Retail", IF(AND(C20=B134),INDEX(OFFSET('Prices-R'!$B$5:$R$14,(0+(J20-1))*14,0),IF(L20<'Prices-R'!$A$5,0,MATCH(L20,'Prices-R'!$A$5:$A$14))+(COUNTIF('Prices-R'!$A$5:$A$14,L20)=0),IF(K20<'Prices-R'!$B$4,0,MATCH(K20,'Prices-R'!$B$4:$R$4))+(COUNTIF('Prices-R'!$B$4:$R$4,K20)=0)),IF(AND(C20=B135,J20<6),INDEX(OFFSET('Prices-R'!$B$105:$AK$119,(0+(J20-1))*19,0),IF(L20<'Prices-R'!$A$105,0,MATCH(L20,'Prices-R'!$A$105:$A$119))+(COUNTIF('Prices-R'!$A$105:$A$119,L20)=0),IF(K20<'Prices-R'!$B$104,0,MATCH(K20,'Prices-R'!$B$104:$AK$104))+(COUNTIF('Prices-R'!$B$104:$AK$104,K20)=0)),IF(AND(C20=B136,J20<6),INDEX(OFFSET('Prices-R'!$B$202:$AK$216,(0+(J20-1))*19,0),IF(L20<'Prices-R'!$A$202,0,MATCH(L20,'Prices-R'!$A$202:$A$216))+(COUNTIF('Prices-R'!$A$202:$A$216,L20)=0),IF(K20<'Prices-R'!$B$201,0,MATCH(K20,'Prices-R'!$B$201:$AK$201))+(COUNTIF('Prices-R'!$B$201:$AK$201,K20)=0)),IF(AND(C20=B137,J20<7),INDEX(OFFSET('Prices-R'!$B$299:$P$307,(0+(J20-1))*13,0),IF(L20<'Prices-R'!$A$299,0,MATCH(L20,'Prices-R'!$A$299:$A$307))+(COUNTIF('Prices-R'!$A$299:$A$307,L20)=0),IF(K20<'Prices-R'!$B$298,0,MATCH(K20,'Prices-R'!$B$298:$P$298))+(COUNTIF('Prices-R'!$B$298:$P$298,K20)=0)),IF(AND(OR(C20=B138,C20=B139),J20<7),1.3*(INDEX(OFFSET('Prices-R'!$B$299:$P$307,(0+(J20-1))*13,0),IF(L20<'Prices-R'!$A$299,0,MATCH(L20,'Prices-R'!$A$299:$A$307))+(COUNTIF('Prices-R'!$A$299:$A$307,L20)=0),IF(K20<'Prices-R'!$B$298,0,MATCH(K20,'Prices-R'!$B$298:$P$298))+(COUNTIF('Prices-R'!$B$298:$P$298,K20)=0))),IF(AND(C20=B141),INDEX(OFFSET('Prices-R'!$B$379:$P$391,(0+(J20-1))*17,0),IF(L20<'Prices-R'!$A$379,0,MATCH(L20,'Prices-R'!$A$379:$A$391))+(COUNTIF('Prices-R'!$A$379:$A$391,L20)=0),IF(K20<'Prices-R'!$B$378,0,MATCH(K20,'Prices-R'!$B$378:$P$378))+(COUNTIF('Prices-R'!$B$378:$P$378,K20)=0)),IF(AND(C20=B140),1.6*(INDEX(OFFSET('Prices-R'!$B$379:$P$391,(0+(J20-1))*17,0),IF(L20<'Prices-R'!$A$379,0,MATCH(L20,'Prices-R'!$A$379:$A$391))+(COUNTIF('Prices-R'!$A$379:$A$391,L20)=0),IF(K20<'Prices-R'!$B$378,0,MATCH(K20,'Prices-R'!$B$378:$P$378))+(COUNTIF('Prices-R'!$B$378:$P$378,K20)=0))),IF(C20=B143,IF(L20<'Prices-R'!$A$500,0,MATCH(L20,'Prices-R'!$A$500:$A$526))+(COUNTIF('Prices-R'!$A$500:$A$526,L20)=0),IF(K20<'Prices-R'!$B$499,0,MATCH(K20,'Prices-R'!$B$499:$W$499))+(COUNTIF('Prices-R'!$B$499:$W$499,K20)=0),IF(C20=B144,IF(L20<'Prices-R'!$A$532,0,MATCH(L20,'Prices-R'!$A$532:$A$558))+(COUNTIF('Prices-R'!$A$532:$A$558,L20)=0),IF(K20<'Prices-R'!$B$531,0,MATCH(K20,'Prices-R'!$B$531:$W$531))+(COUNTIF('Prices-R'!$B$531:$W$531,K20)=0),IF(C20=B145,IF(L20<'Prices-R'!$A$564,0,MATCH(L20,'Prices-R'!$A$564:$A$590))+(COUNTIF('Prices-R'!$A$564:$A$590,L20)=0),IF(K20<'Prices-R'!$B$563,0,MATCH(K20,'Prices-R'!$B$563:$Z$563))+(COUNTIF('Prices-R'!$B$563:$Z$563,K20)=0),IF(C20=B146,IF(L20<'Prices-R'!$A$596,0,MATCH(L20,'Prices-R'!$A$596:$A$620))+(COUNTIF('Prices-R'!$A$596:$A$620,L20)=0),IF(K20<'Prices-R'!$B$595,0,MATCH(K20,'Prices-R'!$B$595:$U$595))+(COUNTIF('Prices-R'!$B$595:$U$595,K20)=0),IF(C20=B147,IF(L20<'Prices-R'!$A$626,0,MATCH(L20,'Prices-R'!$A$626:$A$652))+(COUNTIF('Prices-R'!$A$626:$A$652,L20)=0),IF(K20<'Prices-R'!$B$625,0,MATCH(K20,'Prices-R'!$B$625:$W$625))+(COUNTIF('Prices-R'!$B$625:$W$625,K20)=0),IF(C20=B148,IF(L20<'Prices-R'!$A$658,0,MATCH(L20,'Prices-R'!$A$658:$A$684))+(COUNTIF('Prices-R'!$A$658:$A$684,L20)=0),IF(K20<'Prices-R'!$B$657,0,MATCH(K20,'Prices-R'!$B$657:$W$657))+(COUNTIF('Prices-R'!$B$657:$W$657,K20)=0),IF(C20=B142,(((K20*L20)/1000000)*290)+(G20*'Prices-R'!$D$688)+(H20*'Prices-R'!$C$689),IF(C20=B149,IF(L20<'Prices-R'!$A$695,0,MATCH(L20,'Prices-R'!$A$695:$A$696))+(COUNTIF('Prices-R'!$A$695:$A$696,L20)=0),IF(K20<'Prices-R'!$B$694,0,MATCH(K20,'Prices-R'!$B$694:$O$694))+(COUNTIF('Prices-R'!$B$694:$O$694,K20)=0),IF(C20=B150,IF(L20<'Prices-R'!$A$702,0,MATCH(L20,'Prices-R'!$A$702:$A$703))+(COUNTIF('Prices-R'!$A$702:$A$703,L20)=0),IF(K20<'Prices-R'!$B$701,0,MATCH(K20,'Prices-R'!$B$701:$O$701))+(COUNTIF('Prices-R'!$B$701:$O$701,K20)=0),IF(C20=B151,IF(L20<'Prices-R'!$A$709,0,MATCH(L20,'Prices-R'!$A$709:$A$710))+(COUNTIF('Prices-R'!$A$709:$A$710,L20)=0),IF(K20<'Prices-R'!$B$708,0,MATCH(K20,'Prices-R'!$B$708:$O$708))+(COUNTIF('Prices-R'!$B$708:$O$708,K20)=0)," "))))))))))))))))),"N/A"))

My question is, is there a formula or VBA code to change all 'Prices-W' to 'Prices-R' if a certain cell contains "Retail"? If the cell contains "Wholesale" or is blank, the references should be 'Prices-W' still.

Thank you in advance.