- last edited on
I'm trying to recreate a formula from an excel template that a colleague uses (doesn't make sense to copy the entire template as I only need a few functions from it). I'm not copying and pasting from the template (as it then wants to link to that template), instead I'm writing the formulas from scratch based on the names of my pages within my spreadsheet.
I'm trying to get the first page to look at the second page - I would like to find the earliest date (and the latest date - i.e. start and end dates) under each project heading (i.e. media, logistics, etc.). I can get the first one, but cannot get the rest to fill in (when I drag down the formula or rewrite it). It also doesn't work if I resort the second page.
Currently my formula to find the earliest start date is this: =MIN(IF((Tracker[Project Name]=[@[Project List]])*(Tracker[Start]>0),Tracker[Start]))
I've even copied over the coding from the template that worked (and made adjustments to it as needed).
This feels like it should be a simple thing, but it's been anything but.
Thanks for the help,
07-16-2018 03:29 PM
You use formula with structured references for Excel tables, it depends only on Table names and their column names. You don't need to drag cell with formula down, it shall be autofilled in table column.
If you work with tables do you have one table with Project List column and another table is Tracker?
If you attach small sample file it'll be easier to say what's wrong.
07-16-2018 04:02 PM
Yes, those naming conventions are correct. Unfortunately I am not permitted to upload the document, however I have attached a couple of screen shots so you can hopefully see what I'm talking about. I'm trying to get the start/end dates for each project (each of which has multiple tasks listed under them, with different dates on the project tracker page - table name is 'tracker') to automatically go into the project overview page (table name is 'overview'). For some items it appears to be working, for others i still only get 0-Jan-00, even though the formulas are the same.
07-16-2018 04:22 PM
Thank you for the screenshot, it's more clear now. In general your formula shall work, but only if you use it as array one (use Ctrl+Shift+Enter to enter the formula). Regular (non-array) formula equivalent could be
=AGGREGATE(15,6,1/(Tracker[Project Name]=[@[Project List]])/(Tracker[Start]>0)*Tracker[Start],1)
Both are in attached file.
07-17-2018 01:18 PM - edited 07-17-2018 01:19 PM
That's fantastic, thank you so much!
Would the end formula be the same (obviously using End instead of Start) or would there be other changes? Trying to find the latest End date.
07-17-2018 04:34 PM
=AGGREGATE(14,6,1/(Tracker[Project Name]=[@[Project List]])/(Tracker[End]>0)*Tracker[End],1)
File is attached