Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"year" date function
I'd like to have a drop down box with various years in one cell 2000, 2001,
2002 etc) linked to another cell which would display the year. I can't find a drop down box in forms, but I found a "list box" For "input range" I clicked a column containing 2000, 2001, 2002, etc For "cell link" I clicked on a cell called "year" where I want it displayed. I've tried numerous "format cell" maneuvers, ie setting original date cells as text, numeric, date, custom date "yyyy". If I format it as regular date, of course, the 2000 gets converted to date in the usual manner. Is there a way to tell excel that "1999" is purely the year, ie no day or month. I tried all combinations. However, no matter what I do, if I click on 2000 (first in the list), 1 is displayed in the "cell link". If i click on 2001 (second in lits), 2 is displayed. I have a calendar spreadsheet with a cell called "year" that changes the calender automatically, but currently you need to type the year in. What I want is to intruduce a drop down box that would send the result to that sell. I can use a "spinner" function and set minimum and maximum numbers etc, and the correct number is transferred and the calendar works, but I can't get it working with "list box". Thanks. Is there a drop down box option as well. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
"year" date function
Use a data validation drop down list. It will return the actual selection
value rather than the number of the selection. Otherwise, you'll need to use a formula to convert the selection number to a year number. Biff "Scott" wrote in message ... I'd like to have a drop down box with various years in one cell 2000, 2001, 2002 etc) linked to another cell which would display the year. I can't find a drop down box in forms, but I found a "list box" For "input range" I clicked a column containing 2000, 2001, 2002, etc For "cell link" I clicked on a cell called "year" where I want it displayed. I've tried numerous "format cell" maneuvers, ie setting original date cells as text, numeric, date, custom date "yyyy". If I format it as regular date, of course, the 2000 gets converted to date in the usual manner. Is there a way to tell excel that "1999" is purely the year, ie no day or month. I tried all combinations. However, no matter what I do, if I click on 2000 (first in the list), 1 is displayed in the "cell link". If i click on 2001 (second in lits), 2 is displayed. I have a calendar spreadsheet with a cell called "year" that changes the calender automatically, but currently you need to type the year in. What I want is to intruduce a drop down box that would send the result to that sell. I can use a "spinner" function and set minimum and maximum numbers etc, and the correct number is transferred and the calendar works, but I can't get it working with "list box". Thanks. Is there a drop down box option as well. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
"year" date function
What formula, as an example, would you use to convert selection "1", for
example, to 2000. Also, where do you find and how do you implement data validation droip down list. I was surprised that you couldn't simply link numeric 2000 to another cell as 2000 rather than as a selection number. "Biff" wrote: Use a data validation drop down list. It will return the actual selection value rather than the number of the selection. Otherwise, you'll need to use a formula to convert the selection number to a year number. Biff "Scott" wrote in message ... I'd like to have a drop down box with various years in one cell 2000, 2001, 2002 etc) linked to another cell which would display the year. I can't find a drop down box in forms, but I found a "list box" For "input range" I clicked a column containing 2000, 2001, 2002, etc For "cell link" I clicked on a cell called "year" where I want it displayed. I've tried numerous "format cell" maneuvers, ie setting original date cells as text, numeric, date, custom date "yyyy". If I format it as regular date, of course, the 2000 gets converted to date in the usual manner. Is there a way to tell excel that "1999" is purely the year, ie no day or month. I tried all combinations. However, no matter what I do, if I click on 2000 (first in the list), 1 is displayed in the "cell link". If i click on 2001 (second in lits), 2 is displayed. I have a calendar spreadsheet with a cell called "year" that changes the calender automatically, but currently you need to type the year in. What I want is to intruduce a drop down box that would send the result to that sell. I can use a "spinner" function and set minimum and maximum numbers etc, and the correct number is transferred and the calendar works, but I can't get it working with "list box". Thanks. Is there a drop down box option as well. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
"year" date function
Hi, to do a data validation drop-down list, got to Data--Data
Validation--List and enter your list criteria. That list criteria can be a range in an excel worksheet. Brevity is the soul of wit. "Scott" wrote: What formula, as an example, would you use to convert selection "1", for example, to 2000. Also, where do you find and how do you implement data validation droip down list. I was surprised that you couldn't simply link numeric 2000 to another cell as 2000 rather than as a selection number. "Biff" wrote: Use a data validation drop down list. It will return the actual selection value rather than the number of the selection. Otherwise, you'll need to use a formula to convert the selection number to a year number. Biff "Scott" wrote in message ... I'd like to have a drop down box with various years in one cell 2000, 2001, 2002 etc) linked to another cell which would display the year. I can't find a drop down box in forms, but I found a "list box" For "input range" I clicked a column containing 2000, 2001, 2002, etc For "cell link" I clicked on a cell called "year" where I want it displayed. I've tried numerous "format cell" maneuvers, ie setting original date cells as text, numeric, date, custom date "yyyy". If I format it as regular date, of course, the 2000 gets converted to date in the usual manner. Is there a way to tell excel that "1999" is purely the year, ie no day or month. I tried all combinations. However, no matter what I do, if I click on 2000 (first in the list), 1 is displayed in the "cell link". If i click on 2001 (second in lits), 2 is displayed. I have a calendar spreadsheet with a cell called "year" that changes the calender automatically, but currently you need to type the year in. What I want is to intruduce a drop down box that would send the result to that sell. I can use a "spinner" function and set minimum and maximum numbers etc, and the correct number is transferred and the calendar works, but I can't get it working with "list box". Thanks. Is there a drop down box option as well. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date formulas | Excel Discussion (Misc queries) | |||
Mileage Claim Formula | New Users to Excel | |||
Date function in Excel that updates only when a doc is changed? | Excel Worksheet Functions | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
Date function | Excel Worksheet Functions |