**Question description:**

*This user has given permission to use the problem statement for this blog.*

I would like to have Excel look at a cell and if there is any # in it, to automatically add a new row below the current row and copy the previous row and paste it in the new row. If there is nothing in the target call, then do nothing. I dont know if it can do this, can it be done?

Solved by T. U. in 48 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
21/05/2018 - 08:05

Hello there.

Excelchat Expert
21/05/2018 - 08:05

Welcome to Got It Pro.

User
21/05/2018 - 08:05

hi

Excelchat Expert
21/05/2018 - 08:06

It is possible to add a new row based on the previous cell having a "#"

User
21/05/2018 - 08:06

OK, how do i do that?

Excelchat Expert
21/05/2018 - 08:07

But that requires VBA.

User
21/05/2018 - 08:07

what is "VBA"?

Excelchat Expert
21/05/2018 - 08:07

Visual Basic for Applications.

Excelchat Expert
21/05/2018 - 08:07

It is like programming for Excel.

Excelchat Expert
21/05/2018 - 08:08

We currently do not cover VBA and Macros.

User
21/05/2018 - 08:08

so, do i need to create a macro?

Excelchat Expert
21/05/2018 - 08:08

yes.

Excelchat Expert
21/05/2018 - 08:08

You can have another alternative.

Excelchat Expert
21/05/2018 - 08:09

You can have two tables and update the next one based on the previous.

Excelchat Expert
21/05/2018 - 08:10

Will that be okay?

User
21/05/2018 - 08:10

I am sorry, I do not follow you. you mean create 2 Tables in 1 tab, or in 2 tabs?

Excelchat Expert
21/05/2018 - 08:10

Whichever you prefer.

User
21/05/2018 - 08:11

I would prefer everything in the same tab please

Excelchat Expert
21/05/2018 - 08:11

To do it in one table you'll require Macros. But if there are two tables(not important on the same tab or on a different one) it can be done with formulas.

Excelchat Expert
21/05/2018 - 08:12

Alright, could you please clear a few things at first.

User
21/05/2018 - 08:12

can you please show me the formula to do it in 2 tables

Excelchat Expert
21/05/2018 - 08:12

Sure.

User
21/05/2018 - 08:12

what things?

Excelchat Expert
21/05/2018 - 08:13

By "#', do you mean any values, numbers or the sign "#"?

User
21/05/2018 - 08:13

it would not be the # symbol, it would be a number, from 1-999

Excelchat Expert
21/05/2018 - 08:14

Alright.

Excelchat Expert
21/05/2018 - 08:15

Could you please have a look at the Excel Online panel on the middle of the screen?

User
21/05/2018 - 08:16

I am looking

Excelchat Expert
21/05/2018 - 08:16

Is this what you wanted?

User
21/05/2018 - 08:17

No, sorry it is not.

Excelchat Expert
21/05/2018 - 08:17

Please make any necessary changes on how you would want to see the output.

User
21/05/2018 - 08:18

