+ Reply to Thread
Results 1 to 10 of 10

Web Query, Stop Formulas Shifting

  1. #1
    Registered User
    Join Date
    02-23-2007
    Posts
    19

    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

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    have you tried absolute referencing

    = #Sheet2!$A$69

  3. #3
    Registered User
    Join Date
    02-23-2007
    Posts
    19
    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.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    try =IF(Sheet2!$A$105>Sheet2!$B$5,INDIRECT("Sheet2!F"&ROW()-1),"")
    where the row ()-1 is to give the reference you want
    so if your formula is in row in row 20 on sheet1 you would add 85 to it to reference 105
    row()+85
    edit obviously if you are in row 105 on sheet 1 and want to ref 105 on sheet2 don't add anything just leave it as row()
    Last edited by martindwilson; 09-21-2008 at 11:28 AM.

  5. #5
    Registered User
    Join Date
    02-23-2007
    Posts
    19
    Ok thanks for your help. I'll go give that a try now and post my findings Thanks again

  6. #6
    Registered User
    Join Date
    02-23-2007
    Posts
    19
    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

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    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

  8. #8
    Registered User
    Join Date
    02-23-2007
    Posts
    19
    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

  9. #9
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    =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

  10. #10
    Registered User
    Join Date
    02-23-2007
    Posts
    19
    Thanks you so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. MS Query Update changes formulas
    By profector in forum Excel General
    Replies: 2
    Last Post: 12-06-2007, 04:06 PM
  2. formulas calling to wrong cells after query
    By minkus in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-07-2007, 08:11 PM
  3. Excel query and parameters
    By urbanmojo in forum Excel General
    Replies: 1
    Last Post: 07-23-2007, 12:17 AM
  4. Query & formula's
    By kdarr in forum Excel General
    Replies: 5
    Last Post: 05-07-2007, 05:58 PM
  5. Formulas change when column/row is deleted... how can i stop that?
    By RNiner in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-11-2006, 06:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1