Anchoring issue - must be something wrong

[soilwork6]

Hi guys. For some reason I am having an anchoring issue i.e. B$6 is acting the same way as $B$6. I am only trying to anchor the row not the column and they are both anchoring which I don't want. When I drag down, B6 doesn't change either way I try it. Must be a quick fix I'm not seeing. Thanks.

 

[Aladin Akyurek]

What must B6 become when you drag down just one cell?

 

[soilwork6]

C6
d6
e6
f6

 

[Aladin Akyurek]

C6
d6
e6
f6

Let's suppose that you want the contents B6, C6, D6,... in A10, A11, A12, etc. The following would do that...

In A10 enter and drag down:

=INDEX($B$6:$Z$6,ROWS($A$10:A10))

Does this help you out?

 

[soilwork6]

Yes thank you. What made you think of this formula? This does work and I only need to change two numbers when I grab the formula and paste it in another column that I pulling data for in the next row down. I am still surprised with the anchoring for both anchored column and row and just anchoring the row..

 

[Aladin Akyurek]

Yes thank you. What made you think of this formula? This does work and I only need to change two numbers when I grab the formula and paste it in another column that I pulling data for in the next row down. I am still surprised with the anchoring for both anchored column and row and just anchoring the row..

1.

=$B6

dragged down,we get successively the contents of

B6
B7
B8
.
.
.

This is the intent of the set up: the column component is fixed while the row component increments.

Dragging to the right, this will return the contents of B6 repeatedly in each successive cell.

2.

=B$6

dragged to the right, we get successively the contents of

B6, C6, D6, E6, ...

This is the intent of the set up: the column component increments while the row component is fixed.

Dragging down will return the contents of B6 repeatedly in each successive cell.

3.

=B6

dragging to the right and the down will yield:

B6, C6, D6...
B7, C7, D7...
B8, C8, D8...

4.

=$B$6

gives just the contents of B6 when dragged down or when dragged to the right.

Hope this helps.

 

[soilwork6]

Thanks for your detailed reply. I don't think I phrased it well. On the sheet that I am dragging the formula down for example is:

B1
B2
B3
B4

Will need to pull information from another sheet

B1 ( will pull info from another sheet from cell C6)
B2 (..D7)
B3 (E7)
B4 (F7)

and so on..

I am pulling info down from a reference that goes across.. kind of like transpose..

1.

=$B6

dragged down,we get successively the contents of

B6
B7
B8
.
.
.

This is the intent of the set up: the column component is fixed while the row component increments.

Dragging to the right, this will return the contents of B6 repeatedly in each successive cell.

2.

=B$6

dragged to the right, we get successively the contents of

B6, C6, D6, E6, ...

This is the intent of the set up: the column component increments while the row component is fixed.

Dragging down will return the contents of B6 repeatedly in each successive cell.

3.

=B6

dragging to the right and the down will yield:

B6, C6, D6...
B7, C7, D7...
B8, C8, D8...

4.

=$B$6

gives just the contents of B6 when dragged down or when dragged to the right.

Hope this helps.

 

[Aladin Akyurek]

Thanks for your detailed reply. I don't think I phrased it well. On the sheet that I am dragging the formula down for example is:

B1
B2
B3
B4

Will need to pull information from another sheet

B1 ( will pull info from another sheet from cell C6)
B2 (..D7)
B3 (E7)
B4 (F7)

and so on..

I am pulling info down from a reference that goes across.. kind of like transpose..

In B1 (of Sheet1) enter and copy down:

=INDEX(Sheet2!$C$7:$Z$7,ROWS($B$1:B1))

 

[soilwork6]

In B1 (of Sheet1) enter and copy down:

=INDEX(Sheet2!$C$7:$Z$7,ROWS($B$1:B1))

Yep that works great. Noted! Thanks all for your help.

 

[Aladin Akyurek]

Yep that works great. Noted! Thanks all for your help.

You are welcome.