What I want to do is look at a cell, like A$ and if there ia a number in it, to inser a new row below that cell, so it would be new Row 2, and copy the contents of row 1 into new row 2 (Row 1 will have some basic formulas like sums in it that I want to have duplicate into new row 2.

Excelchat Expert
21/05/2018 - 08:19

Alright.

User
21/05/2018 - 08:23

Ok, so all I want is to look into cell A5 and if there is any number, to insert a new row 6 below it and copy the contents of row 5 into new row 6, if there is nothing in A5, do nothing.

Excelchat Expert
21/05/2018 - 08:24

Okay.

Excelchat Expert
21/05/2018 - 08:24

To insert a new row is not possible.

Excelchat Expert
21/05/2018 - 08:25

Without VBA. What I can do is have a copy of the same table with another row/ keep the A6 cell blank at first.

User
21/05/2018 - 08:26

No, I do not want to create another table, i want the same table to expand by one row as it sees that I am populating the last row that is there.

Excelchat Expert
21/05/2018 - 08:28

Well, that isn't possible without VBA, what I can offer is to keep the bottom cell of the last one blank at first.

Excelchat Expert
21/05/2018 - 08:28

Let me show you

Excelchat Expert
21/05/2018 - 08:30

What you can do is see how long your table is.

Excelchat Expert
21/05/2018 - 08:30

And the next line after the table will end add this formula before you insert the table

Excelchat Expert
21/05/2018 - 08:30

=IF(ISNUMBER(A5),A5,"")

Excelchat Expert
21/05/2018 - 08:30

this one to the Qty row.

Excelchat Expert
21/05/2018 - 08:30

=IF(ISNA(A6),"",B5)

Excelchat Expert
21/05/2018 - 08:31

This to the items and copy it to the ones at the right.

Excelchat Expert
21/05/2018 - 08:31

Would the sum in A7 and D7 include the duplicated row?

User
21/05/2018 - 08:33

yes, the sums would need to capture the numbers in the newly added rows.

Excelchat Expert
21/05/2018 - 08:33

I've included them to the sum. Will it work?

User
21/05/2018 - 08:35

So, as i understand, the formulas in row 6, look in the cells above them in row 5 and if there is a number in A5 or A6, or B6, then it duplicates the contents above it? But there is no new row, the rows are already there?

Excelchat Expert
21/05/2018 - 08:36

Yes, you have to have an extra row before you insert the table.

Excelchat Expert
21/05/2018 - 08:37

This extra row will work as the new row. It is not possible to enter the row dynamically without Macros.

Excelchat Expert
21/05/2018 - 08:37

And I apologize but we do not currently cover VBA/ Macros.

User
21/05/2018 - 08:38

Ok, but if I have a table that has 50 rows then I have to have 49 empty rows below the one I am working in, correct? ad th esum functions in A7 and D7 need to already be set to sum up all 50 rows? correct?

Excelchat Expert
21/05/2018 - 08:40

Do you need to check the last row only or all the rows?

User
21/05/2018 - 08:41

I do not understand your meaning, what do you mean "check" the rows? do you mean sum the contents of all rows in a column? yes. we need to sum the columns up?

Excelchat Expert
21/05/2018 - 08:42

No, what I meant was you asked me to look into cell A5 only.

User
21/05/2018 - 08:43

Oh Ok, yes, just to look at the last row and to see if it is populated, then that is the signal to add another row in the table.

Excelchat Expert
21/05/2018 - 08:43

Did we need to check cells A1:A4 as well?

Excelchat Expert
21/05/2018 - 08:43

Oh, that will need only one more row

User
21/05/2018 - 08:43

no, there is no need to check cells A1-A4

Excelchat Expert
21/05/2018 - 08:43

Alright.

Excelchat Expert
21/05/2018 - 08:44

Then, you will need only one more row at the bototm.

Excelchat Expert
21/05/2018 - 08:44

*bottom.

Excelchat Expert
21/05/2018 - 08:44

Let me show you

Excelchat Expert
21/05/2018 - 08:44

Say, we are going to have a table of 50 rows, For display purposes I'll just copy the data we have here.

Excelchat Expert
21/05/2018 - 08:44

Take a look.

Excelchat Expert
21/05/2018 - 08:45

So, here I have a table.

Excelchat Expert
21/05/2018 - 08:45

It has 50 rows.

Excelchat Expert
21/05/2018 - 08:45

Say this was your initial table.

Excelchat Expert
21/05/2018 - 08:46

Now in the beginning, you need to keep one extra row vacant, in our case, it is row 51.

User
21/05/2018 - 08:46

OK

Excelchat Expert
21/05/2018 - 08:47

In this row, we'll check if row 50 cell A50 has a number or not.

Excelchat Expert
21/05/2018 - 08:48

with this formula

Excelchat Expert
21/05/2018 - 08:48

=IF(ISNUMBER(A50),A50,"")

Excelchat Expert
21/05/2018 - 08:48

And for B51, we will use this formula, =IF(ISNA(A51),"",B50)

Excelchat Expert
21/05/2018 - 08:48

And copy it for C and D.

Excelchat Expert
21/05/2018 - 08:49

Now we will sum the Qty and Total costs.

Excelchat Expert
21/05/2018 - 08:49

Or you could sum at before you add the formulas to A51:D51

Excelchat Expert
21/05/2018 - 08:50

Did you understand it? Will this be okay?

User
21/05/2018 - 08:50

OK , i see the point, the sums have to be done after we know the final number of rows that are created.

Excelchat Expert
21/05/2018 - 08:50

Yes. That is one perk without Macros.

User
21/05/2018 - 08:51

The problem is that this method just copys the contents of the cells above it, not the formulas, which is what I want to copy, the formula in cell D50, not the contents in D50.

User
21/05/2018 - 08:51

So, I see that I really need to use a Macro for this problem

Excelchat Expert
21/05/2018 - 08:52

I am afraid so.

Excelchat Expert
21/05/2018 - 08:52

Yes.

User
21/05/2018 - 08:52

Ok thank you very much for your time today

Excelchat Expert
21/05/2018 - 08:52

You are welcome.

Excelchat Expert
21/05/2018 - 08:52

Thanks for using Got It Pro.

Excelchat Expert
21/05/2018 - 08:52

Have a great day.

User
21/05/2018 - 08:52

Bye

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*