Power Automate - How to create Excel table dynamically (Excel Formula)?

This video explains how to create an excel table dynamically?

Formula used to find the table range

=OFFSET(Sheet2!A1,0,0,SUBTOTAL(103,Sheet2!$A:$A),3)

[yeskumar]

Thank you so much for the video. Requesting you to please explain more about =OFFSET(Sheet2!A1, 0, 0, SUBTOTAL(103, Sheet2!$A:$A), 3). I'm bit confused.

[pranayjindal]

Dear Ajith, thank you so much for this great video. Very very helpful.

[s.p.]

Dear Ajith - Thank you very much indeed for your great video - this was really really helpful. I have applied your method and it works!! You are an amazing person...

[navarrocloud]

Great video. During my testing I found out that you don't need to specify the Sheet name if the workbook has only one worksheet. This simplifies the formula.
Also, the number at the end of OFFSET represents the number of columns, in my case I had 35 columns so I used that number instead. Cheers.

[csmtiltm]

Hey Thank you very much!!! I didn't know that we can use Excel formulas.

[mog]

Great video, would you mind posting the formula in description? Thank you

[SaurabhSharma-ovox]

Hi Ajith ! Great solution!

I was wondering if we can have a dynamic names for tables also . I have a requirement where excel table is updated frequently but there seems to be an issue where table can’t be override with same name (as here you have given constant name- Emp )

Thanks in advance for your help !

[sparshraikar]

What if we don’t know the no of columns as well in the last part of offset formula? How to get it dynamically?

[fernandosjrp]

Great vídeo..
Please dou you know How to get a New sheet without have to select a New sheet?
For exemplo, if I Just save in Onedrive my New sheet the flow already select this one..and start ALL flow??

[epidot]

My table is working but an extra blank row at the bottom is also being added to the table. Any idea why? thanks

[mitch]

Hola, antes me funcionaba ese tipo de forumlas con el Power Automate, pero de un momento a otro dejo de funcionar y me sale un mensaje de "Bad Request". Si le pongo un rango fijo, funciona, sino "No funciona".

[josebravo]

It's a simple spell but quite unbreakable....

[abyalkod]

Is there a limit to this formula, because it only turned 3610 into a table, the rest below were not found by the formula.

[prod_adrian]

BadRequest Error
Nop does not work!!!

[AndrewKing]

=$A$1:INDEX($1:$1048576, COUNTA($A:$A), COUNTA(1:$1))
This worked better for me.