Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Offset Formula (2000)

    How does the offset formula work when trying to retieve data from one sheet to another depending on a cell in the current worksheet? Here is an example of what is in a cell from a worbook I've gotten a hold of: =IF(OFFSET('Voltage Level Data'!$B$6,$B$8-1,1)>0,OFFSET('Conductor Data'!$B$6,$B13-1,9)). This formula ultimately returned a number that was in the Conductor Data worksheet depending on what was in cell B8 and B13 of the current worksheet. I cut and pasted this formula into my own workbook that has a simular set-up. Yet I keep getting a #VALUE in the cell that I want the number to be returned in. Am I leaving something out? How exactly is this formula returning data? One more thing - I have noticed that in the insert - name - define menu in that particular workbook , there is a name called data that has 'Conductor Data'!#REF! in the refers to boxsource, however, I don't know how this is used either.

    Please Help - anyone!!!! Thanks in advance and I look forward to your responce.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Offset Formula (2000)

    The formula you list:
    =IF(OFFSET('Voltage Level Data'!$B$6,$B$8-1,1)>0,OFFSET('Conductor Data'!$B$6,$B13-1,9))

    Will look in the sheet "Voltage Level Data". It will start in Cell B6. It will look at the NUMBER that is in Cell B8 of the active sheet, and subtract one from it. The formula will look this many ROWS down from Cell B6 of "Voltage level data" and then go one column to the right to look at that value. If that value is >0

    It will look in the 'Conductor Data' sheet at cell B6. It will go to B13 (of the active sheet grab the number, subtract 1) and go that many rows DOWN from B6 of Conductor Data sheet and 9 columns to the right and put THAT VALUE in the cell.

    If the value refered to on the Voltage Level Data sheet is not >0 then it will display FALSE.

    If B8 or B13 of the active sheet is TEXT (not a number) it will yield #VALUE, as a result, since the offset can not calculate a "text value - 1"

    Steve

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset Formula (2000)

    Gotcha! I see how it works now, but theres one more thing. The workbook I got that formula off of uses B13 as a drop-down list of stringed characters. Is excel somehow looking at this character as an equivalent number to return the data for the given character? What else might be going on?

    Thanks

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Offset Formula (2000)

    If the dropdown is a combobox from FORMS that links to B13, it gives the row number of the selection (1st item = 1, 2nd item = 2, etc)

    If the dropdown is a combobox from controls toolbox linked to a cell, if bound column = 0, then it is the row number that is ZERO based (1st item = 0, 2nd item is 1, etc). if the bound column is 1 (default) the linked cell puts in the TEXT and that could be the source of the problem with the #value.

    Steve

  5. #5
    Star Lounger
    Join Date
    Mar 2003
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset Formula (2000)

    Good Deal! Thanks.

Posting Permissions

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