copying cells down

[johnjohn5]

The problem is i have a sheet with names (example J2=User1, P2=User2).
i want to copy the data from one sheet to another but on the new sheet i need to copy down instead of accross (example B18=(sheet1.J2=User1), B33=(sheet1.P2=User2)).

The idea is i need the letter or column to change not the number or row. An example of this would be copying cells down and getting =B2, =C2, =D2 instead of =B2, =B3, =B4 .

[martindwilson]

=INDIRECT(ADDRESS(1,ROW()+1))
in the example above the address is (row,column)
so formula in a1 (row 1,col1) would return row1 colum 2 ie b1
if you wished col a1 down to be = b2,c2,d2,e2
you'd use
=INDIRECT(ADDRESS(2,ROW()+1))
that is row 2 column =your row number+1
not very clear i know but play with it,

[johnjohn5]

Will this work for pulling information from one sheet to another. I idea is we have 2 sheets one has data and the next has statistics. sheet one is titled "Personnel 1 st shift", sheet two is titled "Personnel 1 st shift Summary".
On page one names are accross row 2 (example J2=User1, P2= User2, V2=User3). On page two they are down column B (example B18=User1, B33=User2, B48=User3).
Also if possible i want data on page 2 to change when i input data into sheet one.

[Mark@Work]

a little bit confused as to what you are actually looking for...

There are several techniques that might appy to your problem:
the INDIRECT(ADDRESS()) is one,
another function that might help you is INDEX(),
a third is TRANSPOSE(),
and there may even be more!

Which one is most appropriate depends on the precise nature of your problem.

[johnjohn5]

I'm glad to hear i have many options but I to don't know which would be better or easier. This i the nature of my problem. In one sheet i have peoples names along with data
ex.
A | B | C | D | E | F || G | H | I |...
1 | John ______________________________| | Sam |...
2 Top | Bot | Mid | Hood | Left | Total ||Top | Bot | Mid | Hood |...
3 0 | 1 | 2 | 0 | 2 | 5 || 4 | 5 | 0 | 0 |...
4 3 | 0 | 0 | 0 | 0 | 0 || 5 | 0 | 0 | 0 |...
5 ...More info...
6 ...More info...(365 Days/Rows of info)

Now on my next sheet i have statisics on each person
ex.
A | B | C | D | E | F | G | H | I |...
1 John
2 ToSamp |TSw/0| %0 | NAT | NASS|....12 columns of Titles/Data
3 0 | 2 | 2 | 5 | 1 |....
4
5 Sam
6 ToSamp |TSw/0| %0 | NAT | NASS|....12 columns of Titles/data
7 5 | 2 | 2 | 0 | 4 |....
8 And more people

The problem is i have a lot of people i need to keep track of and its always changing so i need a way i can highlight the info from sheet two and drag down to give me a new set of statisics on a new person. now let me give you an example of what sheet two looks like with formulas.

A | .....
1 ='Sheet1'!C1
2 Total Samples | ....
3 =countif('sheet1'!A3:E4,">=0") | ....
4
5 ='Sheet1'!I1
6 Total Samples | ....
7 =countif('sheet1'!G3:J4,">=0")

I want a way i can highlight rows i through 4 and copy them down about 50 people later. i could make each one at a time but it wont be user friend if i'm not here and someone need to add someone to the sheet.

One thing i did try is transpose and that didnt work because i have more lights on the last page then i do on the first page.

what i really need is a way i can copy a formula that says something like this

A1='Sheet1'!C1
A5=A1(which is 'sheet1'!C1)+6 columns (which would be 'sheet1'!I1)

[Mark@Work]

