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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
Click to expand...

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 ..

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

Max said:
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
Click to expand...

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 ..

---
Jeze77 said:
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)
Click to expand...
Click to expand...
 
Back
Top