"Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
"Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
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."
"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."
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
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.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
Did you want to enter the second formula in C2 instead?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
The second formula would be entered also in B2 but in different sheet. With the spilled resutl =TRANSPOSE(იჯარები_List!B4:H4).
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
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 ...?
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 ...?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
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?
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
No, that is not possible.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
I have new office 365 is it possible with power automate function?
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
I don't think so, but I'm not an expert on that.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
Copy/paste will not work. But as I mentioned in a previous reply, you might use a VBA macro to create the formulas.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
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.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
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?
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?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
Ok, I would upload sample workbook with explanation.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
If you have a sample workbook, pleas attach it.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
I attached file.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
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))
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))
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
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?
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
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..
P.S But this formula would be also very useful in some cases..
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
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?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 432
- Joined: 23 Mar 2017, 19:51
Re: "Dynamic Excel Formula for Transposing Data Across Rows in Different Sheets"
The Sheets are arranged in order, according to the "იჯარები_List" sequence.