Fill the formula horizontally but increment the cell references vertically

Asked

Viewed 1,369 times

1

I need to fill a table with horizontal reference to values that are vertical from another table

In the horizontal table I am doing the following

='Tab 3x3'!G10

Only when I drag it to the side it increases the column and not the cell. I would need it to continue G11, G12.. and so on.

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

Some easy way to do it?

excel office-2013

  • Hello Gabriel, have you tried using the TRANSPOR the way down and out? I believe that if you want the formula as you mentioned, you will have to do it manually... or perhaps, if you explain better the later use of which it is necessary to have the formula as mentioned, I can try to help you with other formulas that will return what is needed. Maybe linking a CORRESP with ÍNDICE...

1 answer

1


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

In the following data:

inserir a descrição da imagem aqui

  1. Select where you want to have the data transposed (linked) and type the following formula:

=TRANSPOR(A1:A4)

Select the number of cells according to their origin or the amount you want to transpose.

The matrix where the data in my example is A1:A4, where the source data are as an example image.

  1. Press CTRL + SHIFT + ENTER

inserir a descrição da imagem aqui

It is important to press CTRL + SHIFT + ENTER and not only ENTER.

Note that the data is linked if you change the source (A1:A4 in my example) will also change where it was transposed.

I hope I’ve helped!

  1. If you need the complete reference as mentioned in the question instead of the data, you can have the reference (address) of the cofnorm cell follows:

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

inserir a descrição da imagem aqui

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

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

I hope I’ve helped!

Browser other questions tagged excel office-2013

You are not signed in. Login or sign up in order to post.