software

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 Log3:00-5:00
CON 1
Time
50.046
4.750.673
4.50.107
4.250.082
40.421
3.750.816
3.50.199
3.250.572
30.071
Con 2
Time
50.021
4.750.388
4.50.962
4.250.82
40.446
3.750.629
3.50.665
3.250.384
30.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 lt;ggt;)

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

Assume source data in sheet: X
cols A amp; B

It's assumed that a typical data quot;sectionquot;
comprises a 2 col range, for example:

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

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

In another sheet: Y (say),

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

Put in A2:
=IF($A$1=quot;quot;,quot;quot;,OFFSET(INDIRECT(quot;'X'!Aquot;amp;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
savefile.com/projects/236895
xdemechanik
---
quot;Jeze77quot; gt; wrote in message
...
gt; The way i've been doing it is to rename the ranges so the formula can
gt; calculate. Is their an easier way? I've been playing with match and
index
gt; arguments and i'm stuck. I don't know enough about VB to create, only
edit.
gt;
gt; My Data: (today it is from 3-5, tomorrow it may be from 1-5)
gt;
gt; Phone Log 3:00-5:00
gt; CON 1
gt; Time
gt; 5 0.046
gt; 4.75 0.673
gt; 4.5 0.107
gt; 4.25 0.082
gt; 4 0.421
gt; 3.75 0.816
gt; 3.5 0.199
gt; 3.25 0.572
gt; 3 0.071
gt; Con 2
gt; Time
gt; 5 0.021
gt; 4.75 0.388
gt; 4.5 0.962
gt; 4.25 0.82
gt; 4 0.446
gt; 3.75 0.629
gt; 3.5 0.665
gt; 3.25 0.384
gt; 3 0.55
gt;
gt; My formula:
gt; =IF($A$1=Sheet1!$A$2,HLOOKUP($A$1,Con_1,3,FALSE))
gt;
gt; I need it to display the CON1, the time mark and the ASR (column B, no
gt; heading)
thanks Max! That worked!!!!!!!!!!
thank you thank you thank you!!!!

quot;Maxquot; wrote:

gt; Here's one deep guess as
gt; to what you have and what you're after
gt; (and an approach to arrive there lt;ggt;)
gt;
gt; See the sample construct at:
gt; www.savefile.com/files/5097154
gt; Dynamically lookup data sections in another sheet.xls
gt;
gt; Assume source data in sheet: X
gt; cols A amp; B
gt;
gt; It's assumed that a typical data quot;sectionquot;
gt; comprises a 2 col range, for example:
gt;
gt; gt; Con 1 lt;lt; this is assumed in one cell
gt; gt; Time
gt; gt; 5 0.046
gt; gt; 4.75 0.673
gt; gt; 4.5 0.107
gt; gt; 4.25 0.082
gt; gt; 4 0.421
gt; gt; 3.75 0.816
gt; gt; 3.5 0.199
gt; gt; 3.25 0.572
gt; gt; 3 0.071
gt;
gt; and there's no label/heading in col 2, to the right of quot;Timequot;,
gt; and the numeric data is a fixed 9 rows (below quot;Timequot;)
gt; (as hinted in your post)
gt;
gt; In another sheet: Y (say),
gt;
gt; In A1 is a data validation list* to select: Con 1, Con2 ...
gt; (*via Data gt; Validation, Allow: List, Source: Con1, Con2, etc)
gt;
gt; Put in A2:
gt; =IF($A$1=quot;quot;,quot;quot;,OFFSET(INDIRECT(quot;'X'!Aquot;amp;MATCH($A$1, X!$A:$A,0)),ROW(A1),COLUMN
gt; (A1)-1))
gt;
gt; Copy A2 to B2, fill down to B11 (i.e. fill down 9 rows)
gt; [then go back and clear the formula in B2 if desired,
gt; since there's no label in the 2nd col in X]
gt;
gt; A2:B11 will return the required data section from X
gt; for the selection made in A1
gt;
gt; Adapt to suit ..
gt; --
gt; Max
gt; Singapore
gt; savefile.com/projects/236895
gt; xdemechanik
gt; ---
gt; quot;Jeze77quot; gt; wrote in message
gt; ...
gt; gt; The way i've been doing it is to rename the ranges so the formula can
gt; gt; calculate. Is their an easier way? I've been playing with match and
gt; index
gt; gt; arguments and i'm stuck. I don't know enough about VB to create, only
gt; edit.
gt; gt;
gt; gt; My Data: (today it is from 3-5, tomorrow it may be from 1-5)
gt; gt;
gt; gt; Phone Log 3:00-5:00
gt; gt; CON 1
gt; gt; Time
gt; gt; 5 0.046
gt; gt; 4.75 0.673
gt; gt; 4.5 0.107
gt; gt; 4.25 0.082
gt; gt; 4 0.421
gt; gt; 3.75 0.816
gt; gt; 3.5 0.199
gt; gt; 3.25 0.572
gt; gt; 3 0.071
gt; gt; Con 2
gt; gt; Time
gt; gt; 5 0.021
gt; gt; 4.75 0.388
gt; gt; 4.5 0.962
gt; gt; 4.25 0.82
gt; gt; 4 0.446
gt; gt; 3.75 0.629
gt; gt; 3.5 0.665
gt; gt; 3.25 0.384
gt; gt; 3 0.55
gt; gt;
gt; gt; My formula:
gt; gt; =IF($A$1=Sheet1!$A$2,HLOOKUP($A$1,Con_1,3,FALSE))
gt; gt;
gt; gt; I need it to display the CON1, the time mark and the ASR (column B, no
gt; gt; heading)
gt;
gt;
gt;

You're welcome !
Delighted to hear it worked for you
Thanks for the feedback ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Jeze77quot; gt; wrote in message
...
gt; thanks Max! That worked!!!!!!!!!!
gt; thank you thank you thank you!!!!