Faizal,

If by "F1" you mean the value from cell F1 of sheet1 entered into cell A1,
then you could use this in cell A2:

=HLOOKUP(A1,Sheet1!1:14,6,FALSE)

and this in cell A3:
=HLOOKUP(A1,Sheet1!1:14,14,FALSE)

HTH,
Bernie
MS Excel MVP


"OTS" wrote in message
oups.com...
ok i think i should explain my problem more clearly.

in sheet 1, i have a bunch of columns of data; the only ones i'm
interested in are 3 columns: times, dates & values.

in sheet 2, i make a reference in cell A1 to a particular time cell
from sheet 1. i want A2 to automatically show the corresponding date
and A3 to automatically show the corresponding value. so A1 is just
going to be a manual reference done by me. i don't know what formulas
to use for A2 & A3, my problem is that i want to offset the reference
in A1 but have no idea how to do it.

so what i meant in my first message is that if i choose A1 to be F1, A2
should show me F6 and A3 should show me F14. then if i choose B1 to be
Z1, B2 should be Z6 and B3 should be Z14. i hope this makes it clearer.
thanks for the help.

regards,
faizal

JLatham (removethis) wrote:
In your Offset formula, just include the name of the other sheet along
with
the cell address on that other sheet. Given your example, on Sheet2, the
cells A1, A2 and A3 would have these formulas in them:
A1 =OFFSET('Sheet1'!F1,0,0)
A2 =OFFSET('Sheet1'!F1,5,0)
A3=OFFSET('Sheet1'!F1,13,0)

although I'm not sure why you don't just reference them directly as:
A1 = 'Sheet1'!F1
A2 = 'Sheet1'!F6
A3 = 'Sheet1'!F14

But you may have your reasons, so I'll offer some other variations of the
formula that you can play with and perhaps one of them will give you an
idea
of how to attack it to solve your particular situation. All of these will
give the same 3 results; getting contents of Sheet1, cells F1, F6 and F14

A1 =OFFSET('Sheet1'!$A$1,0,5)
A2 =OFFSET('Sheet1'!$A$1,5,5)
A3 =OFFSET('Sheet1'!$A$1,13,5)

and maybe this is what you have in mind, gives results that are always a
given number of columns to the right of cell the formula is in (5 columns
in
these cases), and a number of rows down from the cell the formula is in:

A1=OFFSET(Sheet1!$A$1,0,COLUMN()+4) ' same row, 5 columns right
A2 =OFFSET(Sheet1!$A$1,ROW()+3,COLUMN()+4) ' 5 below, 5 right
A3 =OFFSET(Sheet1!$A$1,ROW()+10,COLUMN()+4)' 10below,5 right

The ROW() and COLUMN() entries may look a little strange to you. That is
the way to write them to say "the row/column" of the cell that the
formula is
in.

one more way of writing that last group above:
A1 =OFFSET(Sheet1!$A$1,ROW()-ROW(Sheet1!$A$1),6-COLUMN())
A2 =OFFSET(Sheet1!$A$1,ROW()-ROW(Sheet1!$A$1)+4,6-COLUMN())
A3 =OFFSET(Sheet1!$A$1,ROW()-ROW(Sheet1!$A$1)+11,6-COLUMN())




"OTS" wrote:

hi there, i need some help using offset.

on sheet 1, i have a table of data. on sheet 2, i manually pick a cell
and reference it to some cell in sheet 1. say for example, in sheet 2,
A1='Sheet1'!F1. what i want is that when i choose A1, A2 will have some
formula making it equal to 'Sheet1'!F6 and A3 will be equal to
'Sheet1'!F14. it seems like i should be using offset for this but i
don't know how to offset from another worksheet.

any help will be appreciated. thank you.