Using OFFSET with ROW functions

[Tom Richardson]

I am a novice user of Excel trying to work out how to use the OFFSET
function in conjunction with the ROW function.

I am trying to put together a formula that will import every 7th cell value
from a column in a worksheet in another workbook, into a column of my
current workbook worksheet
- that is the values from cell O5, then O12, then O19, etc, etc, in a
worksheet referenced: D:\GSP\Weekly Takings\Turnover
and pasting those values into consecutive cells in a column starting at Q4,
then Q5, then Q6, etc, etc, in a worksheet in my current workbook (named
Years Weekly Takings\Sheet 1).

I know that I need to use OFFSET with ROW, but I can't work out the correct
formula. Can anyone help me please?

 

[Frank Kabel]

Hi
try
=OFFSET('other_sheet'!$O$5,(ROW(1:1)-1)*7,0)
and copy this down

 

[Aladin Akyurek]

=INDEX($O$5:$O$26,(ROW()-ROW(Q$4))*7+1)

Adjust and prefix the range in column O with the source name as needed.

 

[Tom Richardson]

Thank you, Frank. It worked perfectly.

Hi
try
=OFFSET('other_sheet'!$O$5,(ROW(1:1)-1)*7,0)
and copy this down

 

[Tom Richardson]

Thank you, Aladin. I tried your method as well as Frank's, and they both did
the job!

 

[Aladin Akyurek]

There are 2 differences though: The one with INDEX is robust, the one with
OFFSET() not. INDEX() is non-volatile, while OFFSET() is.

 

[Tom Richardson]

Hi Aladin,

Does that mean if I use the INDEX function the column will update
automatically, even though the source workbook is not open?

If so, how to I change my formula from using OFFSET to using INDEX? The
formula I am currently using reads:

