+ Reply to Thread
Results 1 to 9 of 9

Inserting a row that will make formulas follow

  1. #1
    Registered User
    Join Date
    07-22-2015
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    4

    Inserting a row that will make formulas follow

    Hi,

    I have an Excel Worksheet of which I made a simple demo of it here. ExcelForum.xlsx.

    I'll use the Excel Worksheet I attached as a demo to guide you through my problem.

    There is three rows. 1,2,3. In the first cell of the row, we have the row number.
    The second cell contains a value (which is normally determined by multiple if statements).
    The third cell contains another value like the cell to his left (again, determined by if statements in the real Worksheet).
    The fourth cell is to be checked if the row is assign to the column a.
    The fifth cell is to be checked if the the row is assign to the column b.
    The seventh cell is to get the total of each rows.
    The eigth cell is to determine if the row is assigned to a (if the column a is checked).
    The ninth cell is to determine if the row is assigned to b (if the column b is checked).
    In the row "total", we have the total of the column a and b (Sums) which comes from the same row but from the seventh and the eigth column. There is also an if statement to get the total of all the values in the seventh cell ( the total of each rows) crosschecked by the total of the columns a and b.

    Question : How do I instert a row to this table and that this row as the same formulas every rows has ? In other words, how do I insert a row that does not affect the already in place formulas but also get's the same formulas for it's own row number ?

    Thanks,
    OUO

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Inserting a row that will make formulas follow

    you mean like this, see the green cells.

    just copy the data to the cells below.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,072

    Re: Inserting a row that will make formulas follow

    Try converting the table to Table first (Insert tab> Table). Then insert a row. The formulas should stay? If not, which formulas are not. Tell us what the formula was before you entered the row and what it was after you entered the row.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    07-22-2015
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    4

    Re: Inserting a row that will make formulas follow

    It looks like that is what I am looking for indeed oeldere. That being said, it is not for me to use, it is for someone else therefore I would like to try to get it the simpler for them so they just it insert and it works. Something can do that?

    I'll try your solution ChemistB.
    Last edited by OUO; 07-22-2015 at 10:02 AM.

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Inserting a row that will make formulas follow

    I just inserted a row.

    After that I copied the formula in N9, O9 en P9 down to the last row.

  6. #6
    Registered User
    Join Date
    07-22-2015
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    4

    Re: Inserting a row that will make formulas follow

    Alright, since that works, I'll get the person to do that instead. Thanks oeldere.

    Thanks to you too ChemistB, I tried your solution but the formulas weren't following. (Can see it here :ExcelForum2.xlsx)

  7. #7
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Inserting a row that will make formulas follow

    You're welcome. We appreciate the feedback!

    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

    It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,072

    Re: Inserting a row that will make formulas follow

    Within the table, the same formula needs to be on each row.
    So, in your example, first I converted back to range.
    Made sure that the formulas are consistent in each row,
    Then selected A30:P36 (included 1 header row, didn't include grand totals). Insert > Table> Table has headers.
    Then right clicked in D35, Insert Rows above
    This row has formulas. See attachment.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-22-2015
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    4

    Re: Inserting a row that will make formulas follow

    Your solution works too ChemistB with a simple click with the mouse. Thanks.

    Both solutions works.

    1-Oeldere : Insert a line. Copy the upper line, Paste it into the new line and change the data.
    2-ChemistB : Convert your data into a table. Right-click the cell you want, Insert -> Row. Formulas followed.

    Thanks to both of you, future readers will be able to choose the one that fits their needs best.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Is there a way to make formulas follow cells?
    By mikey42979 in forum Excel General
    Replies: 3
    Last Post: 03-05-2015, 11:16 PM
  2. Follow huge PDF files name, auto inserting the file names in one worksheet
    By cherrychan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-08-2013, 11:00 PM
  3. make content follow a hyperlink
    By ltjohnson in forum Excel General
    Replies: 0
    Last Post: 06-12-2012, 08:58 PM
  4. VLOOKUP - How to make cell format follow?
    By Mr. Green Genes in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-28-2009, 03:13 AM
  5. [SOLVED] Can formats follow formulas?
    By Phrank in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-09-2006, 10:15 PM
  6. [SOLVED] Inserting rows and having the formula follow
    By workofiction in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 06-21-2005, 12:05 PM
  7. How do I make a row or information, follow as you scroll down..?
    By Dave_86 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-11-2005, 11:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1