formula corrupted by query resultset changing

[stingraysting]

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

 

[Gerald Higgins]

Hi, welcome to the board.

Is it an option for you to stick with your first formula, but just have it copied from A1 down to whatever range it needs to be copied, either manually or automatically, each time you get an update ?

 

[stingraysting]

i could do that - erase and reset that column - haven't explored on how to write that code. suggestions?

 

[stingraysting]

p.s. my search for 'offset' or other solution is to get around the manual refresh of that column, which I have been doing - but am looking to have this report refreshed nightly by a batch file

 

[Gerald Higgins]

I'm not a macro expert, but this would do a basic version - there are probably better ways

Range("A1").Copy
Range("A1:A100").Select
ActiveSheet.Paste

Replace the 100 with a suitably large number.

 

[stingraysting]

duh. thanks