Hi Everyone!
This is a challenging question, so bear with me as I go through a lot of information (I figure the more the better, right?). :)
I have a spreadsheet with multiple tabs, one called "Master", which is where the info is that I need to reference. I created an Aging tab - I look back at the Master tab to 4 columns...Status, Start Date, Contracting, and Closed. On the Aging tab, I have 8 fields where I am using a COUNTIFS formula for the following:
Pre-Contract Phase: 0-30 days (count if status is "in progress", start date column is not blank, contracting column is blank, close date is blank, and start date is within the last 30 days)
=COUNTIFS(sitestatus,"4 In Progress",contracting,"",closed,"",startdate,">="&TODAY()-30)
Pre-Contract Phase: 31-60 days (count if status is "in progress", contracting column is blank, close date is blank, and start date is 31-60 days old)
Pre-Contract Phase: 61 to 90 days (count if status is "in progress", contracting column is blank, close date is blank, and start date is 61-90 days old)
Pre-Contract Phase: 90+ days (count if status is "in progress", contracting column is blank, close date is blank, and start date is 90 days old or more)
Post-Contracting Phase: 0-30 days (count if status is "in contracting", close date is blank, and contracting date is in the past 30 days)
=COUNTIFS(sitestatus,"Contracting",contracting,"<>",closed,"",contracting,">="&TODAY()-30)
Post-Contracting Phase:31-60 days (count if status is "in contracting", close date is blank, and contracting date is 31-60 days old)
Post-Contracting Phase:61-90 days (count if status is "in contracting", close date is blank, and contracting date is 61-90 days old)
Post-Contracting Phase: 90+ days (count if status is "in contracting", close date is blank, and contracting date is 90 days old or more)
The formulas work - I have named ranges for sitestatus, startdate, contracting, and closed. I showed you only one of the formulas above (again, the others work, so that's not the problem). Here's an example of one of the named ranges:
site status
='[our sharepoint site]/teams/[Team Name]/Shared Documents/G. Site Selection and Engagement/2. Data/[Eligible Site List_Refresh_MASTER_11-17.xlsx]Master'!$D$2:$D$5000
Here's the issue...when I copy this aging tab to another spreadsheet, the named ranges are there, pointing to the original spreadsheet, but I'm receiving a #VALUE error for all 8 fields.
There are other pivot tables in different tabs that are copied to a new workbook and they all seem to work - they are pointing to the original spreadsheet on SharePoint, and you can refresh them. It seems that the 8 fields are having errors refreshing the data from the original file.
I get this error message when I hit "enable workbook" when I open it:
We can't update some of the links in your workbook right now.
You can continue without updating their values or edit the links you think are wrong.
I click "update link", and the status shows "OK".
Can anyone help me figure out why I am getting the #VALUE message? What am I doing wrong?
Thanks in advance!
Sunnie
