Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i put increments in the row or column numbers?
with sheet1, i have a column, no. of days, which calls a cell in sheet2.
sheet1: staff no. of days ann A2 - calls sheet1!A8 jess A3 - calls sheet1!A19 staff c A4 - how do i automatically call sheet1!30 (A3 calls sheet1!A19 plus 11 cells - 11 rows down?) etc A5 calls so on...sheet1!A41, A52, A63... sheet2: A B C D E 1 Staff Time S M T 2 ann 8:00-8:30 AM comm wtc comm 3 8:30-9:00 AM comm wtc comm 4 Total: 9:00-9:30 AM comm wtc comm 5 6 4/8 9:30-10:00 AM comm wtc comm 6 10:00-10:30 AM comm comm comm 7 Days: 10:30-11:00 AM comm comm comm 8 6.5 11:00-11:30 AM comm comm comm 9 11:30-12:00 PM comm comm comm 10 11 A B C D E 12 Staff Time S M T 13 jess 8:00-8:30 AM comm wtc 14 8:30-9:00 AM comm wtc 15 Total: 9:00-9:30 AM comm wtc 16 6 4/8 9:30-10:00 AM comm wtc 17 10:00-10:30 AM comm comm 18 Days: 10:30-11:00 AM comm comm 19 6.5 11:00-11:30 AM comm comm comm 20 11:30-12:00 PM comm comm comm is there a formula like R[11]C[1] which adds 11 to the current row number or something? please help, thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i put increments in the row or column numbers?
Either of these
=INDEX(Sheet1!A:A,(ROW()-1)*11-3) =INDIRECT("Sheet1!A"&(ROW()-1)*11-3) placed in A2 and copied down the column best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "brigits" wrote in message ... with sheet1, i have a column, no. of days, which calls a cell in sheet2. sheet1: staff no. of days ann A2 - calls sheet1!A8 jess A3 - calls sheet1!A19 staff c A4 - how do i automatically call sheet1!30 (A3 calls sheet1!A19 plus 11 cells - 11 rows down?) etc A5 calls so on...sheet1!A41, A52, A63... sheet2: A B C D E 1 Staff Time S M T 2 ann 8:00-8:30 AM comm wtc comm 3 8:30-9:00 AM comm wtc comm 4 Total: 9:00-9:30 AM comm wtc comm 5 6 4/8 9:30-10:00 AM comm wtc comm 6 10:00-10:30 AM comm comm comm 7 Days: 10:30-11:00 AM comm comm comm 8 6.5 11:00-11:30 AM comm comm comm 9 11:30-12:00 PM comm comm comm 10 11 A B C D E 12 Staff Time S M T 13 jess 8:00-8:30 AM comm wtc 14 8:30-9:00 AM comm wtc 15 Total: 9:00-9:30 AM comm wtc 16 6 4/8 9:30-10:00 AM comm wtc 17 10:00-10:30 AM comm comm 18 Days: 10:30-11:00 AM comm comm 19 6.5 11:00-11:30 AM comm comm comm 20 11:30-12:00 PM comm comm comm is there a formula like R[11]C[1] which adds 11 to the current row number or something? please help, thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i put increments in the row or column numbers?
Of course, we could make this simpler using: ROW()*11-14
-- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "brigits" wrote in message ... with sheet1, i have a column, no. of days, which calls a cell in sheet2. sheet1: staff no. of days ann A2 - calls sheet1!A8 jess A3 - calls sheet1!A19 staff c A4 - how do i automatically call sheet1!30 (A3 calls sheet1!A19 plus 11 cells - 11 rows down?) etc A5 calls so on...sheet1!A41, A52, A63... sheet2: A B C D E 1 Staff Time S M T 2 ann 8:00-8:30 AM comm wtc comm 3 8:30-9:00 AM comm wtc comm 4 Total: 9:00-9:30 AM comm wtc comm 5 6 4/8 9:30-10:00 AM comm wtc comm 6 10:00-10:30 AM comm comm comm 7 Days: 10:30-11:00 AM comm comm comm 8 6.5 11:00-11:30 AM comm comm comm 9 11:30-12:00 PM comm comm comm 10 11 A B C D E 12 Staff Time S M T 13 jess 8:00-8:30 AM comm wtc 14 8:30-9:00 AM comm wtc 15 Total: 9:00-9:30 AM comm wtc 16 6 4/8 9:30-10:00 AM comm wtc 17 10:00-10:30 AM comm comm 18 Days: 10:30-11:00 AM comm comm 19 6.5 11:00-11:30 AM comm comm comm 20 11:30-12:00 PM comm comm comm is there a formula like R[11]C[1] which adds 11 to the current row number or something? please help, thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do i put increments in the row or column numbers?
Hi,
Also: in any cell =INDEX(A:A,ROW(A1)*11-3) HTH Jean-Guy "brigits" wrote: with sheet1, i have a column, no. of days, which calls a cell in sheet2. sheet1: staff no. of days ann A2 - calls sheet1!A8 jess A3 - calls sheet1!A19 staff c A4 - how do i automatically call sheet1!30 (A3 calls sheet1!A19 plus 11 cells - 11 rows down?) etc A5 calls so on...sheet1!A41, A52, A63... sheet2: A B C D E 1 Staff Time S M T 2 ann 8:00-8:30 AM comm wtc comm 3 8:30-9:00 AM comm wtc comm 4 Total: 9:00-9:30 AM comm wtc comm 5 6 4/8 9:30-10:00 AM comm wtc comm 6 10:00-10:30 AM comm comm comm 7 Days: 10:30-11:00 AM comm comm comm 8 6.5 11:00-11:30 AM comm comm comm 9 11:30-12:00 PM comm comm comm 10 11 A B C D E 12 Staff Time S M T 13 jess 8:00-8:30 AM comm wtc 14 8:30-9:00 AM comm wtc 15 Total: 9:00-9:30 AM comm wtc 16 6 4/8 9:30-10:00 AM comm wtc 17 10:00-10:30 AM comm comm 18 Days: 10:30-11:00 AM comm comm 19 6.5 11:00-11:30 AM comm comm comm 20 11:30-12:00 PM comm comm comm is there a formula like R[11]C[1] which adds 11 to the current row number or something? please help, thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using an Index by Column Headings instead of Column Numbers | New Users to Excel | |||
placing numbers in one column at intervals of 2nd | Excel Discussion (Misc queries) | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions |