Forum Discussion

upashi's avatar
upashi
Copper Contributor
Jun 10, 2022

Excel Automatic Formula

Hello!

Question : Is anyone here know how to automatically copy a formula in excel from one cells to different cells ? Non-Adjacent cells.

I want automatically copy the formula from the Master.

I tried goggling it, but all answer I found is apply automatic in entire column. I don't want that because I have spacing in the table. Please help me.

See attached sample table.

 

excel
Formulas and Functions

3 Replies

  • BeyK40's avatar
    BeyK40
    Brass Contributor
    Jun 10, 2022

    upashi 

     

    There is an easy way. You could use the IF formula combined with "ISNUMBER", see formula below.

     

    As you see, the blank columns has also got the forumla. 

     

    Forumla:  =IF(AND(ISNUMBER(B2), ISNUMBER(C2)),B2*C2," ") 

    Use the forumla in one cell, CRTL Shift Down arrow to insert it in the row. 

    • upashi's avatar
      upashi
      Copper Contributor
      Jun 10, 2022
      Hi,
      I want to remove the process of using the CTRL shift down.
      1. I want the remaining cells downward to automatically copy the formulas on the 1st cell. Every time I change the formula or the conditioning like lookup etc. I want it to apply automatically to the rest.
      Now : the formula is 1+1
      Then after sometime I will change it to 1-1 or with vlookup, hlookup, etc.
      Once I change the formula in Row D3, I want the remaining cells downward to update automatically without using the CTRL Shift Down Arrow. I want to skip that part. If there's a way to do that ?
      • BeyK40's avatar
        BeyK40
        Brass Contributor
        Jun 13, 2022

        upashi 

        Well, you just have to create a real table. The column with the answer is a calculated column. You just have to insert the formula in the first cell, and it will automatically copy the the rest. If you change one of the formula's, all of them will change.