Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
dynamic transpose
Is it possible to do a dynamic reference for a transpose?
I'd like to do the transpose of a 12 cell horizontal reference into vertical, but I need the tranpose function to lookup the line of the 12 cells by finding a value in a vertical list. In other words, the value that drives it all is in a list. The 12 cells are to the right of the found cell. So the vertical block of 12 cells (which would be the transposed values), would have to do the following: find the lookup value grab the 12 horizontal cells that start about 10 cells to the right of the found value transpose those 12 cells into vertical. Thx for any help. -- Boris |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
dynamic transpose
"BorisS" skrev i en meddelelse
... Is it possible to do a dynamic reference for a transpose? I'd like to do the transpose of a 12 cell horizontal reference into vertical, but I need the tranpose function to lookup the line of the 12 cells by finding a value in a vertical list. In other words, the value that drives it all is in a list. The 12 cells are to the right of the found cell. So the vertical block of 12 cells (which would be the transposed values), would have to do the following: find the lookup value grab the 12 horizontal cells that start about 10 cells to the right of the found value transpose those 12 cells into vertical. Thx for any help. -- Boris Hi Boris Here's one way to do it. Assuming the lookup value in A2, the lookup list in C2:C10 and the 12-cells block in M2:X10 1. Select e.g. D13:D24 2. While selected enter this array formula =TRANSPOSE(OFFSET(C2,MATCH(A2,C2:C10,0)-1,10,1,12)) The formula must be finished with <Shift<Ctrl<Enter, also if edited later. If done correctly, Excel will display the formula in the formula bar enclosed in braces { }. Don't enter these braces yourself. Enter various numbers found in C2:C10 in A2 and D13:D24 will display the matching 12-cells range. -- Best regards Leo Heuser Followup to newsgroup only please. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
I WANT TO TRANSPOSE LINKS, AS WE TRANSPOSE VALUES | Excel Worksheet Functions | |||
using LOOKUP instead of IF on dynamic row | Excel Worksheet Functions | |||
Can't create dynamic charts | Charts and Charting in Excel | |||
Dynamic charting problems (events) | Charts and Charting in Excel |