I created a workbook for project estimating for my office and cannot figure out how to insert rows with formulas (without copying). This form is used by all and many have challenges when it comes to spreadsheets. I would like to have it so that one could select a cell in the appopriate section and either use a keystroke or button to add rows in that section with the formulas of the line above or below. I tried all the web advice I have found and I can get the macros to add rows but not the formulas.
You will see by the attached file I am not a developer and my expertise is limited to say the least. If anyone knows and will share I will be grateful!
Thank you.
There are several tabs in your file. In which tabs should the row be inserted with formulae?
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Thank you for responding Arlette.Originally Posted by arlu1201
The numbered tabs (10-50, 55-85, 90-120, etc.) are where the extra rows may need to be inserted. There are three rows under every "task" but more are almost always needed depending on the size of the project being estimated. I used three rows throughout for ease of entering the formulas, but every job is different. Say I wanted to add more rows under task 10-Labor and task 20-Materials, is it possible to add a button either on the ribbon or floating where one could click on C8 or C13, the press the button to add rows with the formulas from above?
I populated Phase 10 of the workbook so you can see what I am talking about but can get the file to upload. I can email it to you or if I figure out how to upload it I will let you know.
Thanks again.
Yes, its possible. Once you click on the cell and then on the button, the rows can be inserted.
To Attach a File:
1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
I can select the file and select upload but it loads with an I/O error and I cannot complete the action. It may be my office's security as I uploaded with no problem from a different office the day before yesterday. I will try from home this afternoon.
Vickie
Sorry it didn't work at home either. I have contacted the forum for help. Hopefully you can look later.
Did you remove your previous attachment? It is no longer visible in post 1.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Yes I did. I thought maybe it was preventing me from uploading a new one. Now I can't upload anything. Haven't heard back from the website yet.
Arlette,
I was able to upload the populated file. See tab 10-50. I highlighted where potential rows would be added. I would like to be able to add them to any "task" on any of the numbered tabs. Let me know if you have any questions and thank you so much again for taking a look.
Vickie
Actually there are several colors in your file. Which are the rows in the 10-50 tab where rows & formulae need to be inserted?
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
I should have said yellow highlights. Sorry!
Convert each section to an Excel "Table" or "List".
1) Highlight B5:AC8
2) Press Ctrl-L
3) Make sure "[x] My List has Headers" is checked. then click OK
4) Now highlight D5:AC5 and color the font blue to match the background.
Repeat with other sections.
Excel's LIST/Table function should copy formatting/formulas down into any new rows that are inserted. Also, when you click INSIDE any of these tables Excel will present you with an expansion row at the bottom of the table, type anything in that row and it will become permanent.
I've done the first few tables for you in the attached...
Last edited by JBeaucaire; 02-13-2012 at 06:24 AM.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thank you very much for looking at the spreadsheet. I tried the table solution but it isn't ideal because of the tabs it inserts in the headers. I don't want there to be any sorting or any other selection options other than to insert a row where needed.
I do appreciate the information. Thanks!
I decided to go with the table scenario but instead of selecting "[x] My List has Headers" I instead didn't include the headers, added a new row, then hid the two rows at the top so the drop down buttons don't show up and confuse things. I think it's going to work well. Thank you to all who advised!
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks