Named ranges and drop-down in-cell lists

[nickE55]

Hi,

I am a newbie and a volunteer Treasurer for a small non-profit Trust in the UK.

I am trying to modify a spreadsheet for use by our scheme manager to enter income and expenditure each month, for it to be automatically reconciled with our bank account.

The problem I have is that I cannot edit the ranges named "Payment" and "Receipt" which are used in drop-down in column I in the Bank statement - Apr sheet because, when I use Formula and Name Manager, the cell ranges used for them (=Lists!$D$2:$D$8 for one and =Lists!$B$2:$B$8 for the other) do not make sense to me.

I thought there might be a hidden sheet with the two lists but that does not seem to be so.

Although there are 14 worksheets in the workbook, you only need to consider two of them:

1. Bank statement - Apr is one of the source worksheets and it feeds into the April column in

2. SORP, which, in cells B10 to B21 contains a copy of the the two named ranges but not the original, because if you edit the text in the B column, it does not alter the contents shown by Name Manager.

I also tried editing the cell ranges for Payment and Receipt to the B column in the SORP sheet but that stopped SORP importing the data from Bank Statement - Apr.

Any help you can give would be greatly appreciated.

nickE55

[#2]

There is a hidden Lists sheet in this workbook (I can see it from the VBA editor), but the workbook's structure is protected with a password which makes it impossible to add or remove sheets or reorder them without that password (you can see this from Review => Protect Workbook).

Ideally you will be able to get that password and unprotect the sheet so you can unhide the Lists tab and make the changes directly.

Without that password the most you could do is add some new cells on an existing sheet with the options you need for those dropdowns, and then amend the validation rules to look at your new ranges instead.

[#3]

Thank you Savia, I am grateful to you.

[#4]

@nickE55 No problem - hopefully that helps you fix your sheet.  Don't forget you can mark an answer as the "best answer" to help future readers who find this thread who have similar problems.