Forum Discussion
Formula that can update automatically each month to show monthly KPI
Hi Matt,
Thank you for your help. I'm attaching the document I'm creating based on the template. I figured out how to add text to formulas and got the %monthly change section working in the "email open rate" box on the summary tab. What I'm really stuck on is the numbers in large font in the template. Somehow the template updates those numbers whenever a new month's data is entered on the other worksheets. I can't figure out how to get Excel to find the new month's data to update automatically. If I link the summary tab cell to a single month's cell in another worksheet, I'll have to redo the formula each month, which would defeat the purpose of the quick dashboard view.
Please see attached for a dynamic way of calculating some of the metrics you would like. I have completed the Email Open Rate and Email Opens using Index Match Match formulas.
=INDEX(Email!$B$5:$O$29,MATCH("Open Rate",Email!$B$5:$B$29,0),MATCH(rngMonthBeg,Email!$B$5:$O$5,0))
=INDEX(Email!$B$5:$O$29,MATCH("Opens",Email!$B$5:$B$29,0),MATCH(rngMonthBeg,Email!$B$5:$O$5,0))
I also added in a few named ranges and commented your spreadsheet for better understanding.
rngToday = Today()
rngMonthBeg = EOMONTH(Z5,-2)+1
Hi Matt,
Thank you again for your help. I copied the formula to my real sheet (exact same format, layout, order of worksheets, etc.) and now I get a #name error. Can you tell me what you named? Or better yet (I know it's asking a lot) could you write out the steps you took to create this formula? This is way beyond my Excel knowledge, so I'm not sure what I need to do to replicate the process you followed. I'm also not sure what these mean or what I need to do with them:
rngToday = Today()
rngMonthBeg = EOMONTH(Z5,-2)+1
Sorry for the lack of understanding, I can normally muddle my way through Excel issues and formulas but this one seems to combine a few processes that I haven't learned yet, so it's hard to figure out where I need to start.
Thanks,
CarlyReference cells Z5 and Z6 in the "Email" worksheet in the workbook I attached. These are the named ranges. So you will want to add formulas like this to your workbook. Once they are in cells you can simply name them:
In order to create a named range you will need to go to
Formulas > Name Manager > New
or Option B
go to the cell you want to name then type in the range name you would like into the top left corner of the Excel window: