Automatic row insert with formulas

July 16, 2013 at 23:02:40
Specs: Windows 7
Hi, I wonder if you can help please?
I have a Excel 2010 spreadsheet with formulas that are locked so that they can't be changed by anyone but me but others people can enter data.
I need a way to automatically insert a new row, keeping all the formulas, when data is entered in a given cell. For example, Cells 'I40' and cell R40 automatically enters a total, when either of those cells is full I need it to trigger a new row with all the formulas from the rest of row 40.
Is this possible please
Regards
Bill

See More: Automatic row insert with formulas

Report •


#1
July 17, 2013 at 04:09:51
re: "Cells 'I40' and cell R40 automatically enters a total, "

I'm not sure what you mean by that. Cells can't "enter" anything.

What causes the "automatic" total in those cells?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 17, 2013 at 07:21:40
I have practically no knowledge of the terminology of, or how excel works, Nor do I know anything about Makro's or VBA Code, so please excuse my ignorance.

This is the layout of the sheet

Row 40 has 19 columns, B to J are for expenditure entries and cells K to S is for income entries.

Cell A40 is blank, B40 has a date, C.D and E are blank. F is for the client to enter an amount (Let's say $100) G is also for the client to enter an amount.
Cell H has a formula that automatically shows a tax amount based on the amount in Cell G and Cell I automatically shows the sum of Cells F and G. Cell J is blank.

Cells K to S has the same information as cells as B to J

It would be ok if when the client enters the date in either Cell B40 or Cell K40 another row with the formulas automatically opens underneath, and the so on for subsequent rows

Does that help?


Report •

#3
July 17, 2013 at 20:44:53
Is there already something in Row 41? If not, can you populate the rows below 40 with your formula's so that they are ready and waiting for data entry?

The only way to have Excel automatically insert a Row is to use a macro. That, in and of itself isn't bad, but the fact that you mentioned "client" could be an issue.

In order for macros to work, they must be enabled within the Excel application of the machine on which the workbook is being used. You can't force a user to enable macros, you can only ask them to. If they choose not to enable macros or don't feel safe doing so, the code won't run.

Do you see that as being an issue?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
July 18, 2013 at 00:29:00
Rows go down to row 473. So yes, row 41 shows the totals of Cols F,G,H,I,O,P,Q and R.

The programme is divided into 12 sections, one for each month of the year and the information in section 1 (month 1) is replicated in the other 11 sections.

I don't see Macros being an issue but I need to lock all formulas (I've worked out how to do that) so that clients can't alter or copy them.


Report •

#5
July 18, 2013 at 04:07:09
You have asked to insert a row only at row 40. Is that your only requirement?

Client enters data in B40 or K40 and a new row 41, populated with the formulas from row 40 appears.

That's it. Nothing else in any other monthly section. A one time insertion of a new row 41, right?

VBA code is very specific so if you have other requirements it's best to give them to us up front.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
July 18, 2013 at 06:55:10
Oh dear, this is getting complicated! (For me anyway!)

Your second sentence "Client enters data....." is spot-on but the client may want to add many rows

Good thinking on your part regarding more data

As it stands at the moment, I would need to be able to do the same at rows 79, 118, 157, 196, 235, 274, 313, 352, 391, 430 and 469 so that the row after these numbers becomes a new row.

I for-see a problem here, because if you add rows after row 40, then the other row numbers will change accordingly, will that not effect the formulas?



Report •

#7
July 18, 2013 at 07:04:47
It occurs to me that Columns H and Q might be a problem, they are populated with formulas that will create a % figure of G and P. These percentage figures can be changed in cell H4 to any figure from 1% to 100%

Report •

#8
July 18, 2013 at 09:53:26
re: I forsee a problem here, because if you add rows after row 40, then the other row numbers will change accordingly, will that not effect the formulas?

Depending on how the formulas are constructed, it may or may not effect the formulas. Excel has provisions for allowing rows/columns to be inserted/deleted and letting the formulas update accordingly. If Excel didn't allow for those types of changes...well, can you imagine having to update every formula - manually - whenever you made that type of change? So no, that may not be an issue. You could manually insert a row right now and see if it causes a problem.

The bigger issue in my mind, and the reason I asked for more details into your requirements, is because you said essentially what I expected you to say.

"I would need to be able to do the same at rows 79, 118, 157, 196, 235, 274, 313, 352, 391, 430 and 469 "

