Ask Your Question

How to 'drag-copy' a formula to the same row and modify (only) the column parameter? [closed]

asked 2018-05-16 12:28:20 +0100

unknown1001 gravatar image


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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2018-05-16 17:58:31.901448

2 Answers

Sort by » oldest newest most voted

answered 2018-05-16 13:04:08 +0100

erAck gravatar image

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.


The C5 then will be adjusted when copying the formula cell to a different cell position.

edit flag offensive delete link more


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

unknown1001 gravatar imageunknown1001 ( 2018-05-16 21:33:52 +0100 )edit

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.

erAck gravatar imageerAck ( 2018-05-17 13:22:13 +0100 )edit

answered 2018-05-16 12:35:38 +0100

Lupp gravatar image

updated 2018-05-16 12:36:12 +0100

What about =SUM($Mapa.C5:C7)?

edit flag offensive delete link more


i need to have indirect formula because i add some empty rows and move source data

unknown1001 gravatar imageunknown1001 ( 2018-05-16 12:43:58 +0100 )edit

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.

Lupp gravatar imageLupp ( 2018-05-16 12:52:08 +0100 )edit

Question Tools

1 follower


Asked: 2018-05-16 12:28:20 +0100

Seen: 57 times

Last updated: May 16 '18