Enable insertion of columns and retain formula

[ozbeachbum]

I have the following formulas but need to insert columns within the range from time to time, however when I do the formulas return VALUE.
Is there another formula or adjustment to these formula that will eliminate the issue?

Destination Cell AF48
='Monies Drawn Down'!M6+AN46
+SUMPRODUCT(--(MOD(COLUMN(T59:LN59)-COLUMN(T59)+1,46)=0),T59:LN59)
+SUMPRODUCT(--(MOD(COLUMN(T59:LN59)-COLUMN(T59)+1,46)=0),'COM BID Off Pur'!T59:LN59)


Destination Cell AF51
=SUMPRODUCT(--(MOD(COLUMN(V59:LN59)-COLUMN(V59)+1,46)=0),V59:LN59)
+SUMPRODUCT(--(MOD(COLUMN(V59:LN59)-COLUMN(V59)+1,46)=0),'COM BID Off Pur'!V59:LN59)

I seem to be able to only place the one mini sheet, trust it helps

240903 20 CHRONICLE mrxl.xlsx
	T	U	V	W								AE	AF	AG	AH																												BJ	BK	BL	BM	BN	BO	BP	BQ
2	1	2	3	4								12	13	14	15																												43	44	45	46	47	48	49	50
48													16
49
50
51													13
52
58
59																																														1		3
HOLD CUR Off Acq Pur Sel

Cell Formulas
Range	Formula
U2:W2,AE2:AH2,BJ2:BQ2	U2	=T2+1
AF48	AF48	='Monies Drawn Down'!M6+AN46 +SUMPRODUCT(--(MOD(COLUMN(T59:LN59)-COLUMN(T59)+1,46)=0),T59:LN59) +SUMPRODUCT(--(MOD(COLUMN(T59:LN59)-COLUMN(T59)+1,46)=0),'COM BID Off Pur'!T59:LN59)
AF51	AF51	=SUMPRODUCT(--(MOD(COLUMN(V59:LN59)-COLUMN(V59)+1,46)=0),V59:LN59) +SUMPRODUCT(--(MOD(COLUMN(V59:LN59)-COLUMN(V59)+1,46)=0),'COM BID Off Pur'!V59:LN59)

 

[RoryA]

The COLUMN(T59:LN59)-COLUMN(T59)+1 part just returns the numbers you already have in row 2, so I don't know why you don't just use those?

 

[kvsrinivasamurthy]

Try In AF48

=INDIRECT("'Monies Drawn Down'!M46")+INDIRECT("AN46") +SUMPRODUCT(--(MOD(COLUMN(INDIRECT("T59:LN59"))-COLUMN(INDIRECT("T59"))+1,46)=0),INDIRECT("T59:LN59")) +SUMPRODUCT(--(MOD(COLUMN(INDIRECT("T59:LN59"))-COLUMN(INDIRECT("T59"))+1,46)=0),INDIRECT("'COM BID Off Pur'!T59:LN59"))

Since this formula passive function (INDIRECT) it may sloe down the excel.

 

[ozbeachbum]

The COLUMN(T59:LN59)-COLUMN(T59)+1 part just returns the numbers you already have in row 2, so I don't know why you don't just use those?

Hi,
I tried what I understood your suggestion to be with no luck.
Would you be good enough to provide more detail and I will give it another try.
Cheers.

 

[RoryA]

I mean you would replace this part of the formula:

--(MOD(COLUMN(T59:LN59)-COLUMN(T59)+1,46)=0)

with:

--(MOD(T2:LN2,46)=0)

so for example, AF48 becomes:

='Monies Drawn Down'!M6+AN46
+SUMPRODUCT(--(MOD(T2:LN2,46)=0),T59:LN59)
+SUMPRODUCT(--(MOD(T2:LN2,46)=0),'COM BID Off Pur'!T59:LN59)

 

[RoryA]

Is the formula in AF51 supposed to ignore columns T:U?

 

[ozbeachbum]

I mean you would replace this part of the formula:

--(MOD(COLUMN(T59:LN59)-COLUMN(T59)+1,46)=0)

with:

--(MOD(T2:LN2,46)=0)

so for example, AF48 becomes:

='Monies Drawn Down'!M6+AN46
+SUMPRODUCT(--(MOD(T2:LN2,46)=0),T59:LN59)
+SUMPRODUCT(--(MOD(T2:LN2,46)=0),'COM BID Off Pur'!T59:LN59)

Hi Rory,
Thanks for taking the time to help me.

Starting with your question "Is the formula in AF51 supposed to ignore columns T:U?" Yes it is.
For the result in AF51 what I was having the formula do was to SUM each 46th cell starting at "BO"
For the result in AF48 what I was having the formula do was to SUM each 46th cell starting at "BM"

With reference to the formula you so kindly provided row 2 is only a count to show the column number for reference to the formula,
EG: T:BM is 46 spaces and V:BO is 46 spaces.

I adjusted the formula as follows, however it returned VALUE.
='Monies Drawn Down'!M6+AN46
+SUMPRODUCT(--(MOD(T59:LN59,46)=0),T59:LN59)
+SUMPRODUCT(--(MOD(T59:LN59,46)=0),'COM BID Off Pur'!T59:LN59)

Thanks once again for all your time,
Cheers,
Dave.

 

[RoryA]

That is not going to work. If you can't use the values in row 2 for some reason, you need to go back to the version with COLUMN in it.

It might be better to ask why your formula is doing what it's doing - why do you need every 46th column? Whenever I see a formula like this, it tends to be due to a worksheet layout that is less than ideal...