Power Automate - How to create Excel table dynamically (Excel Formula)?
Вставка
- Опубліковано 15 вер 2024
- 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)
Dear Ajith, thank you so much for this great video. Very very helpful.
Hi Paranay
You are most welcome. Thanks a lot for the support.
Regards
Aj
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.
Great tip! Thanks
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??
Hi
Default is Sheet1 so you could hardcode that value.
What if you don’t know the number of columns as well , how to get no of columns dynamically?
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...
Hi
Thanks for the support.
Hey Thank you very much!!! I didn't know that we can use Excel formulas.
Happy to help!
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.
What if we don’t know the no of columns as well in the last part of offset formula? How to get it dynamically?
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA(1:$1))
really good! thanks for sharing.
Glad you liked it!
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 !
Hi
Thanks so much for the support.
I was away for few weeks. Do you still need help in this please?
Thanks
Hi @@abm4975 , Yes it will great if you can share the solution :)
My table is working but an extra blank row at the bottom is also being added to the table. Any idea why? thanks
Thanks for your question. Never come across that. Try the below formula and see
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA(1:$1))
Great! Works for me!
Thanks a lot
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA(1:$1))
This worked better for me.
Thanks for letting me know Andrew.
I have tried this, however, the table stops short from capturing all the rows... have you seen this before? For example the table captures "A1:K1023" however its supposed to be "A1:K1058". thanks in advance.
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??
Hi
I have made a tutorial video based on your request. Please have a look and comment.
ua-cam.com/video/urSnaB6G_U4/v-deo.html
Thanks
@@abm4975 thanks you !! It Works perfect..
Thanks a lot
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".
Hi
I need to see your flow for this? I could offer a screenshare. Check my channel about section. You can see my email address here. Connect me via Teams. I will have a look into this.
Thanks
BadRequest Error
Nop does not work!!!
Hi
I need to see your flow. I could offer a screen share help. Check my channel contact and send me an email.
Thanks
Great video, would you mind posting the formula in description? Thank you
Hi Eamonn
Its already in the description. Please click show more.
Formula used to find the table range
=OFFSET(Sheet2!A1,0,0,SUBTOTAL(103,Sheet2!$A:$A),3)
Thanks
Is there a limit to this formula, because it only turned 3610 into a table, the rest below were not found by the formula.
Hi
Check the List Items action step settings > Pagination. Set to on and set a higher number here.
@@abm4975 oh ok, that might be the case, thanks for the help
It's a simple spell but quite unbreakable....
Thanks