Excel Programming

L5

Hi, Ive been asked to write a database in Excel, and to include these features .Im totally new to Excel, and was wondering can any of these be done without writing macros ?
Thanks for the help.


1.when filling out rows of cells if there is an empty cell in one column, there will be some method of warning to fill it in when trying to save the spreadsheet.

2.Locking a sheet against copy and paste.

3.Automatic selection of cells, i.e the spreadsheet will recognise what is entered in one cell and then automatically select the following cells in recognition of what has already been selected.

4.When using validation in excel, a drop down menu occurs. allows to type the first letter or number and the drop down menu will take you to the section of the menu that corresponds with letter or number entered in the cell.

Hobbes

Why use a spreadsheet to create a database?

L5

I dont know for certain, its not exactly a database, its hard to explain, but it has to be done in Excel

Victor

Originally posted by L5
2.Locking a sheet against copy and paste.

There is an option somewhere to "protect" (look it up in the help section) cells. I'm not sure, but this may stop any new data being entered at all until unprotected.

The rest of your questions seem a bit too vague to be answerable.

dynamic.ie

Ok,

I'll take a stab at helping you out but may need some more info.....



1. in a new column put in the following =+IF(A1="","*","") This has the effect of checking the cell a1 for anything and if it is blank, it will display * in the column you've entered. You'll need to copy this down the column for each row you want to check. If you move the mouse over the bottom right corner of the cell, click and then drag down the rows, excel will recreate the code and automatically increment the a1,a2,a2,etc. You can change the * to whatever you want. Just make sure it stays inside the " ". The expression above in english is: if cell A1 equals nothing then put in *, otherwise put in nothing. You could also use: =+IF(A1="","Fill Me In","Complete") or whatever you want.



2. ok, not sure if you can lock against copy and paste. Do you mean locking so that someone can't copy and paste out of it? I'm not sure if that can be done but if you want to lock certain parts of a sheet so that others can edit the values, do this.

- Right click on your toolbar menu in a blank spot so it brings up all the different toolbars you can enable/disable.
- Select customise from the menu
- Select commands from the tabs
- Go to format on the list of categories
- Shuffle down the list to very near the bottom and you will see lock cell with a little padlock icon.
- click and drag the icon up beside your cut, copy, paste icons
- close the customise box

Now, when you are editing your cells, you can highlight them and select which ones are locked and which are not. Select all of them by pressing CTRL + A. You will see that the padlock is pressed. This means all cells will be locked if you lock the sheet. Highlight any cells you want to let users access and deselect the padlock.

Now, goto Tools, Protection, Protect Sheet. You can enter a password here if you want. Don't know if that's exactly what you want to do here but maybe....



3. Think you might need to explain a bit more here but here's something that might do what you are looking for. Highlight a group of cells, e.g. A1 to B3. Then above the A column header there should be a white box with A1 in it (if you selected A1 first, that is while highlighting the cells). Now click there and type testcells. Now click anywhere on the sheet. You can click the dropdown that you just entered testcells into and you will see testcells in the list. Click on it and it will automatically select the cells you had originally saved. May do what you are looking for...



4. Didn't exactly see a question here. What are you trying to do?



Hope these help. Let me know how u get on.

Cheeurs,

Dave

L5

Ok thanks for the help, ill try them out and report back!