Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 - How to get cell referrences from dates in combobox
I have an Excel 2007 spreadsheet named "Weight" that has dates in Column A, weight in Column C, Blood Pressure in Column D, etc. The workbook contains another spreadsheet named "Stats" that has 2 combobox's for "StartDate" and EndDate". These combobox's are populated from a named range ("WeightDates") that returns the dates from the "Weight" spreadsheet. I want the user to select a StartDate and EndDate, from the combobox's, that can be used to return a range of weight values that I can then use to calculate average weight and other stats. The combobox's return a text value that I can't use for looking up date values. Question: How can I use these combobox's to return a range that can be used in spreadsheet functions? E.g. =AVERAGE(hethere) Thanks Don |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 - How to get cell referrences from dates in combobox
On Sun, 02 Oct 2011 21:49:38 -0700, Wee Willie Winkie wrote:
I have an Excel 2007 spreadsheet named "Weight" that has dates in Column A, weight in Column C, Blood Pressure in Column D, etc. The workbook contains another spreadsheet named "Stats" that has 2 combobox's for "StartDate" and EndDate". These combobox's are populated from a named range ("WeightDates") that returns the dates from the "Weight" spreadsheet. I want the user to select a StartDate and EndDate, from the combobox's, that can be used to return a range of weight values that I can then use to calculate average weight and other stats. The combobox's return a text value that I can't use for looking up date values. Question: How can I use these combobox's to return a range that can be used in spreadsheet functions? E.g. =AVERAGE(hethere) Thanks Don Excel (and VBA) can convert many text values that look like dates into the actual date. What, exactly, is your combobox returning? Is it a text value that looks like a date string? Or is it a text value that looks like a serial number? Either can be converted to an actual date. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 - How to get cell referrences from dates in combobox
On Mon, 03 Oct 2011 06:53:50 -0400, Ron Rosenfeld wrote:
On Sun, 02 Oct 2011 21:49:38 -0700, Wee Willie Winkie wrote: I have an Excel 2007 spreadsheet named "Weight" that has dates in Column A, weight in Column C, Blood Pressure in Column D, etc. The workbook contains another spreadsheet named "Stats" that has 2 combobox's for "StartDate" and EndDate". These combobox's are populated from a named range ("WeightDates") that returns the dates from the "Weight" spreadsheet. I want the user to select a StartDate and EndDate, from the combobox's, that can be used to return a range of weight values that I can then use to calculate average weight and other stats. The combobox's return a text value that I can't use for looking up date values. Question: How can I use these combobox's to return a range that can be used in spreadsheet functions? E.g. =AVERAGE(hethere) Thanks Don Excel (and VBA) can convert many text values that look like dates into the actual date. What, exactly, is your combobox returning? Is it a text value that looks like a date string? Or is it a text value that looks like a serial number? Either can be converted to an actual date. It is a text value that looks like a serial number. The second question is, how do I get a range from the Start and End dates that I can use in a function? Thank you Don |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 - How to get cell referrences from dates in combobox
On Mon, 03 Oct 2011 13:01:16 -0700, Wee Willie Winkie wrote:
Excel (and VBA) can convert many text values that look like dates into the actual date. What, exactly, is your combobox returning? Is it a text value that looks like a date string? Or is it a text value that looks like a serial number? Either can be converted to an actual date. It is a text value that looks like a serial number. I assume it is some five digit number, like 41027. That being the case, merely perform some mathematical operation on it to convert it to the serial number of the date. For example, if your dates are in A1:A10, and your "linked cell" to the combo box is D4, then =match(--d4,a1:a10,0) will return the row number containing the relevant date. The second question is, how do I get a range from the Start and End dates that I can use in a function? There are many ways based on the above method, but exactly how depends on the details of your worksheet. Basically you use the match function to find the relevant rows in the array, and then return a limited array based on that. Again, the precise method depends on information not available to me. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referrences of multiple cells from one worksheet to another | Excel Discussion (Misc queries) | |||
data range referrences | Charts and Charting in Excel | |||
Couting the number of referrences that... | Excel Worksheet Functions | |||
In an excel cell,choosing a value,similar to combobox in VB - reg | New Users to Excel | |||
format combobox in userform to accept dates | Excel Worksheet Functions |