formula cell reference updates when query returns more rows

[reillyBoston]

I have a TFS bound query in a worksheet that returns a group of work items - this query is refreshed daily, and may return more or less rows than the previous day. I have a formula in column A to extract important information from the query results that start in column B- let's say it is

=CONCATENATE($B1,"SomeText")

I copy this formula down column A so it is absolute to column B, but relative to the row the formula lives on. formula works fine.

But when the query refreshes and if it returns more rows, it skews the relative row number by the number of rows it inserted.

let's assume query originally returned 10 rows. on the tenth row my formula is =CONCATENATE($B10,"SomeText"). if the query now returns 15 rows, the formula for row 11 should be =CONCATENATE($B11..... but instead it is =CONCATENATE($B16.... because it compensated for the insert of 5 more rows.

thoughts?

[NBVC]

Does it do the same if you change formula to?:

=$B10&"Some Text"

[reillyBoston]

yes, same thing. and i used CONCATENATE as an example - my formula is a bit more wordy

I did find that I can do =CONCATENATE(INDIRECT("$B"&ROW()),"SomeText")

that works BUT it sucks the life out of the spreadsheet to do this calculation

[NBVC]

I remember something similar happening to me in my queries.. but I think it was because I had the sheetname in the formula too..

as in =Concatenate(Sheet1!$B1&"SomeText")

I have so many queries though, that I don't remember where that occurred..