• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy above rows formulas in below blank cells

vijay.vizzu

vijay.vizzu

Member
Dear All,


I have a problem which i am facing regularly. I just want the solution for, in any sheet if i insert blank rows, then it can't copy the formulas which above row contains. Assume that i have an data in A1:G10, every cells contains some formulas, if i insert blank row or rows in between A3:G6, then it can't copy the above rows formula, So i requested you to all, Is there any idea to do this everytime when a blank row or rows inserted then it can copy the above rows formulas automatically.


Thanks in advance.

Vijay
 
Vijay


There are ways to rewrite formulas to allow for this


Can you post an example Formula?
 
vijay.vizzu


do you mean that if you add rows between A3:G6, then formulae on row 7 will break away from anything on row2?


or do you mean if you insert rows 3 to 6 all formulae from row 2 to row 7 would fill in automatically so that you don't have to hi-light row 2 and and copy them down to row 7 to make the entire table formulae consistent?
 
Dear Hui,


This type of formulas i have in my sheet. I just pasted the formulas please view . In Row 11, in cell W11 = =IF(ISBLANK(V11),"",V11+5), in Y11 = =IF(ISBLANK(V11),"",V11+21), in AF11 = =IF(AE11="CAPITALIZE",(AD11/120000)+AC11,AC11)


So if i insert a blank row(s) in below Row No.11, then it should automatically copy the the Formulas from Row No.11 to inserted blank row(s).


Thanks

Please give any suggestions.
 
Vijay


Please disregard my answer above, I misread the question.


I don't think Excel does what you want
 
Dear Hui,

Sorry for my explanation to you. It can be done through VBA Code, but i don't know, how to write VBA Code in excel and your are the magician in excel VBA. So now i am explaining to you my problem. VBA Code to be like this. Insert blank row from a active cell, code need to check the cells in above rows which contains formulas, if no formula then it remains same, if yes, then it will copy down to the inserted blank row.


Please try this pattern, i need your help


Thanks

vijay
 
Hi, vijay.vizzu!

I don't know what Excel version are you using, and I don't remember how 2003 behaved, but with 2010 and I think I remember that with 2007 too, if you have formulas in row 11 on certain cells, when you begin typing in row 12 (or selecting cells) it automatically copies formulas from previous rows.

Regards!
 
Hi SirJB7,


Thank you for your comments. Currently i am using Excel 2007, if i insert one row and begin typing then some cells will automatically copy the above cells formulas but some not, and if i will insert multiple rows at a time then it will not copy the formulas by typing. i found some blogs regarding "insert blank rows and copy formula from above cells". All are in VBA Code. So i can't able to modify that one as per my requirement.


Thanks

Vijay
 
You must log in or register to reply here.
Back
Top