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

 

[jasonb75]

The syntax of the formula is correct I don't get the same error as you are seeing, just a #REF! error because I don't have the named range in my workbook.

The only reason I can think of for that error would be if you have european language which require semicolons in place of commas.

 

[S_Asks]

Without the Indirect function it works fine on the same sheet, but in this case I have to refer to a another sheet (Which is why I am using Indirect) So I think the problem is in the Indirect part.... The commas here work fine in my excel, thanks for the suggestion though

 

[jasonb75]

Looking at it again, I can see from the way that you have written the formula that I misread part of it to start with and thought that 'Analyser list' was a named range referring to multiple sheets.

Are you looking to reference the sheet name as a variable in a cell once the formula is working? At the moment, I can see no reason for the INDIRECT function to be used. Regardless of that, I've just set up a test sheet with the same name that you are using, added a bit of data to it and the formula returns results as expected. I can't see any reason why you would see the error that you have shown other than the one that I already mentioned, or a typo in the formula that is not in the one that you posted (extra characters at the end that didn't get copied?).

 

[S_Asks]

Well what I have read online you need to use the indirect function if you want to use a sheet reference in a formula... So that is the reason why I am using indirect (Tried to reference to another sheet without indirect and that wasn't working)

The formula I pasted is the exact same as the one I am using in my excel sheet...

I solved the problem!
I just copied the same code from this chat and now it's working, it's kinda weird but the textstyle changed in my formula bar?
Maybe the problem was cause by me copying it from teams right into excel... I have NO IDEA why this causes an error for excel. Oh well the problem is solved thanks for the help Jason!

 

[jasonb75]

I have read online you need to use the indirect function if you want to use a sheet reference in a formula.

Don't believe everything you read online, most things are written by people like me that think we know what we're doing but don't really have a clue

You only need to use INDIRECT if you want to take the sheet name from a different cell.
For example, if you had say Sheet1, Sheet2, and Sheet3 with identical layouts but different data and you wanted to choose which sheet the formula should look at by entering the name of the sheet into A1 of the sheet with the formula in.

If it's simply looking at a different sheet then you only need

=OFFSET('Analyser list'!$A$1,1,MATCH($A$1,'Analyser list'!$A$1:$D$1)-1,COUNTA(OFFSET('Analyser list'!$A$1,1,MATCH($A$1,'Analyser list'!$A$1:'Analyser list'!$D$1,0)-1,20,1)),1)

Although I suspect that further improvements could be made.

Are you just trying to return the contents of the used range to a cluster of empty cells, or is there going to be more to the formula?

 

[S_Asks]

The end goal is to have a dropdown (right one) correspondent to another dropdown (the left one)

And when clicked on the right dropdown I don't want to see empty cells so thats why it's sucha long code.

So if you choose GC on the left dropdown I want to see all the items under GC in the right dropdown etc.

But I see now that the character limit of data validation is 256 characters... (Which I use on the right dropdown) so I have to find a solution for that aswell.

 

[jasonb75]

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.

 

[S_Asks]

Thanks for the help Jason! I will give this a go tomorrow. I am done with work for today
I will come back tomorrow with the feedback of how it goes.

Have a nice day

 

[jasonb75]

You're wecome

One tip that I forgot to add, when it comes to editing the formulas for the names ranges it is best to do them in a cell (or anywhere other than the data validation window).
If you try to use the cursor keys to move through the characters in the formula then it actually moves the selected cell on the sheet and makes a mess of the formula (it happens to me almost every time).

I've got a few things planned for tomorrow so may not be on the forum as much as today but will check when I can to see how you're getting on with this.