#REF errors when refreshing Team Foundation (pivot-style) table and can't use INDIRECT()

[Maelstrm]

Hi All,

I'm currently running a report each day. It pulls data from Team Foundation Server (TFS) in to a table (in Excel 2010) which is referenced by a number of queries which perform simple calculations. Those calculations are displayed next to the TFS data and are used to sort it.

Example of report display with different calculation options discussed below. TFS sourced data to be shown in blue section, calculations to be provided in grey section and used to sort both sections.

Report.PNG

I'm currently using calculation option 2 from the image.

The issue I'm seeing is that when the TFS data is updated by using the Refresh button in the Team tab any item that no longer exists in the query shows #REF errors and new items do not appear unless I create new lines to query them. Each morning I therefore have to autopopulate all the calculation cells in order to essentially refresh them.

I've tried working around the issue by using the INDIRECT() function but am having some issues - it seems the only way to autopopulate INDIRECT() is by calling it using cell based formulas eg =INDIRECT(CELL("address",A1)) or =INDIRECT("R"&ROW()&"C"&COLUMN(),FALSE). These work to display the data but cannot be used to sort it as far as I can see.

I'd appreciate any thoughts or assistance as I'm going a little bonkers trying to sort this out

[dilipandey]

Hi Maelstrm,

Welcome to the forum.

I would like to see the Team tab as well.... would it be possible for you to upload a sample workbook. thanks.

Regards,
DILIPandey
<click on below * if this helps>

[Maelstrm]

Hi DILIPandey,

Thanks for your reply.

In short I don't think I can - I cannot provide a populated excel sheet as the actual data is corporate and I do not believe it is possible to easily create dummy data in TFS. From what I understand you would not be able to see it without the add-in and it would not function unless it could connect to TFS anyway.

Here is a screenshot of a Team tab in case it is of use (not mine):
http://cs.txstate.edu/~br02/cs1308/l...eamTab2010.gif

[dilipandey]

Hi Maelstrm,

Okay...
the Team tab you posted is blank.. and also Report.PNG do not have any reference to Team Tab from where I can understand where is the error.
#Ref errors occurs when reference is moved or do not exists anymore and for Indirect, you can share a scenario by a dummy sample. Hope this helps.

Regards,
DILIPandey
<click on below * if this helps>

[Maelstrm]

Hi DILIPandey,

Thanks for your input so far. Here is the best I can do:

Screenshot of Team tab from the report:
Report2.PNG

I've also created a dummy report which demonstrates that you cannot sort by cells which depend upon the INDIRECT() function
Example1.xlsx

You are correct in that #REF errors are created when rows are deleted (no longer present in the query due to an update). Alas I cannot create a more realist example as it would not be possible to reproduce the error without being able to connect to the TFS server to refresh the data in order to have rows removed and #REF entries created.