Copying a formula down doesn't yield results

Copper Contributor

Hi,

 

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,

Erin

5 Replies

Hi Erin,

 

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.

Hi Sergei,

 

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.

Hi Erin,

 

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.

 

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.

 

Erin, that's

=AGGREGATE(14,6,1/(Tracker[Project Name]=[@[Project List]])/(Tracker[End]>0)*Tracker[End],1)

, please check https://support.office.com/en-us/article/aggregate-function-43b9278e-6aa7-4f17-92b6-e19993fa26df

 

File is attached