Match index problems

Hi
Can anyone help with a match index problem I am facing?

On the attached I have a tab COST DB where I would like to record expenditure by category and item using drop down so when I add a new row I get a list of categories to choose from (which seems to be working) and in the Item column i should get the relevant item drop down options which doesn't seem to be working?

Can anyone explain where I am going wrong

Many Thanks

Dave

Hi, I'm unable to look into this due to your named ranges referring to a sharepoint site.

Do these actually display as #REF in your file?

If so that's the start point for troubleshooting.

Thanks for your replies - its something I had working a few years ago but I had copied the formula from an online doc. How would I correct the name manager there are three items in their payment type, region column and region start which all read #REF!

You need to update the Named Range providing a correct link and cell location to the list, or create a new list in this file and refer to that.

Hi
Im not sure if Ive done this correctly, I have removed all the contents in the name manager and created three new ones

Ok so this if you Data Validation statement to produce the dropdown in Item column

=OFFSET(RegionStart,MATCH(A2,RegionColumn,0)-1,1,COUNTIF(RegionColumn,A2),1)

You now do not have named ranges for RegionStart or RegionColumn so it can't create your list.

Typically your RegionStart will be the top of the list and the RegionColumn would be the entire column.

So you your list was in Column A with a header the region start would be from A2 and the region column would be A:A.

But I am having to make assumptions here and it's possible this has been set up to be more dynamic. (So Region Column may be created by the Region Start which is found by a condition)

however Data validation doesn't like two dynamic ranges so you will need to fix your second set of named ranges which I have done in the example attached. There may be a better solution out there though - such as AVK's input re updating the name manager

Thanks that seems to work great, could I ask if I want to add further items to the categories where and how do I do that?

Originally Posted by faodavid

Thanks that seems to work great, could I ask if I want to add further items to the categories where and how do I do that?

So because the dynamic range won't work, you need to add the item under the relevant list in sheet("VALIDATION") then in Name Manager expand the range for that list.

If you give it a try now and let me know how you get on I can provide step by step notes if you need them

That works fine - many thanks

You're welcome, thanks for the Rep, speak soon.