formula corrupted by query resultset changing

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
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

New Member
Joined
Aug 31, 2009
Messages
4
i could do that - erase and reset that column - haven't explored on how to write that code. suggestions?
 

stingraysting

New Member
Joined
Aug 31, 2009
Messages
4
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
I'm not a macro expert, but this would do a basic version - there are probably better ways
Code:
Range("A1").Copy
Range("A1:A100").Select
ActiveSheet.Paste
Replace the 100 with a suitably large number.
 

Forum statistics

Threads
1,081,691
Messages
5,360,644
Members
400,591
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top