Trying to remember a function to add a row below if the row above has data

Question

Working on creating a massive calculation spreadsheet (75 sheets). The only formula we are missing is the one where if there is data on the line above, the final sheet will automatically add a row below.

example:

Cell A6 = (sheet 16 A16)

Add a row below

Yes, I realize that is not proper formula notation. Yes, I realize this sounds a lot harder than it really it is. Yes, I realize I am probably not using the right terms to figure out what I want.

Let me try it this way -

If row 6 is occupied (has data), information is automatically added to row 7. All information is being pulled from other sheets in the workbook.

I know there is an IF in the function, but cannot remember (or find) the remaining part of the formula.

Please help.

mathetes · Answer

Galaxy_Admin&nbsp;&nbsp;in row 7, whatever cell -- let's say C7=IF(C6&lt;&gt;"",source,"")&nbsp;but that's assuming I'm understanding what you want, what you described. That formula will only do C7, however. If you want all the cells on row 7 to fill with whatever, you need to write similar formulas in each cell; it might be possible to construct it in a way that would enable copying the same formula across. You will need to give us a better idea of the "big picture"

galaxy_admin · Answer

Thank you&nbsp;mathetes&nbsp;. I believe the formula is a lot more complicated than that. I will be working on it more today, and if I figure it out, I will definitely post it.&nbsp;More along the lines of =(sheet:B6 = TRUE, (A6), FALSE), IF row2=true, DATA=row3)&nbsp;I know that is not accurate either, but closer to what I am trying to achieve....

mathetes · Answer

Galaxy_Admin&nbsp;I believe the formula is a lot more complicated than that. I will be working on it more today, and if I figure it out, I will definitely post it.&nbsp;More along the lines of =(sheet:B6 = TRUE, (A6), FALSE), IF row2=true, DATA=row3)&nbsp;I know that is not accurate either, but closer to what I am trying to achieve....&nbsp;Might I suggest that you start with a clear statement in words of what it is you're hoping to accomplish.&nbsp;So far your words themselves (as well as the attempts at formulas) have been confusing. &nbsp;For example, is it actually the fact that sheet(whatever),cell B6 will simply contain TRUE (or 1)?It certainly can't be the case that row2 will ever be TRUE. Rows, as such, don't contain values; they contain cells and the cells contain values.Is it acceptable to ask you what the massive workbook is about, what the context or bigger picture is? I see by your user name that you are administering the galaxy itself, so can't help but wonder if it has something to do with the heavens..... Just curious. It's not common to see workbooks with 75 sheets in them.

galaxy_admin · Answer

The worksheet is so massive because there are several different departments and parts and stuff divided up.It is basically trying to distill cost onto a single worksheet that will determine work order costs/estimates/billing. The other worksheets will be locked so the workers will only be able to access 2 sheets in the end - the sheet that provides both cost and end price, and the estimate/billing sheet. The designer knows there is a formula that, if the line above in the estimate is filled, the next item will populate the line below as pulled from the master cost sheet. Unfortunately, it has been a very long time since he has used the formula and has since forgotten it.Why is he doing it himself in Excel when there are estimating programs already written? Well, proprietary use and all that.Does that help explain what we are trying to accomplish?Thanks again for being persistent.

mathetes · Answer

Galaxy_Admin&nbsp;&nbsp;Does that help explain what we are trying to accomplish?&nbsp;Yes. And, sadly, no.&nbsp;Depending on how recently that&nbsp;memory was created, there's a relatively new function called FILTER that might have been in play. I have used it to populate additional lines in such things as invoices for work done. It uses criteria to determine what lines to pull from another sheet (so in that sense bears a resemblance to IF formulae).&nbsp;Here's a website with info on FILTER. And here, perhaps even more helpful, is a YouTube video with a couple of demonstrations.

Forum Discussion

Trying to remember a function to add a row below if the row above has data

Share

Resources