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

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

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