How to automate all process?

**janernevis** · 12-01-2010, 09:50 AM

Hi,
I'm writing from Croatia and just now I found this forum so first of all, greetings to everyone.

I'm working at this kind of job in which i have to enter large amounts of data on paper.
Excel 2007 could help mi to automate this process.But I don't have great experience in it.
So I'm asking you for help.
In attachment I give you excel table for example (all my tables are on the same principle).
In this table I would like automate this:

1.I have row with big title with green fill, semi big title bold, and normal title.Now I would like to automate
inserting new rows with same futures as that 3 types of rows.For example that I just select which type of row to insert and where
and to enter different name for that row.

2.I want to automate formulas.For example cell D22 is summing all semi big titles.If I insert new one it won't sum that title as well.
So, would like in D22 formula that sums all semi big titles until it comes to next Big title.
The same thing I want for semi big titles compered to normal titles.

3.Also the problem is C74 (4.BIG EXAMPLE 4) because this row doesn't have any subcategory but it could.
I want solve this problem in a way that if there is no subcategory I enter data in Big Title row and if I insert subcategory, either semi big or normal title,
to sum this subcategory and that then Big Title row is same as 1. BIG TITLE,C22 in my table,that means to change automatically.

4.I would like to protect this table so none can accidentally change it or see formulas.

5.Would like if this can be done as much as possible without using vba, and if you could explain me how you automate this table so I can apply that on my other tables and not bother you again.

Sorry for big post and my bad English

would be very grateful if you could help me in this.

Regards,
Janer Nevis

**Richard Buttrey** · 12-01-2010, 10:11 AM

Hi Janer, and welcome to the forum.

Re 2 and inserting rows.
In the case of D22 and all the Big total rows, you needn't worry about these formulae since as you add new rows the formulae will change and always remain linked to the dependent semi-Big totals. For instance if you were to add a row at say row 26, D22 would change so that is summed D23, D34, D37, D44 & D47

Re 3, and the subcategories. The trick here is to always include a blank row below the last example number in each section. i.e. add a blank row under row 32 - you can hide the row if you wish so that you don't see it. Now just make sure .that new examples are added above the blank row and your sub category totals, e.g D23 always include the last blank row, i.e. =SUM(D24:D33). As you add new examples the SUM range will expand automatically.

Re 4 and protecting the formulae.
When you protect the sheet (Home, Format, Protect Sheet) all the cells will by default become locked. Before you protect the sheet you therefore need to turn off the locked status on all the cells which are not formulae and where you want users to enter data. (Home, Format, Format Cells, choose the Protection tab and untick the 'Locked' parameter for the cell).

HTH

**janernevis** · 12-01-2010, 01:58 PM

Originally Posted by Richard Buttrey

Hi Janer, and welcome to the forum.

Re 2 and inserting rows.
In the case of D22 and all the Big total rows, you needn't worry about these formulae since as you add new rows the formulae will change and always remain linked to the dependent semi-Big totals. For instance if you were to add a row at say row 26, D22 would change so that is summed D23, D34, D37, D44 & D47

Hm, this doesn't solve my problem. The problem is if I add new semi-Big Title D22 won't sum it because it sums only predefined cells (D23, D33, D36, D43, D46) and it is not predefined to sum only semi-bold cells until it comes to Big Title cell.So this doesn't automate my process.I would have to do this manually again.

Re 3, and the subcategories. The trick here is to always include a blank row below the last example number in each section. i.e. add a blank row under row 32 - you can hide the row if you wish so that you don't see it. Now just make sure .that new examples are added above the blank row and your sub category totals, e.g D23 always include the last blank row, i.e. =SUM(D24:D33). As you add new examples the SUM range will expand automatically.

Sorry if I misunderstood you but this could help only if you gave me answer on how to automatically add predefined row. For example if this can be done or something similar: I want add new semi-bold row.So I select Insert and then Excel ask me which type of row I would like to add: Big,Semi-big or Normal and I select semi-bold.

Re 4 and protecting the formulae.
When you protect the sheet (Home, Format, Protect Sheet) all the cells will by default become locked. Before you protect the sheet you therefore need to turn off the locked status on all the cells which are not formulae and where you want users to enter data. (Home, Format, Format Cells, choose the Protection tab and untick the 'Locked' parameter for the cell).

But can I hide formulas in Fx field?

Thanks man on this help. I think we are going to succeed something with this if I explain you right what I want with this table.

Regards,
Janer

**Richard Buttrey** · 12-01-2010, 03:05 PM

OK, In that case I'd be inclined to use an additional code column and enter relevant codes for each semi Big line. e.g 1 on rows 23,33, 36, 43 & 46, 2 on rows 52, 71, etc.
Then instead of a SUM() formula in D22 (and assuming your using column A as the helper code column) use for example SUMIF(A23:A1000,1,D23:D1000)

Not sure what you mean by the Fx field but you can 'hide' formulas by ensuring they are locked, and when you protect the sheet ensure you don't have the 'select locked cells' parameter ticked. I noticed that you had all the sheet protect parameters ticked.

Rgds