Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Auto entry (default entry) in an Excel cell


  • Please log in to reply

#1
rmccafferty

rmccafferty

    Member

  • Member
  • PipPip
  • 16 posts
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
  • 0

Advertisements


#2
Neil Jones

Neil Jones

    Member 5k

  • Member
  • PipPipPipPipPipPipPipPip
  • 8,476 posts
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.
  • 0

#3
rmccafferty

rmccafferty

    Member

  • Topic Starter
  • Member
  • PipPip
  • 16 posts
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.
  • 0

#4
Ax238

Ax238

    Tech Staff

  • Technician
  • 1,323 posts
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
  • 0

#5
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts

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.
  • 0

#6
Ax238

Ax238

    Tech Staff

  • Technician
  • 1,323 posts
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.
  • 0

#7
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts

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.
  • 0

#8
JDMcI

JDMcI

    New Member

  • Member
  • Pip
  • 1 posts
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


  • 0

#9
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
How do you envisage the default of USA being triggered?
  • 0

#10
04ST1300TX

04ST1300TX

    New Member

  • Member
  • Pip
  • 1 posts
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.
  • 0

Advertisements


#11
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
I think in this instance you'd be better off using lookup formulae, all triggered by your first drop down.
  • 0

#12
Dave2009

Dave2009

    New Member

  • Member
  • Pip
  • 4 posts
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
  • 0

#13
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
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
  • 0

#14
Dave2009

Dave2009

    New Member

  • Member
  • Pip
  • 4 posts
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
  • 0

#15
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
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.

  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP