Forum Discussion
Cell Referencing not Autofilling Correctly
Hello, I'm having some trouble autofilling some referenced cells from another sheet. I would like to have the column letter continue its sequence but I want the row number to stay the same (ex. a11, b11, c11, etc.). I have inserted a $ symbol before the number so it stays the same, but the letter doesn't continue its sequence even without the $ symbol in front of it. Here are a couple of screen shots to hopefully help with this problem:
Here is what my formula looks like before I drag it down to autofill it.
Here is what it looks like after I drag it down:
The formula just stays the same for each cell. The letter doesn't change and the number stays the same (=TPT!TX$11). I would like it to continue like this: (=TPT!TU$11, =TPT!TV$11, =TPT!TW$11, etc.). I can manually change each cell, but it can be very tedious. Any help with this would be greatly appreciated. Thanks!
=INDEX(TPT!$11:$11,1,541+ROW(A1)-ROW($A$1))
or
=INDIRECT(ADDRESS(11,541+ROW(A1)-ROW($A$1),,,"TPT"))
These formulas return the intended result in my sample sheet if i correctly understand what you want to do.
- Thank you OliverScheurich! I tried the first formula that you listed here and it seems to work just fine. I had to adjust the 541 number to 544 but I couldn't be happier. I'm not familiar with using this type of formula so I'll research it a little bit to see what it's actually doing but in the mean time it will save me a lot of time.
Hi, as you are filling down the column references do not move! You have fixed row 11 with the dollar symbol, which makes row 11 to stay in the reference as you fill downwards. Column TX is naturally staying in the same column as you fill, because you are filling downwards.
In order to move the reference TX to TU, TV, etc; you should be filling to the right.
I think that you might want a TRANSPOSE function. For instance, =TRANSPOSE(TX11:TW11)
