Mar 02 2021 07:20 AM - edited Mar 02 2021 08:43 AM
Hello,
I have created a workbook with two sheets - the first ("Report") where an Excel report is downloaded from a software and pasted into the sheet and the second ("Calculations") where a series of Excel formulas pull specific portions of data from the "Report" spreadsheet.
For example, "Calculations" contains some of the following formulas:
What I am looking to do is create a formula or VBA that will look at the "Report" sheet and apply the formulas above to the same number of rows in "Calculations" - this means that if there are 50 rows of data in "Report" then there would be 50 rows of data in "Calculations". Currently, I have copied the formulas down a generic 100 rows and have a bunch of "#VALUE" or "0" where there is no corresponding data in the "Report" spreadsheet. If I ended up with more than 100 rows in "Report" then I would be missing out on data in the "Calculations" spreadsheet.
I've attached an example workbook for clarity. Any help would be greatly appreciated!
EDIT: I forgot to include that the data from the spreadsheet would be used for a mail merge.
Mar 02 2021 08:08 AM - edited Mar 02 2021 08:22 AM
@creidy Now, I'm not offering you the "smart" solution as I believe your situation can be quite easily handled by IFFEROR and IF, copied down to a range you feel comfortable with. A hundred or a thousand rows, that doesn't really matter. A smarter solution would entail formulae that would be neither straight-forward nor easy to maintain, whereas the attached (revised) workbook contains formulae that are. See for yourself and decide if you can live with it.
Alternatively, consider Power Query to connect to the "Report" and have it transform in the matter of seconds, no matter how short or long the Report is. Set it up once and you can refresh the query at any time.
Mar 02 2021 08:41 AM
Mar 02 2021 08:58 AM
Solution@creidy I use MailMerge once per year, so I'm not an expert here, but believe you can filter out records that meet a certain criteria. So, just don't include records that have "".
Mar 02 2021 09:10 AM - edited Mar 02 2021 09:13 AM
@Riny_van_Eekelen - You are Godsent. Seriously. The issue was that the blank time column still had a "date/time" in them. After I read this, I changed the filter to ignore anything without a first name, regardless of the other cell's content, and voilà! I honestly cannot thank you enough!
Mar 02 2021 09:17 AM
@creidy Most welcome!
Mar 02 2021 08:58 AM
Solution@creidy I use MailMerge once per year, so I'm not an expert here, but believe you can filter out records that meet a certain criteria. So, just don't include records that have "".