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.

What progression do you want for Column F?

You're showing
F1
F6
F14

Is that a typo?

If not, what's the next couple of references?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"OTS" wrote in message
ups.com...
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.

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.

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.

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.

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.

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.

Bernie, OTS,
Sorry I was late getting back to the party. I saw OTS post after mine this
morning and it hit me he was looking for a V/HLookup() type of solution then,
but I didn't have time to examine in detail and answer then.

As far as the macro-phobic in the crowd, there is a good chance that the
whole solution could be written as a User Defined Function (UDF) that do the
whole thing invisibly to the end user. I can't quite get my head wrapped
around where formulas are being changed and how in his current setup. Kind
of need to see Before and After 'pictures' of cell locations and
contents/formulas in all of this.

As for why Excel quits looking in almost all lists in functions like the
LOOKUP functions and MATCH and even INDEX, it's one of what those functions
were designed to do - find the FIRST match or closest to it depending on some
other parameters. You'd probably need a new function (VLOOKUPC() ?? -
VLOOKUP and Continue Looking Up?) so that at the very least you could more
easily examine worksheet formulas and see what is going on. But think about
the actual implementation somewhe
The first VLOOKUPC() that found something would not only have to show what
it found, but also keep a 'where I found it' pointer somewhere, then
subsequent uses of it in the same area would have to look back up the sheet
(or to the left or whatever) to find out even if it needs to look for another
match in the table. Plus the first VLOOKUPC() has the added overhead of
figuring out just that it IS the first one used in the "group" and act
accordingly. And how do you tell it to ignore perhaps the 1st 3 or 1st 4
matches and show you the results of the 5th match - and if you instruct it
that way, then how do you insure there are the requisite number of matches
ahead of it? Nope, this kind of thing is definitely a job for VBA, I think.

"Bernie Deitrick" wrote:

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.