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)

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" wrote in message
...
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)

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" wrote in message
...
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)

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" wrote in message
...
thanks Max! That worked!!!!!!!!!!
thank you thank you thank you!!!!