stingraysting
New Member
- Joined
- Aug 31, 2009
- Messages
- 4
i have a worksheet tied to a TFS query to retrieve work items from that system. The results of which are displayed in $B:$D. In column A I need to have a formula that examines column D and potentially massages the data - something like:
=IF(LEN($D1)=0,"NULL",IF(LEN($D1)=1,"FOO",MID($D1,2,FIND("\",CONCATENATE($D1,"\"),2)-2)))
the problem I run into is that if the query from TFS returns 10 rows initially, then A1:A10 dispaly the correct information. But then if I refresh the query and the result set is 15, then excel assumes an insert in B11:D15 and the formula for A11 starts referencing $D16, not $D11.
So someone suggested I used a formula or macro using offset and I ran with this:
=IF(LEN(OFFSET($A1,0,3))=0,"NULL",IF(LEN(OFFSET($A1,0,3))=1,"FOO",MID(OFFSET($A1,0,3),2,FIND("\",CONCATENATE(OFFSET($A1,0,3),"\"),2)-2)))
that works, except this worksheet tends to have many more than 10 rows AND I have to use this formula on several other worksheets in the book, so.... the workbook is tied up with 'Calculating 2 processors....' and essentially unusable.
using excel 2007, XP sp2, TFS 2008.
thanks in advance
=IF(LEN($D1)=0,"NULL",IF(LEN($D1)=1,"FOO",MID($D1,2,FIND("\",CONCATENATE($D1,"\"),2)-2)))
the problem I run into is that if the query from TFS returns 10 rows initially, then A1:A10 dispaly the correct information. But then if I refresh the query and the result set is 15, then excel assumes an insert in B11:D15 and the formula for A11 starts referencing $D16, not $D11.
So someone suggested I used a formula or macro using offset and I ran with this:
=IF(LEN(OFFSET($A1,0,3))=0,"NULL",IF(LEN(OFFSET($A1,0,3))=1,"FOO",MID(OFFSET($A1,0,3),2,FIND("\",CONCATENATE(OFFSET($A1,0,3),"\"),2)-2)))
that works, except this worksheet tends to have many more than 10 rows AND I have to use this formula on several other worksheets in the book, so.... the workbook is tied up with 'Calculating 2 processors....' and essentially unusable.
using excel 2007, XP sp2, TFS 2008.
thanks in advance