Hi

I run Excel 2K.

I have one column and 500 rows of data on one sheet. On another sheet I
want to have a formula that is in every 3rd row which reads each of the
entries in the first sheet.

I can do this manually of course, however when I try to copy it down the
sheet the cell references are all wrong because I am jumping 3 rows at a
time. Is there a formula that will allow me just to copy it down in 3 cell
increments but have it refer to the sequential cells in the 1st sheet.


Thanks

John Calder wrote:
Hi

I run Excel 2K.

I have one column and 500 rows of data on one sheet. On another sheet I
want to have a formula that is in every 3rd row which reads each of the
entries in the first sheet.

I can do this manually of course, however when I try to copy it down the
sheet the cell references are all wrong because I am jumping 3 rows at a
time. Is there a formula that will allow me just to copy it down in 3 cell
increments but have it refer to the sequential cells in the 1st sheet.


Thanks

Probably yes, but how to do it depends on the formula you are using and
possibly the specific design of both sheets.

Let's start with the basics: /What exactly/ are you trying to accomplish
on "another sheet"?

Hi John,

Is there a formula that will allow me just to copy it down in 3 cell
increments but have it refer to the sequential cells in the 1st sheet.

You can try;
=INDIRECT(ADDRESS(1+ROW()/3,1,,,"Sheet2"))

Copy it in a cell in row 1, it will return the value in cell A1 in Sheet2

Copy to row 4, returns value in cell A2 in Sheet2

To change the returned cell reference, change the '1's in the formula

Ed Ferrero
www.edferrero.com

Thanks for your prompt response.

OK I will give you what I need. I have 2 sheets. 1st sheet is called 'DRO BY
SHIFT' the 2nd sheet is called 'DRO BY DAY'

On the DRO BY SHIFT I have blocks of 3 rows of data that represent a single
date but is broken into shifts.

A7, A8 & A9 have the same date in and cells D7, D8 & D9 have the letters D,
A & N
in them.

(these letters represent shifts (D=Day, A=Afternoon, N= Night)

In cell V7 of the sheet 'DRO BY SHIFT' I require the formula.
In cell V10 of the sheet 'DRO BY SHIFT' I require the formula.
In cell V13 of the sheet 'DRO BY SHIFT' I require the formula.
etc, etc, etc

In the 2nd sheet the data is where each row is data that represents a date.
So every row has a date next to it unlike the 1st sheet which has 3 rows of
data for each date.

I need to copy the data in cell BU23 on sheet 'DRO BY DAY' into V7 in sheet
"DRO BY SHIFT'.

(this is simple enough by using ='DRO BY DAY'!BU23)

BUT !

Then have it that when I copy it down the column V that it copies what is in
cell BU24 in sheet "DRO BY DAY' into V10 in sheet 'DRO BY SHIFT'

then BU25 into V13
then BU26 into V16

etc etc etc


Hope this helps

John


"smartin" wrote:

John Calder wrote:
Hi

I run Excel 2K.

I have one column and 500 rows of data on one sheet. On another sheet I
want to have a formula that is in every 3rd row which reads each of the
entries in the first sheet.

I can do this manually of course, however when I try to copy it down the
sheet the cell references are all wrong because I am jumping 3 rows at a
time. Is there a formula that will allow me just to copy it down in 3 cell
increments but have it refer to the sequential cells in the 1st sheet.


Thanks

Probably yes, but how to do it depends on the formula you are using and
possibly the specific design of both sheets.

Let's start with the basics: /What exactly/ are you trying to accomplish
on "another sheet"?

Maybe this in DRO BY SHIFT cell V7, and fill/copy down:

=IF(D7="D",VLOOKUP(A7,'DRO BY DAY'!A:BU,73,FALSE),"")
! !!

or just

=VLOOKUP(A7,'DRO BY DAY'!A:BU,73,FALSE)
! !!

Note:
You did not say which column in DRO BY DAY has the date to match. I
assumed it is in column A. If that is not correct, you will need to
adjust the VLOOKUP formula where indicated by "!" above (view in fixed
width font for clarity). Write back if you need more help with this.


John Calder wrote:
Thanks for your prompt response.

OK I will give you what I need. I have 2 sheets. 1st sheet is called 'DRO BY
SHIFT' the 2nd sheet is called 'DRO BY DAY'

On the DRO BY SHIFT I have blocks of 3 rows of data that represent a single
date but is broken into shifts.

A7, A8 & A9 have the same date in and cells D7, D8 & D9 have the letters D,
A & N
in them.

(these letters represent shifts (D=Day, A=Afternoon, N= Night)

In cell V7 of the sheet 'DRO BY SHIFT' I require the formula.
In cell V10 of the sheet 'DRO BY SHIFT' I require the formula.
In cell V13 of the sheet 'DRO BY SHIFT' I require the formula.
etc, etc, etc

In the 2nd sheet the data is where each row is data that represents a date.
So every row has a date next to it unlike the 1st sheet which has 3 rows of
data for each date.

I need to copy the data in cell BU23 on sheet 'DRO BY DAY' into V7 in sheet
"DRO BY SHIFT'.

(this is simple enough by using ='DRO BY DAY'!BU23)

BUT !

Then have it that when I copy it down the column V that it copies what is in
cell BU24 in sheet "DRO BY DAY' into V10 in sheet 'DRO BY SHIFT'

then BU25 into V13
then BU26 into V16

etc etc etc


Hope this helps

John


"smartin" wrote:

John Calder wrote:
Hi

I run Excel 2K.

I have one column and 500 rows of data on one sheet. On another sheet I
want to have a formula that is in every 3rd row which reads each of the
entries in the first sheet.

I can do this manually of course, however when I try to copy it down the
sheet the cell references are all wrong because I am jumping 3 rows at a
time. Is there a formula that will allow me just to copy it down in 3 cell
increments but have it refer to the sequential cells in the 1st sheet.


Thanks
Probably yes, but how to do it depends on the formula you are using and
possibly the specific design of both sheets.

Let's start with the basics: /What exactly/ are you trying to accomplish
on "another sheet"?