Fill in the formula horizontally but increment the cell references vertically

Navigation
1

I need to fill a table with horizontally referring to values that are vertical of another table

In the horizontal table I'm doing the following 

='Tab 3x3'!G10

But when I drag to the side it increments the column, not the cell. It would need him to continue G11, G12 .. and so on.

I've tried to anchor ($) in every way but it's no use. I also tried using TRANSPOR that even worked but it was a mess, I would need the same reference as the above formula. I have also researched and found a staff using Offset, but I could not understand it very well.

Any easy way to do this?

    
asked by anonymous 29.08.2017 / 16:01

1 answer

1

You can use the TRANSPOR function that has a little secret to work with.

On the following data:

  • Selectwhereyouwantthedatatobetransposed(linked)andenterthefollowingformula:

    • =TRANSPOR(A1:A4)

      

    Selectthenumberofcellsaccordingtotheiroriginorthequantityyouwanttotranspose.

        

    ThearraywherethedataisinmyexampleisA1:A4,wherethesourcedataisaccordingtothesampleimage.

  • Press CTRL + SHIFT + ENTER

    •   

    ItisimportanttopressCTRL+SHIFT+ENTERandnotonlyENTER.

        

    Notethatthedataisbound,changingthesource(A1:A4inmyexample)willalsochangewhereitwastransposed.

    IhopeIhavehelped!

  • If you need the complete reference as quoted in the question instead of the data, you can have the reference (address) of the following cell:

    • =CÉL("endereço";ÍNDICE(Plan1!$A$1:$A$4;CORRESP(C1;Plan1!$A$1:$A$4)))

    Notice that you have the formula CORRESP that searches the transposed cell for the source address, and the reference Plan1!$A$1:$A$4 is my source array.

      

    The formula described in item 3 will give the exact source reference even though the source data is in another tab / spreadsheet.

    I hope I have helped!

        
    29.08.2017 / 20:22
    date comparison for new fields dynamically inserted with jquery How to place event click on graph DonutChart NVD3?