Select how many cells should be avaiable

[cinox]

Hi,

I want to have a cell where i write in a number, example: 10.
And then i have cell A1:A10 available for editing. And if i write 5 then i have A1:A5 available for editing.

How is that possible?

 

[Jon von der Heyden]

Welcome to the board.

One way might be to use data validation. For example, say the cell with the count in is C1.

Select A1.
Invoke Data Validation
Settings (tab) Allow: Custom
Formula: =ROW(A1)<=$C$1
Click OK

Now copy A1.
Select the range in column A that you want this to apply to (e.g. A1:A100).
Paste Special > Validation

Inputting 10 will only allow A1:A10 to be edited.

 

[cinox]

Thanks for your early reply,

i dont understand what you mean with
"Invoke Data Validation
Settings (tab) Allow: Custom"
I cant find this in my excel sheet.

Thanks

Welcome to the board.

One way might be to use data validation. For example, say the cell with the count in is C1.

Select A1.
Invoke Data Validation
Settings (tab) Allow: Custom
Formula: =ROW(A1)<=$C$1
Click OK

Now copy A1.
Select the range in column A that you want this to apply to (e.g. A1:A100).
Paste Special > Validation

Inputting 10 will only allow A1:A10 to be edited.

 

[Jon von der Heyden]

In Excel 97-2003, on the worksheet menu go Data > Validation.

In 2007-2010, on the ribbon go Data > Data Validation.

The 1st tab of the dialog is called 'Settings'.
In that tab choose 'Custom' from the 'Allow' drop-down.
Then, in the 'Formula' text box enter the formula that I provided.

Hope this helps.

 

[cinox]

Hmm... doesent seem to work!
when i paste =ROW(A1)<=$C$1 in A1 afterwards i only get "False in the colum afterwards

In Excel 97-2003, on the worksheet menu go Data > Validation.

In 2007-2010, on the ribbon go Data > Data Validation.

The 1st tab of the dialog is called 'Settings'.
In that tab choose 'Custom' from the 'Allow' drop-down.
Then, in the 'Formula' text box enter the formula that I provided.

Hope this helps.