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)

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" <[email protected]> wrote in message
news:[email protected]...
> 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)

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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)
>
>
>

You're welcome !
Delighted to hear it worked for you
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeze77" <[email protected]> wrote in message
news:[email protected]...
> thanks Max! That worked!!!!!!!!!!
> thank you thank you thank you!!!!