| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Index to Transpose, Update Reference Cells as you Drag Down
I have this in cell L9.
=INDEX(Sheet1!$F$36:$F$51,COLUMN()-11) It works great when I drag across, transposing column data on another sheet into a row. But how do I get it to work so when I drag the formula down to cell L10, that it changes to this: =INDEX(Sheet1!$G$36:$G$51,COLUMN()-11) Thanks. And separately, I don't really understand why the -11 works. I viewed Excel help and didn't understand. I found the -11 works for me by trial and error. Any more intuitive explanation of choosing the right number like -11 would be great. SteveC |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Index to Transpose, Update Reference Cells as you Drag Down
I have this in cell L9.
=INDEX(Sheet1!$F$36:$F$51,COLUMN()-11) And separately, I don't really understand why the -11 works. The reason -11 works is because you have the formula entered in cell L9 and column L is column number 12. So: =INDEX(Sheet1!$F$36:$F$51,COLUMN()-11) = =INDEX(Sheet1!$F$36:$F$51,12-11) = =INDEX(Sheet1!$F$36:$F$51,1) = =F36 As you copy across the COLUMN() increments by 1 and the result of the subtraction also increments by 1. So, if you want to transpose the entire range, I'll assume it's F36:H51: =INDEX(Sheet1!$F$36:$H$51,COLUMNS($A:A),ROWS($1:1) ) Biff "SteveC" wrote in message ... I have this in cell L9. =INDEX(Sheet1!$F$36:$F$51,COLUMN()-11) It works great when I drag across, transposing column data on another sheet into a row. But how do I get it to work so when I drag the formula down to cell L10, that it changes to this: =INDEX(Sheet1!$G$36:$G$51,COLUMN()-11) Thanks. And separately, I don't really understand why the -11 works. I viewed Excel help and didn't understand. I found the -11 works for me by trial and error. Any more intuitive explanation of choosing the right number like -11 would be great. SteveC |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Index to Transpose, Update Reference Cells as you Drag D
Great! Extremely useful formula. Thanks very much for the explanation too.
"Biff" wrote: I have this in cell L9. =INDEX(Sheet1!$F$36:$F$51,COLUMN()-11) And separately, I don't really understand why the -11 works. The reason -11 works is because you have the formula entered in cell L9 and column L is column number 12. So: =INDEX(Sheet1!$F$36:$F$51,COLUMN()-11) = =INDEX(Sheet1!$F$36:$F$51,12-11) = =INDEX(Sheet1!$F$36:$F$51,1) = =F36 As you copy across the COLUMN() increments by 1 and the result of the subtraction also increments by 1. So, if you want to transpose the entire range, I'll assume it's F36:H51: =INDEX(Sheet1!$F$36:$H$51,COLUMNS($A:A),ROWS($1:1) ) Biff "SteveC" wrote in message ... I have this in cell L9. =INDEX(Sheet1!$F$36:$F$51,COLUMN()-11) It works great when I drag across, transposing column data on another sheet into a row. But how do I get it to work so when I drag the formula down to cell L10, that it changes to this: =INDEX(Sheet1!$G$36:$G$51,COLUMN()-11) Thanks. And separately, I don't really understand why the -11 works. I viewed Excel help and didn't understand. I found the -11 works for me by trial and error. Any more intuitive explanation of choosing the right number like -11 would be great. SteveC |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Index to Transpose, Update Reference Cells as you Drag D
You're welcome. Thanks for the feedback!
Biff "SteveC" wrote in message ... Great! Extremely useful formula. Thanks very much for the explanation too. "Biff" wrote: I have this in cell L9. =INDEX(Sheet1!$F$36:$F$51,COLUMN()-11) And separately, I don't really understand why the -11 works. The reason -11 works is because you have the formula entered in cell L9 and column L is column number 12. So: =INDEX(Sheet1!$F$36:$F$51,COLUMN()-11) = =INDEX(Sheet1!$F$36:$F$51,12-11) = =INDEX(Sheet1!$F$36:$F$51,1) = =F36 As you copy across the COLUMN() increments by 1 and the result of the subtraction also increments by 1. So, if you want to transpose the entire range, I'll assume it's F36:H51: =INDEX(Sheet1!$F$36:$H$51,COLUMNS($A:A),ROWS($1:1) ) Biff "SteveC" wrote in message ... I have this in cell L9. =INDEX(Sheet1!$F$36:$F$51,COLUMN()-11) It works great when I drag across, transposing column data on another sheet into a row. But how do I get it to work so when I drag the formula down to cell L10, that it changes to this: =INDEX(Sheet1!$G$36:$G$51,COLUMN()-11) Thanks. And separately, I don't really understand why the -11 works. I viewed Excel help and didn't understand. I found the -11 works for me by trial and error. Any more intuitive explanation of choosing the right number like -11 would be great. SteveC |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell reference update when copied and pasted | Excel Worksheet Functions | |||
| Auto update to "tab" in a reference link | Excel Worksheet Functions | |||
| Excel should be able to map which cells reference each other. | Excel Worksheet Functions | |||
| Populate multiple cells using the same reference | Excel Worksheet Functions | |||
| Removing $$ Reference in cells | Excel Worksheet Functions | |||
Linear Mode