So the client enters data in Row 40, a new Row 41 appears and Row 79 becomes Row 80, Row 118 becomes Row 119, etc. So now that code has to deal with changes to Row 80, not 79 and Row 119, not 118, etc. But, as they say in the commercials "wait...there's more!"

Once the user enters data in the "old Row 79, now Row 80", and a new Row 81 is inserted and Row 119 (which used to be 118) now becomes Row 120, and so on and so on.

As you said "Oh dear, this is getting complicated!"

What this means is that we can't use Row numbers to decide where to insert Rows, we need to use existing data in the Row where the data was entered to trigger the insertion. Is there something unique in each of the Rows where data is entered that the code could check to decide whether a new row needs to be inserted?

In English, not VBA code, I'm looking for something like this:

"If user data is entered in Column B or Column K and Column A of that same row equals (insert unique data here) then insert a row below the current row."

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
July 19, 2013 at 02:02:37
Columns B and K are both used only for entering the date.
Column A is not used at all, except as a border which is coloured red.

Could it be that if a date was entered in either column's B or K and say something like an asterix or a code in column A40 that it could be made to work that way?

The only columns that have code in them are H which has the formula =(G40-(G40/(1+$H$4/100))) Column I which has the formula =SUM(F40:G40) Column Q which has the formula =(P40-(P40/(1+$Q$4/100))) and column R which has the formula =SUM(O40:P40)

The formulas in columns H and Q change dependent on which percentage rate is chosen in cell H4, at the moment the % rate is at 12.5%

The formulas in column I would change to =SUM(F41:G41) when row 41 was added and so-on


Report •

#10
July 19, 2013 at 07:13:10
Maybe I'm making this harder than I need to.

Are the rows in which the user will enter the dates the only rows where changes will be made? Do we really need to be concerned with row numbers or should we only be montioring Columns B & K and inserting a row below whatever row the date was entered in?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
July 19, 2013 at 10:17:16
Providing that row 41, as it would be, is populated with the same formulas as Row 40, that would be fine.

Rows 5 to 40 are for customers to input data.

What is Row 41 at present, shows totals for Columns F,G,H,I and O,P,Q,R

Instead of having so many lines, if I took out rows 6 to 40 and just had it so that as you enter the date in B5 or K5 the next row is populated with the formulas from row 5 and so on, would that make it easier?

I could then do the same with all the other months


Report •

#12
July 24, 2013 at 00:31:02
Hi
I have just received your email, but when I click on the link there isn't any comment.
Is there a problem?
Regards

Report •

#13
July 24, 2013 at 05:07:34
What email did you receive?

I didn't send any email.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#14
July 24, 2013 at 05:44:08
THIS IS THE MAIN PART OF THE EMAIL I RECEIVED DATED WED 24TH JULY AT 08-02;

We just wanted to let you know that you have not marked a "Best Answer" for your Computing.Net Question. Choosing a Best Answer is very easy and assists Computing.Net in determining the best way to provide technical support to its users.

To choose a Best Answer, simply view your posting and click the "Set As Best Answer" icon for the response that was the most helpful in solving your problem. Use the link below to view the posting.
http://www.computing.net/answers/of...


Report •

#15
July 24, 2013 at 08:04:07
Those emails are system generated, not something I send or have anything to do with. I just answer questions as time allows. Obviously there is no need to mark a Best Answer since I don't think we've resolved your issue.

I'm still not 100% sure how you are using the spreadsheet, so it's hard for me to offer any code. You started by asking about Row 40, then you said there are rows for each month where data is input, then you said "Rows 5 to 40 are for customers to input data" and then talked about deleting rows 6 - 40 and then do the same with all the other months.

Frankly, I'm lost.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#16
July 24, 2013 at 10:44:52
Don't despair, I am very appreciative of your help and feel that we are nearly there.
Is there a way that I can send you a sample of the worksheet?

Report •

#17
July 25, 2013 at 00:23:42
Overnight, it occurs to me that I may have asked the question in the wrong way, making it unnecessarily complicated. So let me start again.

In Cell B5 I have a date, C5, D5 and E5, are blank, F5 is for accounts figures, G5 is for accounts figures. H5 has Formulas in it as does Cell I5.

I have exactly the same information replicated in Cells K5 to R5

When I enter a date in Cell B5 or K5 I want a new row to automatically appear below but keeping all the formulas from the row above and replicate a new row every time I enter a date.


Report •


Ask Question