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]

That goes partway to explaining the original problem, if you were trying to use the indirect formula as the validation source then it would have been rejected, although with a different error message than the one you have originally shown.

Anyway, that's no longer important. Trying to make things easier for you. I'll assume that "GC" in the bottom image is in 'Analyser list'!A1 and base the formulas on that.

First create a named range called GC that refers to

='Analyser list'!$A$2:INDEX('Analyser list'!$A:$A,MATCH("zzz",'Analyser list'!$A:$A))

Create 3 more named ranges called TOC, PH_meter, and Conductivity. Each referring to the same formula as above, changing the column in each formula so that it goes with the respective name. (Note that the third one uses an underscore instead of a dash).

Now, for the validation list in B1 you can use A1 as a source with the formula

=INDIRECT(SUBSTITUTE(A1,"-","_"))

Hopefully with that all will work as required.

The problem here is that I need the data on the sheet Analyser list. I don't want the the data to be on the same sheet as my dropdowns.
So the sheet with the dropdowns should only have the dropdowns and nothing else...

I would also like the data to be flexible, so when I want to add another list in the Analyser list, it should be as easy as adding the list to it without writing a formula.
So my colleagues can easy use this sheet without much knowledge of excel.

I hope this make some sense.

Could be I am misunderstanding your solution, but from my understanding you are creating new lists on the dropdown sheet and using these to compare it to the first dropdown?

Well thanks for your help!

But I have another question you don't think it will be possible to insert the following code:
=OFFSET(INDIRECT("'"&P2&"'!"&"A1"),1,MATCH($A$1,INDIRECT("'"&P2&"'!"&"A1"):INDIRECT("'"&P2&"'!"&"D1"))-1,COUNTA(OFFSET(INDIRECT("'"&P2&"'!"&"A1"),1,MATCH($A$1,INDIRECT("'"&P2&"'!"&"A1"):INDIRECT("'"&P2&"'!"&"D1"),0)-1,20,1)),1)

into the validation of the dropdown? I get the following error now:

Which I find hard to solve with this formula... to my understanding it's not reading the formula as list which cause this error, is there a way to get around it or do I have to go with another approach?

 

[jasonb75]

Could be I am misunderstanding your solution, but from my understanding you are creating new lists on the dropdown sheet and using these to compare it to the first dropdown?

It can be easy to misunderstand these things if you haven't seen them before. The lists can all stay on the analyser list sheet, no need to create new ones as such. The named ranges are a workaround to the error that you have shown in your latest reply. You could possible use your formula, the one that I've suggested is just a simpler and more reliable method.

I can see from your reply that named ranges are a new thing to you so I'll try to walk you through it step by step (please bear with me, I can do these things a lot easier than I can explain them to others).

In Excel, press Ctrl and f3 together, you should see a box appear titled 'Name Manager' (or you can click the 'Name Manager' icon on the Formulas tab.

At the top of the box click 'New', you will see a smaller box with 4 options. Name, Scope, Comments, and Refers to: Comments are optional for your reference.
To create the list for the GC dropdown you will need to complete the boxes as below.

Name: GC
Scope: Workbook
Refers to: ='Analyser list'!$A$2:INDEX('Analyser list'!$A:$A,MATCH("zzz",'Analyser list'!$A:$A))

Click Ok, then click New again and repeat the 3 steps above for the other 3 columns.

Name: TOC
Scope: Workbook
Refers to: ='Analyser list'!$B$2:INDEX('Analyser list'!$B:$B,MATCH("zzz",'Analyser list'!$B:$B))

Name: PH_Meter
Scope: Workbook
Refers to: ='Analyser list'!$C$2:INDEX('Analyser list'!$C:$C,MATCH("zzz",'Analyser list'!$C:$C))

Name: Conductivity
Scope: Workbook
Refers to: ='Analyser list'!$D$2:INDEX('Analyser list'!$D:$D,MATCH("zzz",'Analyser list'!$D:$D))

Close the name manager and select the cell where the dropdown is going, then use this formula as the list source for the dropdown and you should be done.

=INDIRECT(SUBSTITUTE(A1,"-","_"))

Hopefully that all makes sense.

 

[S_Asks]

Thanks for the reply Jason!

I put the names into the name manager, this went fine.

But when I tried to put the formula =INDIRECT(SUBSTITUTE(A1,"-","_")) into the data validation of the dropdown I get the next error:

I watched a video online about indirect(subsitute) aswell and saw that there were also empty white spaces in the dropdown

^ is from the video

So... am I doing something wrong?
Is it possible not to have empty cells in the dropdown?


I really appreciate the help!

 

[jasonb75]

It looks as if you've done it correctly, (as per my poor guidance) I think that it is an error on my part, having done a quick test it is not working as expected, but neither is another simple method that should also work so I'm not sure exactly what is going wrong.

I'm not going to be able to look at it for the next couple of hours but will do it as soon as I am able to and post the corrected information for you.

 

[S_Asks]

No problem!
You think it is possible the way I am trying to solve my problem?
Otherwise I will have to try a more dirty method which will work for sure!

I have learned a lot already with your guidance so I am grateful

 

[jasonb75]

Going back to the error message that you had in post 13, "The source evaluates to an error, do you want to continue?", did you click yes and test the dropdown?

If you haven't, please try it and see what happens. I think that I have a problem with my excel installation as a lot of things don't seem to be working correctly at the moment.

I'm sure that I've used everything as I suggested previously and it has worked correctly.

 

[S_Asks]

Yes, I pressed enter.
The problem is the dropdown is not working at all then...
If I press the arrow down nothing happens, so yeah that what we are up against

 

[jasonb75]

At least we are both seeing the same thing then. For some reason, mine is returning errors with basic indirect functions that I'm sure should work correctly.

I'm going to dig around and find an old working version where I've done the same thing before, I'm positive that it should work although I could have missed a tiny but important detail somewhere.

 

[S_Asks]

The Excel formulas are quite unforgiving I have figured out haha,
I will be waiting for your reply

 

[jasonb75]

I have figured out what was going wrong, but not why. For some reason excel seems to have stopped allowing dynamic named ranges with INDIRECT. I'm not sure why, if it's just a problem that we're encountering, or if it's something that's been changed in the excel's core that is causing it and I haven't noticed it previously. (Perhaps something relating to dynamic arrays).

It's something I'm going to look into a bit more, I think I'll start a new thread on the topic later and see if others are encountering the same thing.

For now, this formula works as the validation source when uses with the named ranges that you set up for my earlier attempt.

=CHOOSE(MATCH(A1,'Analysis list'!$A$1:$D$1,0),GC,TOC,PH_Meter,Conductivity)