How to set a number of rows for dragging down the table

[skalus]

Hi All,

I encountered a problem that almost pulled all my hair out.
I work on two sheets. In sheet1 I have table that contains in every row a name of person and in every column a certain number correct for every individual. In sheet2 I have other table, for every individual collating some of the information from sheet1.
It looks like:

Name
A1 =sheet1!B2
A2 =sheet1!C2

If I highlight the table and drag it down to copy the formula I will get something like:

Name
A3 =sheet1!B5
A4 =sheet1!C5

But what I want to get is:

Name
A3 =sheet1!B3
A4 =sheet1!C3

Is there a way to set it up so when I drag the table down the row will increase every 1 not every 3?

Thank you very much for you thoughts.

[benishiryo]

hi skalus, welcome to the forum. try:
=INDEX(Sheet1!B:C,2+FLOOR((ROW(1:1)-1)/2,1),1+MOD(ROW(2:2),2))

[Cheeky Charlie]

Try:
A1=INDEX(Sheet1!B:B,ROW(4:4)/2)
A2=INDEX(Sheet1!C:C,ROW(4:4)/2)

[Goldbadger2]

Why not use table names in your formulas?

In your table in sheet2 have something like

Please Login or Register  to view this content.

where Table1 is the name of sheet1 table and Name is the header.

Otherwise set up a pivot table in sheet2.

Andy

[skalus]

Thank a lot, the Row Formula that both Cheekie Charlie and benishhiryo wrote seems to work on the given example. My table is more complicated so just to grasp the principle:
A1=INDEX(Sheet1!B:B,ROW(4:4)/2)
B:B - i put the column the info should be taken from
ROW(4:4) - i put where the initial info in row is stored
/2 - is the amount of rows I am skiping when dragging the table?

Under the table I also have a pie chart which relates to the table (they take whole A4 page). When I drag it the pie chart copies but it relates to the initial table. Do you know how to lock it, so when I drag it the pie chart will relate to the table which is on the same page as table?

Thanks again guys

[Cheeky Charlie]

In order:
Yes
No
No

row(4:4) evaluates the row number of row 4. Which is 4.
/2 divides that by 2. Which gives 2.
:D

So row(x:x)/2 gives a number which increments by 0.5 for each row it's copied down.

That returns 2 for the first copied row, 2.5 for the next, 3 for the third...
index(B:B,2) = B2 value
index(B:B,2.5) = B2 value (index ignores decimals)
index(B:B,3) = B3 value

Does that help?

Ref charts, don't know, sorry. Start a fresh thread.

[skalus]

thank a lot Cheeky Charlie and now I need to go and open a new thread.
Thank again