=IF(OFFSET('D:\GSPC\[Comparative Weekly Takings Reports Year 4.xls]Annual
Turnover Figures'!$O$5,(ROW(1:1)-1)*7,0)=0,"",(OFFSET('D:\GSPC\[Comparative
Weekly Takings Reports Year 4.xls]Annual Turnover
Figures'!$O$5,(ROW(1:1)-1)*7,0)))

The range from the source workbook is: 'D:\GSPC\[Comparative Weekly Takings
Reports Year 4.xls]Annual Turnover Figures'!$O$5:$O$370 and the data is
retrieved into the range Q4:Q55 in the target workbook.

Thank you for your help.

 

[Aladin Akyurek]

The formula, adapted to your setup, would become:

=IF(INDEX('D:\GSPC\[Comparative Weekly Takings Reports Yea
4.xls]Annual Turnover Figures'!$O$5:$O$370,(ROW()-ROW(Q$4))*7+1)=0,
"",
INDEX('D:\GSPC\[Comparative Weekly Takings Reports Yea
4.xls]Annual Turnover Figures'!$O$5:$O$370,(ROW()-ROW(Q$4))*7+1))

Hi Aladin,

Does that mean if I use the INDEX function the column will update
automatically, even though the source workbook is not open?

If so, how to I change my formula from using OFFSET to using INDEX?
The
formula I am currently using reads:

=IF(OFFSET('D:\GSPC\[Comparative Weekly Takings Reports Yea
4.xls]Annual
Turnove
Figures'!$O$5,(ROW(1:1)-1)*7,0)=0,"",(OFFSET('D:\GSPC\[Comparative
Weekly Takings Reports Year 4.xls]Annual Turnover
Figures'!$O$5,(ROW(1:1)-1)*7,0)))

The range from the source workbook is: 'D:\GSPC\[Comparative Weekl
Takings
Reports Year 4.xls]Annual Turnover Figures'!$O$5:$O$370 and the dat
is
retrieved into the range Q4:Q55 in the target workbook.

Thank you for your help.

There are 2 differences though: The one with INDEX is robust, the on with
OFFSET() not. INDEX() is non-volatile, while OFFSET() is.

 

[Tom Richardson]

Hi Aladin,

I've inputted that formula into the first cell (Q4) on the destination
worksheet, and it immediately returns #REF!. When I click on the cell Excel
highlights the item "Q$4" in both the first and last strings of the IF
statement, so I must be referencing something by mistake.
There's no doubt that the source path is correct, as is the source range.
The first item of sourced data should go into cell Q4 of the destination
worksheet (which in itself references cell O5 of the source worksheet).
Thereafter, Q5 of the destination worksheet is looking at O12 of the source
worksheet, and so on through the year.
The reason I want to use the IF statement is that the source data has only
reached cell O82 (pointing to Q15 in the destination woorksheet) so far this
year, thereafter there are only zeros in the source cells, (that is O89,
O96, etc, etc), and I want to return blank cells in the destination
worksheet when that occurs,
What am I doing wrong?

The formula, adapted to your setup, would become:

=IF(INDEX('D:\GSPC\[Comparative Weekly Takings Reports Year
4.xls]Annual Turnover Figures'!$O$5:$O$370,(ROW()-ROW(Q$4))*7+1)=0,
"",
INDEX('D:\GSPC\[Comparative Weekly Takings Reports Year
4.xls]Annual Turnover Figures'!$O$5:$O$370,(ROW()-ROW(Q$4))*7+1))

Hi Aladin,

Does that mean if I use the INDEX function the column will update
automatically, even though the source workbook is not open?

If so, how to I change my formula from using OFFSET to using INDEX?
The
formula I am currently using reads:

=IF(OFFSET('D:\GSPC\[Comparative Weekly Takings Reports Year
4.xls]Annual
Turnover
Figures'!$O$5,(ROW(1:1)-1)*7,0)=0,"",(OFFSET('D:\GSPC\[Comparative
Weekly Takings Reports Year 4.xls]Annual Turnover
Figures'!$O$5,(ROW(1:1)-1)*7,0)))

The range from the source workbook is: 'D:\GSPC\[Comparative Weekly
Takings
Reports Year 4.xls]Annual Turnover Figures'!$O$5:$O$370 and the data
is
retrieved into the range Q4:Q55 in the target workbook.

Thank you for your help.

There are 2 differences though: The one with INDEX is robust, the one with
OFFSET() not. INDEX() is non-volatile, while OFFSET() is.

Thank you, Aladin. I tried your method as well as Frank's, and they both
did the job!

=INDEX($O$5:$O$26,(ROW()-ROW(Q$4))*7+1)

Adjust and prefix the range in column O with the source name as needed.

I am a novice user of Excel trying to work out how to use the OFFSET
function in conjunction with the ROW function.

I am trying to put together a formula that will import every 7th cell
value from a column in a worksheet in another workbook, into a column
of my current workbook worksheet
- that is the values from cell O5, then O12, then O19, etc, etc, in a
worksheet referenced: D:\GSP\Weekly Takings\Turnover
and pasting those values into consecutive cells in a column starting at
Q4, then Q5, then Q6, etc, etc, in a worksheet in my current workbook
(named Years Weekly Takings\Sheet 1).

I know that I need to use OFFSET with ROW, but I can't work out the
correct formula. Can anyone help me please?

 

[Aladin Akyurek]

Maybe a strange char or something other is introduced during copying and
pasting the formula in your spreadsheet if that's what you did. Try to type
the formula in Q4 and see what happens.

Hi Aladin,

I've inputted that formula into the first cell (Q4) on the destination
worksheet, and it immediately returns #REF!. When I click on the cell
Excel highlights the item "Q$4" in both the first and last strings of the
IF statement, so I must be referencing something by mistake.
There's no doubt that the source path is correct, as is the source range.
The first item of sourced data should go into cell Q4 of the destination
worksheet (which in itself references cell O5 of the source worksheet).
Thereafter, Q5 of the destination worksheet is looking at O12 of the
source worksheet, and so on through the year.
The reason I want to use the IF statement is that the source data has only
reached cell O82 (pointing to Q15 in the destination woorksheet) so far
this year, thereafter there are only zeros in the source cells, (that is
O89, O96, etc, etc), and I want to return blank cells in the destination
worksheet when that occurs,
What am I doing wrong?

The formula, adapted to your setup, would become:

=IF(INDEX('D:\GSPC\[Comparative Weekly Takings Reports Year
4.xls]Annual Turnover Figures'!$O$5:$O$370,(ROW()-ROW(Q$4))*7+1)=0,
"",
INDEX('D:\GSPC\[Comparative Weekly Takings Reports Year
4.xls]Annual Turnover Figures'!$O$5:$O$370,(ROW()-ROW(Q$4))*7+1))

Hi Aladin,

Does that mean if I use the INDEX function the column will update
automatically, even though the source workbook is not open?

If so, how to I change my formula from using OFFSET to using INDEX?
The
formula I am currently using reads:

=IF(OFFSET('D:\GSPC\[Comparative Weekly Takings Reports Year
4.xls]Annual
Turnover
Figures'!$O$5,(ROW(1:1)-1)*7,0)=0,"",(OFFSET('D:\GSPC\[Comparative
Weekly Takings Reports Year 4.xls]Annual Turnover
Figures'!$O$5,(ROW(1:1)-1)*7,0)))

The range from the source workbook is: 'D:\GSPC\[Comparative Weekly
Takings
Reports Year 4.xls]Annual Turnover Figures'!$O$5:$O$370 and the data
is
retrieved into the range Q4:Q55 in the target workbook.

Thank you for your help.


There are 2 differences though: The one with INDEX is robust, the one
with
OFFSET() not. INDEX() is non-volatile, while OFFSET() is.

Thank you, Aladin. I tried your method as well as Frank's, and they
both
did the job!

=INDEX($O$5:$O$26,(ROW()-ROW(Q$4))*7+1)

Adjust and prefix the range in column O with the source name as
needed.

I am a novice user of Excel trying to work out how to use the
OFFSET
function in conjunction with the ROW function.

I am trying to put together a formula that will import every 7th
cell
value from a column in a worksheet in another workbook, into a
column
of my current workbook worksheet
- that is the values from cell O5, then O12, then O19, etc, etc,
in a
worksheet referenced: D:\GSP\Weekly Takings\Turnover
and pasting those values into consecutive cells in a column
starting at
Q4, then Q5, then Q6, etc, etc, in a worksheet in my current
workbook
(named Years Weekly Takings\Sheet 1).

I know that I need to use OFFSET with ROW, but I can't work out
the
correct formula. Can anyone help me please?

--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=4165
View this thread:
http://www.excelforum.com/showthread.php?threadid=271576

 

[Tom Richardson]

Yep, that's what I did! Now, how stupid do I feel......
It's working perfectly - I opened, closed, and re-opened the workbook to
check.
Thank you for your help and your patience, Aladin.

Kindest regards,

Tom Richardson

Maybe a strange char or something other is introduced during copying and
pasting the formula in your spreadsheet if that's what you did. Try to
type the formula in Q4 and see what happens.

Hi Aladin,

I've inputted that formula into the first cell (Q4) on the destination
worksheet, and it immediately returns #REF!. When I click on the cell
Excel highlights the item "Q$4" in both the first and last strings of the
IF statement, so I must be referencing something by mistake.
There's no doubt that the source path is correct, as is the source range.
The first item of sourced data should go into cell Q4 of the destination
worksheet (which in itself references cell O5 of the source worksheet).
Thereafter, Q5 of the destination worksheet is looking at O12 of the
source worksheet, and so on through the year.
The reason I want to use the IF statement is that the source data has
only reached cell O82 (pointing to Q15 in the destination woorksheet) so
far this year, thereafter there are only zeros in the source cells, (that
is O89, O96, etc, etc), and I want to return blank cells in the
destination worksheet when that occurs,
What am I doing wrong?

The formula, adapted to your setup, would become:

=IF(INDEX('D:\GSPC\[Comparative Weekly Takings Reports Year
4.xls]Annual Turnover Figures'!$O$5:$O$370,(ROW()-ROW(Q$4))*7+1)=0,
"",
INDEX('D:\GSPC\[Comparative Weekly Takings Reports Year
4.xls]Annual Turnover Figures'!$O$5:$O$370,(ROW()-ROW(Q$4))*7+1))


Tom Richardson Wrote:
Hi Aladin,

Does that mean if I use the INDEX function the column will update
automatically, even though the source workbook is not open?

If so, how to I change my formula from using OFFSET to using INDEX?
The
formula I am currently using reads:

=IF(OFFSET('D:\GSPC\[Comparative Weekly Takings Reports Year
4.xls]Annual
Turnover
Figures'!$O$5,(ROW(1:1)-1)*7,0)=0,"",(OFFSET('D:\GSPC\[Comparative
Weekly Takings Reports Year 4.xls]Annual Turnover
Figures'!$O$5,(ROW(1:1)-1)*7,0)))

The range from the source workbook is: 'D:\GSPC\[Comparative Weekly
Takings
Reports Year 4.xls]Annual Turnover Figures'!$O$5:$O$370 and the data
is
retrieved into the range Q4:Q55 in the target workbook.

Thank you for your help.


There are 2 differences though: The one with INDEX is robust, the one
with
OFFSET() not. INDEX() is non-volatile, while OFFSET() is.

Thank you, Aladin. I tried your method as well as Frank's, and they
both
did the job!

=INDEX($O$5:$O$26,(ROW()-ROW(Q$4))*7+1)

Adjust and prefix the range in column O with the source name as
needed.

I am a novice user of Excel trying to work out how to use the
OFFSET
function in conjunction with the ROW function.

I am trying to put together a formula that will import every 7th
cell
value from a column in a worksheet in another workbook, into a
column
of my current workbook worksheet
- that is the values from cell O5, then O12, then O19, etc, etc,
in a
worksheet referenced: D:\GSP\Weekly Takings\Turnover
and pasting those values into consecutive cells in a column
starting at
Q4, then Q5, then Q6, etc, etc, in a worksheet in my current
workbook
(named Years Weekly Takings\Sheet 1).

I know that I need to use OFFSET with ROW, but I can't work out
the
correct formula. Can anyone help me please?









--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=4165
View this thread:
http://www.excelforum.com/showthread.php?threadid=271576