Offset with indirect and MATCH

[S_Asks]

Hi There,

I'm trying to use Indirect with Offset. This way trying to match 2 different values.

The formula I am using is as follows:
=OFFSET(INDIRECT("'"&"Analyser list"&"'!"&"A1"),1,MATCH($A$1,INDIRECT("'"&"Analyser list"&"'!"&"A1"):INDIRECT("'"&"Analyser list"&"'!"&"D1"))-1,COUNTA(OFFSET(INDIRECT("'"&"Analyser list"&"'!"&"A1"),1,MATCH($A$1,INDIRECT("'"&"Analyser list"&"'!"&"A1"):INDIRECT("'"&"Analyser list"&"'!"&"D1"),0)-1,20,1)),1)

unfortunately this results in the following error

I understand the error but I don't know how I can make the formula work.

Someone knows how to get this to work? I think the problem is with the Indirect part, but no Idea how to fix it

thanks for the help

 

[S_Asks]

The problem I get with this code is as followed:

 

[S_Asks]

I thinks this was the reason why I started using indirect aswell

 

[jasonb75]

That'll teach me to test properly
I had it working fine, but I had the dropdowns in the Analysis list sheet rather than going back and forth between 2 for testing.

Not sure why it's showing that message, it's definitely not right but I'm seeing the same thing when I move the dropdowns to another sheet. Previously, this only happened if you tried to use another workbook (file) as the source, multiple sheets in the same workbook all work together fine (or at least they did).

I'll give it some more thought, but to be honest I'm running out of ideas. I have asked whether others are encountering similar issues so hopefully we might get some more input from elsewhere.

Noting that you have only just joined the forum, I have assumed that excel 2016 as shown on your profile is correct?

 

[S_Asks]

Yes the version of my excel is correct (2016).

 

[jasonb75]

Ok, leave it with me and I'll see if I can find something that works that will be compatible with excel 2016.

Given the number of problems we have encountered with previously working methods it may take me a while to find anything.

 

[S_Asks]

I can Imagine. Excel is like programming but less flexible.
Thanks for the help again, I will see if I can find anything else to solve the problem.
I will let you know if I find something

 

[jasonb75]

Sorry it's taken me so long to follow up on this. For some reason I had forgotten about the error that you encountered with my last suggestion and thought that you had something that worked.

I've got something that works in my test sheet that sets up both validation lists using a total of 5 named ranges. They both resize dynamically if you add more rows / columns to the source list so you will not have any blank space at the bottom of the dropdowns.

I've done it with the source and dropdowns in separate sheets this time so as not to encounter the same error as the previous attempt. I haven't done a huge amount of testing but everything appears to be working correctly. One thing to note is that I've based it on text or alpha-numeric entries in the table, the part that removes the blanks at the end of the dropdowns will not see numeric entries unless they are formatted as text, or have at least one text entry after them in the list.

The named ranges are as follows, all have the scope set as Workbook.

Name: FirstChoice
Refers to: =OFFSET('Analyser list'!$A$1,,,,MATCH("zzz",'Analyser list'!$1:$1))

Name: FindIt
Refers to: =MATCH(INDIRECT("RC[-1]",0),FirstChoice,0)

Name: TheColumn
Refers to: =OFFSET('Analyser list'!$A$1,,FindIt-1,100,1)

Name: LastRow
Refers to: =MATCH("zzz",TheColumn)

Name: TheList
Refers to: =OFFSET('Analyser list'!$A$1,1,FindIt-1,LastRow-1,1)

Once these are set up, you can use these to create the validation lists.

A1 Source: =IF($B1="",FirstChoice,$A1)
B1 Source: =TheList

The formula for the A1 source will only allow the user to change the selection if B1 is empty. If a selection has been made from the list in B1 then it must be deleted in order to make a different choice in A1. This may not be strictly necessary but is a good way to prevent invalid combinations being selected.

Hopefully I haven't missed anything this time.

 

[S_Asks]

Hey Jason,

Thank you for the reply, I found out that I got Excel 365 and not 2016 (My bad)
But I tried the code but unfortunately I still get an error if I insert the code in the B1 cel as validation

Is there a tweak needed in the code to make it working in the validation in excel 365?

Excuse me again for giving you the wrong excel version.
And thanks again for taking your time to help me out here!

 

[jasonb75]

That error would most likely be because A1 it's currently empty. If you click OK on both boxes then it should work correctly afterwards, or you could make a selection from the dropdown in A1 before setting up the source for B1 which should allow it to go through without the error message.

365 is not a problem, anything like this will work fine with a newer version than it was meant for, problems only occur between versions when you use a new feature that is not compatible with an older version.

 

[S_Asks]

Hmm, I can't press okay and continue, it keeps giving that error

I think the problem is in TheList part

As you can see it puts quotations around the offset part, is there a way to fix this? or is this not a problem?