Copy formula to below cell

Hi All,

I want copy formula to many cells..

my formula looks something like this..

Please Login or Register  to view this content.

if i copy the above formula to cell below it, then it will take it as shown below

Please Login or Register  to view this content.

Similarly for all below cell if i keep on copying it ll take A3,A4,A5 etc..

But my requirement is, it should take B1,C1,D1 etc after A1..

is it possible or should i manually add formula to each and every cell?

Thank you.

Originally Posted by dilipandey

Hi szpt9m,

Firstly, you are taking sum of just one cell.. so the result would always be the same as it is there in that cell. Better do copy paste special transpose
Also, Indirect function can work but -1) Text.xlsx should be open for indirect function to work as it doesn't work on closed workbooks 2) you need to have A,B,C etc already present in a column so that you can reference them in indirect function.


Regards,
DILIPandey
<click on below * if this helps>

Sorry.. it was my mistake.. its not SUM.. it just the value of that cell i want.. it will be something like this..

Please Login or Register  to view this content.

And Test.xlsx cant be opened always.. i want the values to be fetched even if it is closed... if i copy the formula to each cell then it will work.. but i have lot of cells where i need to copy this formula.. so if i try to copy it will take A2,A3,etc but i want it to take B1,C1 etc..

Originally Posted by Fotis1991

Give a try to this...

=INDEX('C:\[Test.xlsx]Sheet1'!$1:$1;ROW(A1))

Copy down.

Nopes.. if i copy down it will take A2 A3 A4 but i want it to take B1 C1 D1 and so on

Originally Posted by Fotis1991

.....................................

looks good... is there any check i can add before copying the formula? like, if any cell is empty in row(A1) the corresponding cell inr result should also empty..right now in your excel file.. if i remove value in C1 which 3.. the corresponding value in reslut which is C6 shows 0.. i want it to be empty as well.. is it possible??

Originally Posted by Fotis1991

Try

=IF(INDEX($1:$1,ROW(A1))=0,"",(INDEX($1:$1,ROW(A1))))

Thanks a ton.. its working :t

Hi One small problem with the formula which you gave..!

everything is working except one small issue..

if the value of the cell is zero, then it should put the same value in the resultant cell but instead it is putting blank cell..

only if the value is blank(negatives not allowed) then i need to use blank cell otherwise whatever the value is there in the cell i need to use the same value in my resultant cell.. pls help

Originally Posted by FDibbins

=if(index($1:$1,row(a1))<0,"",(index($1:$1,row(a1))))

Nopes this is putting zero if there is any blank cell!!

Below formula is somewhat working for me!!

Please Login or Register  to view this content.

Thank you