[rmccafferty]

I am used to doing this in Access but don't see how to do it in Excel. And, of course, nothing is easy to find any more in the 2007 version of the program.

I want the a cell in a row to auto enter a value when people are doing simple data entry into a spreadsheet. I do not, of course, want that entry in every unused row ad infinitum in the spreadsheet. I suppose I could use a drop down box with only 1 option.

If I were in Access, upon entering a field, I could code it to enter the default amount I want. (Assuming that I didn't just set the default value when setting up the table and field. Don't have a clue how to do it in Excel. I have tried several options, but none have worked.

Thanks,
Robert

[Advertisements]

Unlike other Microsoft Office programs such as Microsoft Word, Microsoft Excel does not provide a button to automatically number data. However, you can easily add sequential numbers to rows of data by filling a column with a series of numbers or by using the ROW function.

[Neil Jones]

Unlike other Microsoft Office programs such as Microsoft Word, Microsoft Excel does not provide a button to automatically number data. However, you can easily add sequential numbers to rows of data by filling a column with a series of numbers or by using the ROW function.

[rmccafferty]

I don't think you understood my question. I want to have default values in a cell (a column) unless the user changes the value. These values are not sequential numbers. One example is the length of a mortgage to be calculated for a loan. It can default to 30 (years), but can also be over-ridden as necessary. But i do not want them to see this value all the way down the spreadsheet for the maximum potential number of lines in the spreadsheet.

[Ax238]

Hello,

You should be able to get the drop-down list working by using Validation. The following will help:
Setting up data validation in Excel 2007

In the Allow drop-down box, choose "List". Then, enter the allowable values in the Source field, separated by commas (e.g. 15, 30). Note the only values possible will be those in the list.

Regards,

Ax

[Jonesey]

Hello,

You should be able to get the drop-down list working by using Validation. The following will help:
Setting up data validation in Excel 2007

In the Allow drop-down box, choose "List". Then, enter the allowable values in the Source field, separated by commas (e.g. 15, 30). Note the only values possible will be those in the list.

Regards,

Ax

One limitation of using Data Validation lists is that the list MUST be on the same sheet as the validated cells.

This can prove to be problematic when you want to insert/delete rows.

[Ax238]

That's actually not entirely true. You can, like I said, just use a comma-delimited list of values – not a range – as the Source for the validation criteria.

[Jonesey]

That's actually not entirely true. You can, like I said, just use a comma-delimited list of values – not a range – as the Source for the validation criteria.

Yes - for small lists I suppose that's not a problem - entering validation data for a large number would still cause a problem, as you can't paste a list into the criteria cell.

[JDMcI]

Did you ever get any joy with this. I too have switched from Access (on PC) to Excel (on MAC) and am finding limitation . . . . For example in a Country field, I would like USA to be the default, but have a drop down list of other entries in that column . . . .

I am used to doing this in Access but don't see how to do it in Excel. And, of course, nothing is easy to find any more in the 2007 version of the program.

I want the a cell in a row to auto enter a value when people are doing simple data entry into a spreadsheet. I do not, of course, want that entry in every unused row ad infinitum in the spreadsheet. I suppose I could use a drop down box with only 1 option.

If I were in Access, upon entering a field, I could code it to enter the default amount I want. (Assuming that I didn't just set the default value when setting up the table and field. Don't have a clue how to do it in Excel. I have tried several options, but none have worked.

Thanks,
Robert

[Jonesey]

How do you envisage the default of USA being triggered?

[04ST1300TX]

Low end Excel user here, New to the boards and I have a similar question that I think is related to this thread.


I am working on a large workbook with drop downs etc. What I am looking for, if I select an item from a drop down, I would like a few fields in the following columns to auto populate with a default value.

For instance, this work book contains company names in 1 field with contact info in another. Currently both are available in drop down lists, but thru human error you always run the risk of putting the wrong contact info in place. I want the logic such that when I select a company it automatically poulates contact info the in the appropriate fields. Is it possible to have IF / THEN statements?

Should this be done using a macros?

Thanks in adavnce for the assistance.

[Jonesey]

I think in this instance you'd be better off using lookup formulae, all triggered by your first drop down.

[Dave2009]

I have the same problem as originally reported except in Excel 2003.

"I want to have default values in a cell (a column) unless the user changes the value. These values are not sequential numbers. One example is
the length of a mortgage to be calculated for a loan. It can default to 30 (years), but can also be over-ridden as necessary. But i do not want
them to see this value all the way down the spreadsheet for the maximum potential number of lines in the spreadsheet."

I've searched the web and groups and keep finding the solution of data validation. I don't see this solving this problem in my version. I've tested this solution by creating a dropdown box that lists the potential values (text), but it is not automatically inserted into the cell. Of the 4 potential values for this cell, one will be used about 80% of the time. I would like this value to automatically populate the cell, but it can be changed the other 20% of the time as needed. Since the user will be either using <TAB> or <ENTER> to move from cell to cell, I don't want to make the user go to the mouse to select the value.

If the only solution is data validation, I need specific instructions on how to make my default value automatically populate the cell as I've not been able to find that anywhere.

Thank you for your assistance

Dave

[Jonesey]

I’m just thinking out loud on this one, so please bear with me.

There are only 2 distinct methods to achieve what you want.

Either:
1 – your drop down list (irrespective of defaults or other values) is ONLY populated when a new row (record) is entered. I’m assuming that each row in your spreadsheet equates to a unique data record.

2 – you pre-populate as many rows as you want, but the actual text, default or otherwise is ‘hidden’ by making the text colour exactly the same as the cell background colour, then setting it back to normal text (black?) when a new row is entered.

Option 1 can be triggered either by a specifically written macro, or less commonly, you could write a little bit of code to create & populate your drop down by using either the ‘Worksheet_Change’ or the ‘Worksheet_SelectionChange’ built in macros.

In fact, thinking about it, Worksheet_Change will work a lot better.

Try something like this:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long, c As Long
Application.ScreenUpdating = False
r = ActiveCell.Row
c = ActiveCell.Column
If IsEmpty(Cells(r, 2)) = True Then 'This indicates it's a NEW record
Cells(r, 3).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="10, 15, 20, 25, 30"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ActiveCell.FormulaR1C1 = "30"
Cells(r, 1).Select
ElseIf IsEmpty(ActiveCell) = True Then
Cells(r, c).Select
End If
End Sub





The only downside to this is that the macro will be invoked EVERY time a change is made to the worksheet, although it only takes a second to run, this might prove distracting.

The assumptions are:

a) Column A contains some sort of Identifier, which is only entered when a new record is entered
b) Column C contains your drop down, pre-populated with the number 30, but allowing you to also select 10, 15, 20 or 25

