Expert Solution - Excel IF Problems

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

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

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