Extend Column instead of row?

[Vihral]

Hi

When i press the corner of a cell and drag it (example =D2) it will only go higher in the number (drag it 3 rows down it just goes D2, D3, D4, D5, D6).

Can I drag it down but make it stick to the 2 and instead go E2, F2, G2 etc?

Thanks in advance!

[brokenbiscuits]

I don't think that's possible, but you could amend your formula to something like:

=OFFSET(C2,0,ROW())

and that'd produce the desired results. Problem being the ROW() is obviously a volatile function - if you insert rows in between, etc., you'd have to amend all your formulas.

[Vihral]

I don't think that's possible, but you could amend your formula to something like:

=OFFSET(C2,0,ROW())

and that'd produce the desired results. Problem being the ROW() is obviously a volatile function - if you insert rows in between, etc., you'd have to amend all your formulas.

I tried copying your code into my file (differece is that my original data is in: ='SLASK SILENT'!B2 so when i drag DOWN I want it to write ='SLASK SILENT'!C2 and then the next drag down would write ='SLASK SILENT'!D2 etc...

Not sure how to do this?

the code i tried was: =OFFSET('SLASK SILENT'!B2;0;ROW())

But that becomes the same thing so when i extend it it goes: =OFFSET('SLASK SILENT'!B3;0;ROW()) and then =OFFSET('SLASK SILENT'!B4;0;ROW()) instead of the desired result: =OFFSET('SLASK SILENT'!C2;0;ROW()) then, =OFFSET('SLASK SILENT'!D2;0;ROW()) etc.

[Vihral]

Unsure bout shouldnt some sort of =INDEX formula work similarly as yours? cant get it to work though

[brokenbiscuits]

Sorry, you'd need to anchor the formula using the $ sign.

=OFFSET('SLASK SILENT'!$B$2;0;ROW())

That'll bring back whatever is in cell C2 on the Slask Silent sheet in Row 1, D2 in row 2, E2 in row 3, etc. You may need to adjust the ROW() to ROW()-2, ROW()+3, or whatever.

You could use INDEX too, yes. Something along the lines of:

=INDEX('SLASK SILENT'!$2:$2,1,ROW())

[Vihral]

Sorry, you'd need to anchor the formula using the $ sign.

=OFFSET('SLASK SILENT'!$B$2;0;ROW())

That'll bring back whatever is in cell C2 on the Slask Silent sheet in Row 1, D2 in row 2, E2 in row 3, etc. You may need to adjust the ROW() to ROW()-2, ROW()+3, or whatever.

You could use INDEX too, yes. Something along the lines of:

=INDEX('SLASK SILENT'!$2:$2,1,ROW())

That =Offset worked great (had to do -2 but it worked fine.
I have the same type of connection to make in 3 other sheets. But here for some reason the SAME formula wont work? Only change is SLASK EMAIL instead of SLASK SILENT, everything is the same.. but wont work >.< any ideas?

[martindwilson]

=INDEX('SLASK SILENT'!$D$2:$AA$2,ROWS($A$1:A1))
where d2 is the first cell you want and aa2 the last

[Vihral]

=INDEX('SLASK SILENT'!$D$2:$AA$2,ROWS($A$1:A1))
where d2 is the first cell you want and aa2 the last

This refuses to work for me. And the =Offset formula that worked on the first 24 rows wot work when i change the SLASK SILENT to SLASK EMAIL which is the name of the other sheet. >.<

[brokenbiscuits]

Sorry, there's no obvious reason that wouldn't work. Are you able to upload a sample?

[Vihral]

Sorry, there's no obvious reason that wouldn't work. Are you able to upload a sample?

Wish I was, work network wont allow me to upload files :P oh well, its only like 2400 rows of formulas i need to enter 1-by-1. shouldnt take more then a few hours xD

[Vihral]

Ill try to explain the structure here:

I have 4 sheets, (SLASK SILENT, SLASK EMAIL, SLASK 121 and SLASK SXS). In each of these sheets the structure is the same:

A1: Person 1
A2: CASE
A3: DATE
A4: SCORE
A5: Person 2
A6: CASE
A7: DATE
A8: SCORE

And so on all the way down to A100 (total of 25 people)

Then the structure is the same on all people:

B2: Case1 | C2: Case2 | D2: Case3

Next person has their at B5: Case1 | C5: Case2 | D5: Case3

All the way down to the last agent.

All Rows are used from B to Y so i need basically B2 to Y2 and then B5 to Y5 and so on for all people.
The sheet i want to put all this into is just called SLASK if that matters. Anyone have ANY idea on what to do?

[Vihral]

Just as a test i tried setting the formula for EMAIL to: =OFFSET('SLASK EMAIL'!$B$2;0;ROW('SLASK EMAIL'!B2:Y2)-2) and that worked (when i hit the last one it says =OFFSET('SLASK EMAIL'!$B$2;0;ROW('SLASK EMAIL'!B25:Y25)-2) but the information is accurate (if i change the target cell the information changes accordingly in the cell. Its strange but works xD (or maybe it makes perfect sense but i just dont see it)

[martindwilson]

see attached i cant understand why you are so set on using offset

[martindwilson]

in your offset this bit is just unnecessary
=ROW(B2:Y2)-2 that resolves to 0 so would row(z2:ax2)-2
but it should be
either just row(b2)-2 or rows($b$2:b3)-2
row just returns the row number of the reference
so row(Z3) =3 so does row(AA3) and row(x3:iv3) so the second cell ref is unnecessary in row()
unless you are trying to create a range of numbers but that is something different and not applicable here

rows however counts the number of rows between 2 references
so rows($a$1:a5) =5 rows($c$3:x8) =6
so fixing the ref with $ allows them to increment
when filled down
=ROWS($A$1:A1) = 1
=ROWS($A$1:A2) = 2
=ROWS($A$1:A3) = 3
=ROWS($A$1:A4) = 4
=ROWS($A$1:A5) = 5
=ROWS($A$1:A6) = 6
=ROWS($A$1:A7) = 7
=ROWS($A$1:A8) = 8
=ROWS($A$1:A9) = 9

[Vihral]

Im not set on using offset or have a prefered vision I just want one that works. ill try to implement the one you have uploaded in the example file to see if i can get it to work ill report back soon!

[martindwilson]

ok i see what you mean now ,well sort of, attach a sample work book

[martindwilson]

Next person has their at B5: Case1 | C5: Case2 | D5: Case3 ? is that not
B6: Case1 | C6: Case2 | D6: Case3
do you mean something like this?

[Vihral]

the attached sample file shows what i wanted but I made it work using the: =INDEX('SLASK EMAIL'!$B$7:$Y$7;ROWS($A$1:A20)) formula that you used in the previous example Thanks a lot for the help (saved me a TON of manual work Solved and ofc credit to you sir!