Forum Discussion
inserting new lines
Team,
I´m creating a table where i have a line with cells that users can fill and other to display the results (in grey with formulas).
The idea is to give that table with a single line and, whenever user start filling the cells, autocatically a new line is generated but copying the formulas from the first line.
| Start | End | Work day | Non-Work day | Total Nb of days |
| 01-mar-18 | 31-mar-18 | 22 | 9 | 31 |
In conclusion, for each line, when a user start filling the "start" and "end" cells, a new line is automatically generated and the formulas of the cells "work daw", "Non-Work day" and "Total Nb of days" are copied to that new line.
Any help please?
Can it be done without using macros?
Hi Jose,
If you start Excel Table, add formulas in first line using structured references and check if your auto-correct options have default settings as here
it shall work as you say.
Hi Sergei,
Thanks for your reply. However you have to go slow with me :). I´m not an expert on excel.
For example, the picture that you have sent to me where can I find those settings? On the File Tab? And what you men by structured references? Sorry if I´m bothering you with my questions.
Attached is a sample of a table about what I'm looking for. If I give the table with just one line asking a user to fill it (the yellow lines) as soon is starts to fill it, a new line will be created by carrying the formulas (grey cells) to the new line being created. Grey cell will be protected.
Thank you again.
Hi Jose,
If you are on Excel 2007 or any later you may create the table, here is how
http://www.excel-easy.com/data-analysis/tables.html
Within the table you may use refrence on column name instaed of refrence on cell address, here is how it works
In your case the table could look like
and the formulas within it will be
=NETWORKDAYS([@Start],[@End]) =[@End]-[@Start]-[@[Work day]]+1 =[@[Work day]]+[@[Non-Work day]] =[@[0800-2000]]
Mentioned setting shall work by default, you may find them at
File->Options->Proofing->AutoCorrect Options->AutoFormat As You Type
As soon as you enter any value into the cell under last row of the table the table row with formulas if any will be added automatically.
In the attached file is the sample in next to your sheet
