Dynamic named range and indirect function

**m4rkybob** · 12-21-2023, 01:50 PM

I cannot seem to find an answer to this anywhere so hoping someone can help.

I have an excel sheet for planning my workouts. In my sheet named Data, I have 10 lists of different exercises for each muscle group. Then on my week 1 sheet I have a section where you can select the muscle group from a drop-down using the data validation =Data!$B$5:$K$5 Then the cell next to it is where you select the exercise so I use the data validation of =INDIRECT(A1)

The issue is I want to be able to add to the exercises. I have tried using the following formula for the named range

=OFFSET(Data!$F$6,0,0,COUNTA(Data!$F$6:$F$100),0)

I know from looking on Google that Indirect and Offset or Index do not work well together.

Any ideas for a workaround?

**deadlyduck** · 12-21-2023, 02:19 PM

Could you upload a mock-up of what you've got (showing what you want to be able to do also) so that it's easier to visualise a solution.

EDIT- one thing that may be relevant- the last argument of the OFFSET function must be a positive number- probably 1 in the example given.

Dynamic named range and indirect function

LinkBack

Thread Tools

Rate This Thread

Display

Dynamic named range and indirect function

Re: Dynamic named range and indirect function

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] XLOOKUP using INDIRECT with named range to search across multiple dynamic sheets

[SOLVED] Dynamic Named Range formula with Indirect and Index for Chart

[SOLVED] Alternative to INDIRECT with dynamic named range

[SOLVED] INDIRECT cannot be used in a dynamic named range OFFSET

[SOLVED] Is there a way to work around the INDIRECT function for dynamic named ranges?

Data Validation Indirect Function linked to Dynamic Named Range

Using INDIRECT to put a Dynamic Named Range in a chart Data Source

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions