Web Query, Stop Formulas Shifting

Hi. I had a sheet that had a web query imported into it which took up columns A to F. The web query varies in length upon every refresh. In columns G onwards i have lots of formulas that read the information from the query to produce results.

When i imported the web query, on some refreshes it has the ability to push my formulas down in certain columns. I'm assuming this is because the length has changed of the web query maybe? Although it can do it on the opening import of the query which i find strange.

I was told the best way around this would be to put my web query on another sheet, however this doesnt appear to have fixed my problem.

Lets say for example #Sheet1!A1 looks at #Sheet2!A1, #Sheet1!A2 looks at #Sheet2!A2 and so on. This will work fine. When the web query is imported into Sheet 2, its data goes down to cell A72. Upon another refresh though, the length will changed and now the data might go down to cell A81. However, #Sheet1!A72 will have changed its formula to look at #Sheet2!A81 and my formulas on Sheet 1 will now read as follows:

#Sheet1!A69 = #Sheet2!A69
#Sheet1!A70 = #Sheet2!A70
#Sheet1!A71 = #Sheet2!A71
#Sheet1!A72 = #Sheet2!A81 <----- Problem!

Basically the problem with this is Sheet 1 will not be including Sheet 2 A72:A80 which i also need.

Any ideas how to fix this?

Many thanks

have you tried absolute referencing

= #Sheet2!$A$69

Ive updated my sheet completely to use absolute referencing, however its still doing the same. Just for a bit more information, my cells should look like this:

=IF(Sheet2!$A$105>Sheet2!$B$5,Sheet2!$F$105,"")
=IF(Sheet2!$A$106>Sheet2!$B$5,Sheet2!$F$106,"")
=IF(Sheet2!$A$107>Sheet2!$B$5,Sheet2!$F$107,"")

etc

but when the web query is refreshed, the length of the query extends down to row 112, and the above set of formulas will now read...

=IF(Sheet2!$A$105>Sheet2!$B$5,Sheet2!$F$105,"")
=IF(Sheet2!$A$106>Sheet2!$B$5,Sheet2!$F$106,"")
=IF(Sheet2!$A$112>Sheet2!$B$5,Sheet2!$F$112,"")

Interestingly, the middle part of the forumla "Sheet2!$B$5" remains consistant.

Ok thanks for your help. I'll go give that a try now and post my findings Thanks again

Ok i have tested it, still now luck

After a refresh in the web query, it has added another row of information. What should look like this:

=IF(Sheet2!$A$31>Sheet2!$B$5,INDIRECT("Sheet2!E"&ROW()),"")
=IF(Sheet2!$A$32>Sheet2!$B$5,INDIRECT("Sheet2!E"&ROW()),"")
=IF(Sheet2!$A$33>Sheet2!$B$5,INDIRECT("Sheet2!E"&ROW()),"")

now looks like this:

=IF(Sheet2!$A$31>Sheet2!$B$5,INDIRECT("Sheet2!E"&ROW()),"")
=IF(Sheet2!$A$32>Sheet2!$B$5,INDIRECT("Sheet2!E"&ROW()),"")
=IF(Sheet2!$A$34>Sheet2!$B$5,INDIRECT("Sheet2!E"&ROW()),"")

You know of anything else i could try? Thanks

well do it to all of them!

=IF(INDIRECT("Sheet2!A"&ROW()+1)>INDIRECT("Sheet2!B"&ROW()+1),INDIRECT("Sheet2!E"&ROW()+1),"")

b5 doesnt change probably because only rows below it change
but just in case you can do that one as well

Perfect! Many many thanks!! I've adjusted my other formulas to follow suit and that seems to be doing the trick, the only formula i am strugglin' to convert is

=IF(Q13<0,"",IF(ISERROR(Q13*(Sheet2!$A$13-1)),"",Q13*(Sheet2!$A$13-1)))

I know its a little cheeky but any help with this last formula would be very much appreciated. Thanks for all your help Martin

=IF(Q13<=0,"",IF(ISERROR(Q13*(INDIRECT("Sheet2!A"&ROW()+20)-1)),"",Q13*(INDIRECT("Sheet2!A"&ROW()+20)-1)))
i changed the Q13<0 to Q13<=0 else youd always get back 0 and not blank
the row adjustment is 20 just to distinguish it from the -1 which you want at the end of formula

Thanks you so much!