"Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"

[vaxo]

Hello Friends,

"I am seeking assistance with an Excel formula that involves transposing data from a specific range in one sheet to another. Currently, I have the following formula in cell B2: =TRANSPOSE(იჯარები_List!B3:H3). However, I need this formula to dynamically adjust as I move down the rows, changing from =TRANSPOSE(იჯარები_List!B3:H3) in B2 to =TRANSPOSE(იჯარები_List!B4:H4) in B2, and so on for each subsequent row.

Could you please provide guidance on how to achieve this dynamic formula adjustment in Excel? Your assistance would be greatly appreciated."

[HansV]

That won't work. The formula in B2 =TRANSPOSE(იჯარები_List!B3:H3) spills to B2:B8, so if you enter a formula in B3, you'll get a #SPILL! error.

[HansV]

Did you want to enter the second formula in C2 instead?

[vaxo]

The second formula would be entered also in B2 but in different sheet. With the spilled resutl =TRANSPOSE(იჯარები_List!B4:H4).

[HansV]

I don't think there's a way to have a formula do that automatically. It might be done through VBA.
Will the sheets where you want the formula be next to each other?
Will the be the left-most sheets in the workbook, or the right-most sheets, or ...?

[vaxo]

Sheets are identical, Sheets are right most, I don't need it to be fully acutomaticall but with coping cells from one sheet to another, could it be done automatically, without mannually changing cell references?

[HansV]

No, that is not possible.

[vaxo]

I have new office 365 is it possible with power automate function?

[HansV]

I don't think so, but I'm not an expert on that.

[HansV]

Copy/paste will not work. But as I mentioned in a previous reply, you might use a VBA macro to create the formulas.

[vaxo]

Ok please help me with VBA solution. The range is: =იჯარები_List!$A$2:$H$53 and all tables has this ranges: ='თოდრია ქ.'!$A$2:$B$8 I need every foruma to be in b2 of every sheet.

[HansV]

i don't understand what you mean by

The range is: =იჯარები_List!$A$2:$H$53 and all tables has this ranges: ='თოდრია ქ.'!$A$2:$B$8

What exactly do you want to do?

[vaxo]

Ok, I would upload sample workbook with explanation.

[HansV]

If you have a sample workbook, pleas attach it.

[vaxo]

I attached file.

[HansV]

OK, thanks, that gives me a better idea of what you want.
Does this work in the version that you have?

=TRANSPOSE(INDEX(იჯარები_List!B:H,MATCH(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,100),იჯარები_List!B:B,0),0))

[vaxo]

Yes, it works, and it is dependent of sheet name, if the sheet name changes the formula give N/A, Could we accomplish the same so that the formula only was dependent on: იჯარები_List and not on sheet names?

[vaxo]

Because if I accidentally changed sheet name (which as I know does not have undo function it would be difficult to rectify it. Morevoere Trace Precedents gives me the full table area...


P.S But this formula would be also very useful in some cases..

[HansV]

But how do we know which data to return on sheet "aaaa" if "aaaa" is not the name of the Leas Agreemant on the იჯარები_List sheet?

[vaxo]

The Sheets are arranged in order, according to the "იჯარები_List" sequence.