Closed Thread
Results 1 to 4 of 4

How do you perform lookups when the info is always moving?

  1. #1
    Jeze77
    Guest

    How do you perform lookups when the info is always moving?

    The way i've been doing it is to rename the ranges so the formula can
    calculate. Is their an easier way? I've been playing with match and index
    arguments and i'm stuck. I don't know enough about VB to create, only edit.

    My Data: (today it is from 3-5, tomorrow it may be from 1-5)

    Phone Log 3:00-5:00
    CON 1
    Time
    5 0.046
    4.75 0.673
    4.5 0.107
    4.25 0.082
    4 0.421
    3.75 0.816
    3.5 0.199
    3.25 0.572
    3 0.071
    Con 2
    Time
    5 0.021
    4.75 0.388
    4.5 0.962
    4.25 0.82
    4 0.446
    3.75 0.629
    3.5 0.665
    3.25 0.384
    3 0.55

    My formula:
    =IF($A$1=Sheet1!$A$2,HLOOKUP($A$1,Con_1,3,FALSE))

    I need it to display the CON1, the time mark and the ASR (column B, no
    heading)

  2. #2
    Max
    Guest

    Re: How do you perform lookups when the info is always moving?

    Here's one deep guess as
    to what you have and what you're after
    (and an approach to arrive there <g>)

    See the sample construct at:
    http://www.savefile.com/files/5097154
    Dynamically lookup data sections in another sheet.xls

    Assume source data in sheet: X
    cols A & B

    It's assumed that a typical data "section"
    comprises a 2 col range, for example:

    > Con 1 << this is assumed in one cell
    > Time
    > 5 0.046
    > 4.75 0.673
    > 4.5 0.107
    > 4.25 0.082
    > 4 0.421
    > 3.75 0.816
    > 3.5 0.199
    > 3.25 0.572
    > 3 0.071


    and there's no label/heading in col 2, to the right of "Time",
    and the numeric data is a fixed 9 rows (below "Time")
    (as hinted in your post)

    In another sheet: Y (say),

    In A1 is a data validation list* to select: Con 1, Con2 ...
    (*via Data > Validation, Allow: List, Source: Con1, Con2, etc)

    Put in A2:
    =IF($A$1="","",OFFSET(INDIRECT("'X'!A"&MATCH($A$1,X!$A:$A,0)),ROW(A1),COLUMN
    (A1)-1))

    Copy A2 to B2, fill down to B11 (i.e. fill down 9 rows)
    [then go back and clear the formula in B2 if desired,
    since there's no label in the 2nd col in X]

    A2:B11 will return the required data section from X
    for the selection made in A1

    Adapt to suit ..
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Jeze77" <Jeze77@discussions.microsoft.com> wrote in message
    news:85218455-6344-457C-A0C5-83FB91A013DE@microsoft.com...
    > The way i've been doing it is to rename the ranges so the formula can
    > calculate. Is their an easier way? I've been playing with match and

    index
    > arguments and i'm stuck. I don't know enough about VB to create, only

    edit.
    >
    > My Data: (today it is from 3-5, tomorrow it may be from 1-5)
    >
    > Phone Log 3:00-5:00
    > CON 1
    > Time
    > 5 0.046
    > 4.75 0.673
    > 4.5 0.107
    > 4.25 0.082
    > 4 0.421
    > 3.75 0.816
    > 3.5 0.199
    > 3.25 0.572
    > 3 0.071
    > Con 2
    > Time
    > 5 0.021
    > 4.75 0.388
    > 4.5 0.962
    > 4.25 0.82
    > 4 0.446
    > 3.75 0.629
    > 3.5 0.665
    > 3.25 0.384
    > 3 0.55
    >
    > My formula:
    > =IF($A$1=Sheet1!$A$2,HLOOKUP($A$1,Con_1,3,FALSE))
    >
    > I need it to display the CON1, the time mark and the ASR (column B, no
    > heading)




  3. #3
    Jeze77
    Guest

    Re: How do you perform lookups when the info is always moving?

    thanks Max! That worked!!!!!!!!!!
    thank you thank you thank you!!!!

    "Max" wrote:

    > Here's one deep guess as
    > to what you have and what you're after
    > (and an approach to arrive there <g>)
    >
    > See the sample construct at:
    > http://www.savefile.com/files/5097154
    > Dynamically lookup data sections in another sheet.xls
    >
    > Assume source data in sheet: X
    > cols A & B
    >
    > It's assumed that a typical data "section"
    > comprises a 2 col range, for example:
    >
    > > Con 1 << this is assumed in one cell
    > > Time
    > > 5 0.046
    > > 4.75 0.673
    > > 4.5 0.107
    > > 4.25 0.082
    > > 4 0.421
    > > 3.75 0.816
    > > 3.5 0.199
    > > 3.25 0.572
    > > 3 0.071

    >
    > and there's no label/heading in col 2, to the right of "Time",
    > and the numeric data is a fixed 9 rows (below "Time")
    > (as hinted in your post)
    >
    > In another sheet: Y (say),
    >
    > In A1 is a data validation list* to select: Con 1, Con2 ...
    > (*via Data > Validation, Allow: List, Source: Con1, Con2, etc)
    >
    > Put in A2:
    > =IF($A$1="","",OFFSET(INDIRECT("'X'!A"&MATCH($A$1,X!$A:$A,0)),ROW(A1),COLUMN
    > (A1)-1))
    >
    > Copy A2 to B2, fill down to B11 (i.e. fill down 9 rows)
    > [then go back and clear the formula in B2 if desired,
    > since there's no label in the 2nd col in X]
    >
    > A2:B11 will return the required data section from X
    > for the selection made in A1
    >
    > Adapt to suit ..
    > --
    > Max
    > Singapore
    > http://savefile.com/projects/236895
    > xdemechanik
    > ---
    > "Jeze77" <Jeze77@discussions.microsoft.com> wrote in message
    > news:85218455-6344-457C-A0C5-83FB91A013DE@microsoft.com...
    > > The way i've been doing it is to rename the ranges so the formula can
    > > calculate. Is their an easier way? I've been playing with match and

    > index
    > > arguments and i'm stuck. I don't know enough about VB to create, only

    > edit.
    > >
    > > My Data: (today it is from 3-5, tomorrow it may be from 1-5)
    > >
    > > Phone Log 3:00-5:00
    > > CON 1
    > > Time
    > > 5 0.046
    > > 4.75 0.673
    > > 4.5 0.107
    > > 4.25 0.082
    > > 4 0.421
    > > 3.75 0.816
    > > 3.5 0.199
    > > 3.25 0.572
    > > 3 0.071
    > > Con 2
    > > Time
    > > 5 0.021
    > > 4.75 0.388
    > > 4.5 0.962
    > > 4.25 0.82
    > > 4 0.446
    > > 3.75 0.629
    > > 3.5 0.665
    > > 3.25 0.384
    > > 3 0.55
    > >
    > > My formula:
    > > =IF($A$1=Sheet1!$A$2,HLOOKUP($A$1,Con_1,3,FALSE))
    > >
    > > I need it to display the CON1, the time mark and the ASR (column B, no
    > > heading)

    >
    >
    >


  4. #4
    Max
    Guest

    Re: How do you perform lookups when the info is always moving?

    You're welcome !
    Delighted to hear it worked for you
    Thanks for the feedback ..
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Jeze77" <Jeze77@discussions.microsoft.com> wrote in message
    news:AF6359CE-AE03-4A6E-9F98-DD80D5512813@microsoft.com...
    > thanks Max! That worked!!!!!!!!!!
    > thank you thank you thank you!!!!




Closed Thread

Thread Information

Users Browsing this Thread

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

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