Faizal,

With multiple, repeated key values, you could have used an Index row, with
unique values, inserted above your data table, as simple as =COLUMN(). That
may still be an option if you need to satisfy Macro-phobic users....

Bernie
MS Excel MVP


"OTS" wrote in message
ups.com...
hi, thanks for the suggestion.

that worked fine until there was a duplicate value down the line, in
which case excel would only pick up on the first instance it occurred.

i've found a solution, however it involves vba. from another thread in
this group, someone recommended a site which gave this code:

Function GetFormula(Cell as Range) as String
GetFormula = Cell.Formula
End Function

so all i had to do was get the formula "=sheet1!F1" from cell A1 as a
string, then I could use indirect to make "sheet1!F1" into a proper
cell reference and then offset it to get "sheet1!f6" & "sheet1!f14".
problem solved, although i would have liked to do it without vba. it's
surprising that excel doesn't have such a simple function.

anyway, thanks for the help.

regards,
faizal.

On Jan 23, 8:35 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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
ooglegroups.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.