Protected sheet using List

[Kurupt55]

I have a basic spreadsheet with a named range in a list.
what i want to do is protect the sheet but enable the user to add data to the list, i know how to enable users to edit ranges and what not but the issue is when i protect the sheet the list does not expand to have an extra row to input aditional data.
I have tried enabling all the options under tools > Protection > Protect Sheet but nothing seems to work
is there a way to do this?

Impossible is Nothing

 

[Fenrirshowl]

Just to restate the problem, you have a named range, "inputlist" say, in A1:A5. You want users to be able to add entries to A6, A7 etc and have "inputlist" be redefined to be A1:A6, then A1:A7 etc?

Obviously in this scenario A6 and below canot be locked when you protect the worksheet.

If this is case, set up a cell that counts the number of entries in your list, i.e. make cell Z1 contain the formula =counta(A1:A200) with 200 being the maximum list length you desire.

Currently this would give you an answer of 5 in my example.

Next, define your range. Do this using Insert menu / Name / Define. Enter the name of the range, "inputlist" at the top, then in refers to type:
=INDIRECT("Sheet1!A1:A"&Sheet1!$Z$1)

Then add an entry in A6 and you should find the range "inputlist" is extended.

Fen

 

[xlbo]

Can do it all in 1 formula:

for the range defintition, use:

=OFFSET(A1,,,counta(A:A),1)



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question