Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort of Lookup... (Win XP pro SP2, XL2003 UK)

    Hi,

    Need a sort of a lookup. Have done it before but forgotten how... - so please refresh my mind / ;o)

    Got two sheets:
    1. Data entry (DE)
    2. Data out (DO)

    DE is a traditional accounting workbook without formulas - pure data entry.
    DE has data grouped in columns. Column C=2006, D=2005, E=2004 etc...
    DE has items grouped in rows R7 = Item1, R8 = Item2

    On DO I have a single cell at the top say cell B6, it contains the accounting year to look at, eg. 2006 (defined name is: 'YR_lookup')
    Starting at row 10, I then have a number of formulas aclculating all sorts of ratios.

    I would like the formulas to work in such a way that they lookup values in the DE sheet based on the YR_lookup.
    This means that if I want to look at data for 2004 instead of 2006, all I need to do is to change the value in YR_lookup.

    How do I achieve this?
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort of Lookup... (Win XP pro SP2, XL2003 UK)

    Are the years listed in a row at the top of the DE sheet, e.g. in row 1?

  3. #3
    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: Sort of Lookup... (Win XP pro SP2, XL2003 UK)

    Does something like the attached help you apply it to your spreadsheet?


    Steve
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort of Lookup... (Win XP pro SP2, XL2003 UK)

    Years are listed as a header row starting from row 9
    Data values start from row 10 downwards.
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort of Lookup... (Win XP pro SP2, XL2003 UK)

    You should be able to modify Steve (sdckapr)'s suggestion to suit your needs.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort of Lookup... (Win XP pro SP2, XL2003 UK)

    Hi Steve,

    This looks as if it is very close to what I need.
    One question though for clarification. Used 'trace dependents' and saw that DO!A10 is a precedent to C10.
    However looking at the formula in A10, I must admit that I'm not 100% certain what it really does? (except that it works!)

    Please enlighten me <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort of Lookup... (Win XP pro SP2, XL2003 UK)

    The formula in DO!A10 compares the value of B10 to the values in column A of the DE sheet, and returns the index (i.e. row number) of the cell where the value is found, minus 1. This value is then used in the formula in C10 as a row offset.

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort of Lookup... (Win XP pro SP2, XL2003 UK)

    Beat me to it again, you did

    sign. Yoda <img src=/S/bif.gif border=0 alt=bif width=70 height=28> <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  9. #9
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort of Lookup... (Win XP pro SP2, XL2003 UK)

    Understood, but...

    Formula in C then...
    Thought it would be a relative formula that would refer to a range, but as the formula is copied down it extends its length by adding a single row all the time.
    This will only work if I don't have blank rows in between. Otherwise it will be error prone as well and will require manual checking too (which is what I was hoping to avoid)

    Are there other ways of doing the last lookup?
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort of Lookup... (Win XP pro SP2, XL2003 UK)

    Sorry, I don't understand.

  11. #11
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort of Lookup... (Win XP pro SP2, XL2003 UK)

    Not sure I fully understand either. I'll do some experimenting and get back to you if the problem turns out to persist.

    Thanks both of you for your quick replies so far.

    ...Hans - got it figured out.

    Hadn't noted in the first place that the $A10 followed a ' ; ' and therefore wasn't an end range, but the row offset value... <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15> <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

Posting Permissions

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