Hi,
I have formula in C column =SUM(INDIRECT(“Mapa.C5”):(INDIRECT(“Mapa.C7”))) and i would like to drag and copy same formula to other columns only change “C” to “D” like D column to have formula =SUM(INDIRECT(“Mapa.D5”):(INDIRECT(“Mapa.D7”))) drag and copy dont work.
Best regards
Automatically adjusting the INDIRECT parameter in your example obviously is not possible because it is a literal string.
However, you can use OFFSET to create a reference that always points to the first three rows, regardless whether rows will be inserted.
=SUM(OFFSET(C5;0;0;3;1))
The C5 then will be adjusted when copying the formula cell to a different cell position.
i try that one but again when i add new cells above C5 cell then =SUM(OFFSET(C5;0;0;3;1)) automatically change to =SUM(OFFSET(C6;0;0;3;1)) how to make static to be C5
i found solution need to change C5 to C1 =SUM(OFFSET(C1;5;0;3;1)) and i can add new cells above C5
Asking precise questions could yield precise answers. You never said you wanted to insert rows above the range and keep the same range. Anyway, good you found a solution.
What about =SUM($Mapa.C5:C7)?
i need to have indirect formula because i add some empty rows and move source data
I simply don’t understand, obviously. Sorry. The explanation tried in above comment doesn’t tell me anything relevamt.
Generally this may be a case of a premature fixing on a specific tool or on a first idea concerning a possible solution. Insisting on the usage of INDIRECT() in such a case, you will end up with monster formulae.