June 26, 2020
I have good basic Excel skills, and no experience with VBA.
My company uses Excel to calculate estimated costs for our remodeling projects. The Scope of Work is entered as text in the column and rows next to the Item being estimated. I want to copy the Scope of Work text into an adjacent worksheet or separate workbook, so that the Designer can finish writing the scope of work, adding details, measurements ,etc., without having to use Copy/Paste between sheets/workbooks.
I found the IFSUMIF formula to determine if the Scope of Work needs to be copied from the Estimating Sheet to Scope of Work Sheet. See the screen shot. Logic: If QTY is more than Zero, enter X into a cell. If X, then copy the text from cells or range into another worksheet.
The master Estimating workbook has 30 +/- worksheets, not including the Scope of Work sheets for each Item. I can add the Scope of Work worksheets to the Estimating Workbook, but that will make a huge file of 65 +/- tabs.
1)Can this be done with formulas or should I use VBA?
2)Would the VBA code be different for each worksheet?
3)Would it be better to have the Scope of Work be a separate workbook, and use VBA to reference the Estimating Workbook? I don't know at what point the Excel program slows down in relationship to file size.
Thanks in advance for help, advice, suggestions.
October 5, 2010
If you can use a formula you should use it. I'd only use VBA when a formula can't do what I need.
In this case you can get the data from the 'Site Prep Estimate' sheet directly into the 'STPREP SoW Example Finished' sheet, no need for an intermediate cell to store an X and then read that cell's value.
In B4 on 'Site Prep Estimate' I've entered
=IF(SUMIF('Site Prep Estimate'!E5:E7,">0"), 'Site Prep Estimate'!B5:B7, "")
This will only work if you have dynamic arrays.
If you don't you can use the rather unwieldy formula in B15 instead
=IF(SUMIF('Site Prep Estimate'!E27:E34,">0"), 'Site Prep Estimate'!B26 & 'Site Prep Estimate'!B27 & 'Site Prep Estimate'!B28 & 'Site Prep Estimate'!B29 & 'Site Prep Estimate'!B30 & 'Site Prep Estimate'!B31 & 'Site Prep Estimate'!B32 & 'Site Prep Estimate'!B33 & 'Site Prep Estimate'!B34, "")
I'd keep all the data together for ease of use.
Also, merging cells is not a good idea. It causes all sorts of issues.