Answered

How to get Worksheet name using Power Automate flow and use it to create a table

(0) Share

Report

Posted on by CU06081659-0

134

I am using Power Automate Flow to convert an excel file to Table when it gets created on a SharePoint site. In the flow, I am using the below in 'Table range' so as to convert the entire data to a Table Range.

=OFFSET('Page 1'!A1,0,0,SUBTOTAL(103,'Page 1'!$A:$A),24)

My excel file would always have 1 sheet only. But the sheet name may change every time. The above formula only works when the Sheet Name is 'Page 1'. Kindly help me to build a Flow that I can use on any sheet name. That is instead of 'Page 1' it should be dynamic to consider the 1st or single sheet as a reference.

I am sharing the screenshot of the Flow for everybody's reference.

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (1)

Sort by

v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Shubhabrata ,

You can get worksheet name using below actions:

Best regards,

Was this reply helpful? Yes No
CU06081659-0 134 on at

Like (0)

Report
Copy link

Link copied!

Hi @v-jefferni ,

Thanks for your prompt reply. I tried using your method but getting the below error in 'Create table 2' step.

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Shubhabrata ,

Ah, I just meant to tell you to use the Name dynamic content inside your previous function:

=OFFSET('@items('Apply_to_each')?['Name']'!A1,0,0,SUBTOTAL(103,'Page 1'!$A:$A),24)

Best regards,

Was this reply helpful? Yes No
CU06081659-0 134 on at

Like (0)

Report
Copy link

Link copied!

Used the formula in 'Table range' but got the below error:

Was this reply helpful? Yes No
Verified answer

v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Shubhabrata ,

It would be like this:

=OFFSET('@{items('Apply_to_each')?['name']}'!A1,0,0,SUBTOTAL(103,'Page 1'!$A:$A),24)

Best regards,

Was this reply helpful? Yes No
CU06081659-0 134 on at

Like (0)

Report
Copy link

Link copied!

Yayyyyy....it worked. You are a magician.

Many thanks for your quick help. Really appreciate your patience for people like us who not are not an expert on this and trying to learn.

Was this reply helpful? Yes No
npandya98 2 on at

Like (0)

Report
Copy link

Link copied!

Offset function doesn't work in the new designer. Please help.

Was this reply helpful? Yes No