c) Column B must only populated AFTER Column A – this is because Column B is tested every time the macros is run. If it’s empty, it’s a new record. If it isn’t empty, the macro stops execution

[Dave2009]

WOW, Jonesey! How do you know this stuff? I'm impressed.

This is 99.99% perfect. I changed your numbers to the text (in Upper Case) I need to insert and the column from C to D. I'm going to get greedy now.

Since I'm entering text with your code (in Upper Case) and on those few occassions where it needs to be changed, is there a way to change the contents of the cell in D when entered in Lower case and have the validate list recognize the lower case, but display it in Upper case? I guess I could enter both Upper Case and Lower Case in the Validate List, so the question becomes how can those lower case entries be changed to upper case? All I'm trying to do is simplify the date entry proces by reducing the number of key strokes.

Finally, the workbook this is going into will have 17 worksheets and each worksheet will need to run this code. Is there a way to make this happen without entering this code into each worksheet?

Thank you so much for taking your time and sharing your knowledge to help me solve my problem.

Dave

[Jonesey]

Should be possible - but you need to clarify a couple of points.


1 - which cell column contains the validation dropdown?
2 - which cell is acting as the trigger?

There is a function that VB uses called ucase, which converts any string of characters into Upper Case

If all you're doing here is using Excel as a means of entering data into a spreadsheet, probably the best and easiest way is to design a form, with dropdown boxes, text boxes, check marks, that sort of thing - but this can get a bit hairy if you're not fully conversant with VB

To answer your 2nd question - yes, you'll have to enter the code in each separate sheet. BUT, you can simplify this by putting actual code into a private module, giving it a unique procedure name, like DataEntry. Then, in each of your separate worksheets, between "Private Sub Worksheet_Change(ByVal Target As Range)" and "End Sub", you just type in DataEntry

Edited by Jonesey, 25 February 2009 - 09:27 AM.