#### stingraysting

##### New Member

- Joined
- Aug 31, 2009

- Messages
- 4

=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