Formula too long - Need to change sheet reference

[michaljireht]

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.

[FDibbins]

wow, Im not even going to try and wade through that. What exactly are you trying to do, and can you provide a small sample workbook, showing (clean) samples of what you have and what you want?

Monster formulas like that may look impressive and fancy, but as you have discovered, they are not easy to adjust or trouble-shoow.

1 thing I did notice in the 1st riow was an unnecessary AND()...
=IF(R20="Wholesale", IF(AND(C20=B134),INDEX
you only need to use AND when you are testing for multiple criteria...
=IF(R20="Wholesale", IF(AND(C20=B134,C20<B135),INDEX

You also seem to make excessive use of (), such as this...
+(COUNTIF('Prices-W'!$A$5:$A$14,L20)=0)

While there is nothing wrong with that, and if it helps frame up your logic - go for it, it is unnecessary. This will work just as well...
COUNTIF('Prices-W'!$A$5:$A$14,L20)=0,

[michaljireht]

Thank you for your response! I've cleaned up the formula as you said and I solved the problem by simply separating the formulas into two different cells.

[daffodil11]

Another tip worth knowing: You can use Alt+Enter to apply line breaks to your formula to keep it easy to read. This makes it much easier to troubleshoot later.

This represents your formula:

Please Login or Register  to view this content.

Now imagine if instead it looked like this:

Please Login or Register  to view this content.