Forum Discussion
"Smart" Excel Formulas - Autofill based on number of rows in another sheet
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:
- =MID('Report'!A4,FIND(" ",'Report'!A4)+1,256)
- =LEFT('Report'!A4,(FIND(",",'Report'!A4,1)-1))
- =MID('Report'!G4,SEARCH("(",'Report'!G4)+1,SEARCH(")",'Report'!G4)-SEARCH("(",'Report'!G4)-1)
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.
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 "".
5 Replies
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.
- Riny_van_Eekelen I haven't even thought about a Power Query in a decade and would have to relearn it all again, but that is a fantastic suggestion!
Your "IF" formula solution was prefect for not viewing the information. What I failed to include in my original post (updating now) is that the data in "Calculations" will be used for a mail merge. Unfortunately, mail merge fully recognizes those cells as containing data and tries to tell me that there are 100 recipients listed. This might be the same issue with a Power Query, now that I think about it...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 "".