I think INDEX() in conjunction with OFFSET() would probably suit you best.
I assume each person on sheet 1 has a fairly regular arrangement of cells relating to them.
OFFSET(Sheet1!$A$1,5,6,3,100) would reference a block of cells 3 rows deep by 100 columns wide, offset 5 rows down and 6 columns over from cell A1 on sheet1.
INDEX(OFFSET(Sheet1!$A$1,5,6,3,100),2,9) would reference the cell 2 rows down and 9 columns across within that block (ie Sheet1!O7.)
It takes a bit of mental juggling but after a while you get used to it.
Now looking at your posts it seems that the data for each of your people starts on row 1, so the row offset will always be zero.
You have 365 days (366 on a leap year?) + two rows of heading so the height of the offset block is 367 (368?)
You seem to indicate 9 columns for each person, so
OFFSET(Sheet1!$A$1,0,0,368,9) would reference the block of data for the first person,
OFFSET(Sheet1!$A$1,9,0,368,9) would reference the block of data for the second person,
OFFSET(Sheet1!$A$1,18,0,368,9) would reference the block of data for the third person...
INDEX(OFFSET(Sheet1!$A$1,18,0,368,9),3,4) would reference the cell 3 rows down and 4 columns across for the third person
INDEX(OFFSET(Sheet1!$A$1,(I-1)*9,0,368,9),3,4) would reference exactly the same piece of information, but for the Ith person.
You can now set up a block of these formula on Sheet2 referencing the data you want in Sheet1 thus:
A1 : INDEX(OFFSET(Sheet1!$A$1,(B1-1)*9,0,368,9),1,1)
B1 : 1
: :
: :
A5 : INDEX(OFFSET(Sheet1!$A$1,(B5-1)*9,0,368,9),1,1)
B5 : 2
Note that I have taken the (currently unused?) cell next to the persons name in column B to determine which persons data will be bought across for each case.

Hope this makes sense for you,
Mark.

[johnjohn5]

I think, what you said will really work for my problem but i gave you sample of what i was thinking of. i have posted the whole thing here. i have removed all data and names though.

[johnjohn5]

I gave it a try but i couldnt figure it out. this is what i did figure out though.

OFFSET(Sheet1!$h$2,0,2) would reference the block of data for the first person,
OFFSET(Sheet1!$h$2,0,8) would reference the block of data for the second person,
OFFSET(Sheet1!$h$2,0,14) would reference the block of data for the third person...
i get lost when you add ",368,9" behind the offset. i have given it a try i dont know how many times and each time it reads 0 or #Value!
example of sheet

____|____H____|____I____|____J____|____K____|____L____|
2______________________|___JOHN__|___________________|
3________Titles_________Titles_______________Titles_______|
4____|____0___|____1____|___9____|_____8___|____18____|
5.

369 rows in all 368 rows from john (john is on row 2).

[Mark@Work]

Have a look at the attached workbook.

Note on the summary sheet:
N19 & N34 give the column number of the start of the user data in the main sheet.
N20-31 & N35-46 give the row number of the start of the months data.
O20-31 & O35-46 give the number of days in the month.

These 2 columns shhould be hidden when you are happy with the result.

[johnjohn5]

I'm not sure what i'm doing wrong. Each time i copy down the next group of user data i get "#Name!" in data boxes. If you still have the file you posted, try and copy down and see what happens.

[Mark@Work]

I am sorry, but I have no problem with it!

I just selected the entire rows 19 to 46 (of the summary sheet), copyed, then pasted into cell A49 and got 2 new users that appear to work fine!

Name generally indicates an unrecognised FUNCTION.
I am using office 2007, but I don't think I am using anything that would upset earlier versions.
Which cell exactly gives the error?

Mark.

[johnjohn5]

i get #Name? from B50:M61, O50, N51:O61. i dont know what to say other then i'm using Excel 2000.

[johnjohn5]

i got it i needed to install some add-ins Thanks for all your help

[johnjohn5]

Thanks again. I'm not sure what im doing because i cant post my question as solved. for what ever reason it doesnt give me an option to edit my first post.

On second thought, please post as solved. (I think thats what i do