default value

LisaLisa

New Member
Joined
Dec 16, 2004
Messages
4
Hello

i searched for the answer on the forum but could not find a solution that worked for me.

Problem: i have a log of calls and would like to limit some columns to either "yes" or "no" and if the cell is left blank to default to "no".

originaly i set up a validating rule with the list: Yes, No; and UNTICKED "ignore blank". however nothing happens if i tab through the cell in question. i only get an error if i select one of the options from the pull-down list (Yes, No) and then delete it using backspace. if i delete it using "delete" key i dont get an error.

ideally i would like the cells to defaul to "No" if its left blank and only if there is something in column A.

i am reluctant to use macros/vba because:
a) its a large speadsheet and i dont want to weigh it down even more
b) dont have much experience in vba

i am using excel'02 sp1

i would very much appreciate any help/advice

thank you

Lisa
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

MycroftII

New Member
Joined
Sep 30, 2004
Messages
29
Why don't you simply prepopulate all cells in that column with "No"?

If there are already some cells filled and therefore manual editing would be too much effort, try the following:

1. Copy a cell containing "No" (CTRL-C)
2. Edit -> Go To -> Special -> Blanks - now all empty cells should be selected. (if other columns also contain empty cells, first copy this column to a new worksheet, apply this method and then copy it back)
3. Paste the cell (CTRL-V)

HTH,
Mycroft
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Welcome to MrExcel -- another option is to, in your down-stream processing, either A] go through the above process or B] allow for anything <> "Yes" be "No" rather than specifically checking for "No".
 

LisaLisa

New Member
Joined
Dec 16, 2004
Messages
4
Thank you MycroftII and just_jon.

i decided to use <> "Yes" method as it seemed more elegant than prepolulating the column.
however it doesnt quite work for another column where i have more than 2 values on the predefined list. e.g. A, B, C, n/a. if the user does not select any of the options and leaves the cell blank i want it to be counted as "n/a".
i used the following formula for counting just "n/a"s:
=SUMPRODUCT(('Daily Log'!$B$5:$B$1003>A3)*('Daily Log'!$B$5:$B$1003<=A4)*('Daily Log'!$O$5:$O$1003=$Q$2))
where A3 and A4 are the dates and $Q$2 = "n/a"

if i add another criteria for blanks: "", the result becomes zero:
=SUMPRODUCT(('Daily Log'!$B$5:$B$1003>A3)*('Daily Log'!$B$5:$B$1003<=A4)*('Daily Log'!$O$5:$O$1003=$Q$2)*('Daily Log'!$O$5:$O$1003=""))
what am doing wrong here?
and
if i wanted the cells to default to "n/a" automatically if left blank and the date in 'Daily Log'!$B$5:$B$1003 is already entered, would the only solution be the use of vba/marco?

thank you

LL
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

What does a typical record look like?

What I'm thinking is have an event macro monitor changes in a row, and seeding those cells as No and N/A if blank, or leaving them as-is once filled.

Just need to find the best trigger for that to happen... any ideas?
 

LisaLisa

New Member
Joined
Dec 16, 2004
Messages
4
just_jon, thank you for your prompt reply

the record is laid out as follows:
"Daily log" worksheet:
date_raised (mandatory); date_closed; application; a/c used (yes, no); fix_required (A, B, C, n/a)

"trends analysis" worksheet:
week_ending; a/c_yes, a/c_no (calculating how many times a/c was used or not used during that week); fix_A, fix_B, fix_C, fix_n/a (fix columns calculate number of times each fix was applied during that week)

so as a trigger to auto populate the fix_required field i would like to use date_raised as it is a mandatoty field.

hope i explained it ok, if not i can post the sample.

Thank you for you help

LL
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, [A:A]) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#00007F">If</SPAN> Len(Range("D" & Target.Row)) = 0 <SPAN style="color:#00007F">Then</SPAN> Range("D" & Target.Row) = "No"
    <SPAN style="color:#00007F">If</SPAN> Len(Range("E" & Target.Row)) = 0 <SPAN style="color:#00007F">Then</SPAN> Range("E" & Target.Row) = "N/A"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Put the above in the Daily Log sheet module. It should fill column D with No and column E with N/A when 1] an entry is made in column A, and 2] those columns do not yet hold a value.
 

LisaLisa

New Member
Joined
Dec 16, 2004
Messages
4
just_jon, thank you for the solution

i tried running the module but nothing happens as if it's not recognised.
do i need to do anything else to make it work?

sorry to ba a pain but i am new to vba - i only used to record macros and then tweak them in the editor.

thank you

LL
 

Forum statistics

Threads
1,147,623
Messages
5,742,209
Members
423,714
Latest member
ftp2jz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top