How to vary format of single cell by data type?

[MrStohler]

I have a table that summarizes & compares data from several years by week (each year is given 52 weeks for this ongoing effort). Each yearly table has over 100 items that are tabulated by week. All of the data is numerical but some of it is in decimal form and some is in time form - [h]:mm:ss

To make the summary manageable the goal is to use a drop down menu (Combo Box) to select which variable will be considered). This would present as a table where the right column represents the week of the year, and each year has a separate column of data.

Pretty straight forward if all the data is the same format. But when some of the data is [h]:mm:ss the summary table returns a decimal that represents the time as a decimal fraction of a 24 hour period ( 4:12:53 is reported as 0.175613 ).

Is there a way to have the format of the summary data match the format of the source.

Thanks

 

[SkipVought]

Hi,

Are you saying that you have different numeric datatypes in one column?

Are you using a PivotTable to summarize?

What verion of Excel?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Although TIME can be displayed as elasped time rather than a time stamp value, you have uncovered a problem that is enherent in the stored value and the displayed value. MS Access solves this dilema by NOT making this type of display format [h] possible.

The best solution is to convert DAYS to HOURS or MINUTES for storing the data.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MrStohler]

Thanks for the reply,

To answer the Q's in order:

Q: Are you saying that you have different numeric data types in one column?
A: That depends on how one interprets it, all columns would have the same type of data at any one time, but by using the menu to select a different variable could lead to all of the columns to either have mumerical or time data

Q:Are you using a PivotTable to summarize?
A: No, I was not able to find a practical way to make that work.

Q:What verion of Excel?
A: 2002 SP3

Reply to 17:06 posts:
Post: Although TIME can ....
Reply: Unfortunately the target audience is Excel capable and not Access capable (and I am not in a position to dictate)

Post: The best solution is to convert DAYS to HOURS....
Reply: Agree, I am in the process of adding if statements to the Index functions used to find retrieve the summary so that if the columns with time data are referenced the value is multiplied by 24

Thanks

 

[SkipVought]

Well it seems that your approch is not using native Excel features; rather some code to retrieve the subset of choice. Is that the case? And if so, your questions would be better addressed in forum707.

Otherwise, please explain your method of data access and reporting.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MrStohler]

The summary data is obtained by this formula

=INDEX('07 Summary'!$C$5:$EM$57,$B22,$C$4)

Each cell of the summary table has a similar formula (this one is for the 12th week of 2007, there columns in the summary table for the years 2003 through 2008)

'07 Summary'!$C$5:$EM$57 is the range where the data is stored for 2007

$B22 represent the cell with the week of the year (Column B from row 11 to 62 in numbered from 1 to 52)

Cell "$C$4" is the cell that the drop down menu index to select the variable to be looked up

 

[SkipVought]

Using this approch, the is no way of changing cell formats other than a macro.

Determine the number of distinct formats you will need. Macro record changing the format for each.

Then assign a macro to the drop down. I assume that you are using a Forms control rather than a Control Toolbox control. The Forms control Linked Cell returns a numeric index to the dropdown list. You can use that value to control which recorded macro will be executed after the dropdown selection is changed. You can use a macro something like this, assuming that the linked cell is Sheet2!Z1...

Sub DropDown1_Change()
   select case Sheets("Sheet2").[Z1]
      case 1, 3 to 5   'format general
         macro1
      case 2           'format time
         macro2
      case 6           'format number
         macro3 
   end select
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

This may make things a little ugly if calculations need to be run off the back of the data but if it is presentational only then something like:

=if(vlookup(ComboboxSelection,DataChoiceResult,2,false)= "Timeformat", TEXT(INDEX('07 Summary'!$C$5:$EM$57,$B22,$C$4),"[h]:mm:ss"),
TEXT(INDEX('07 Summary'!$C$5:$EM$57,$B22,$C$4),"0.0"))

may work for you - as you can see, it converts the result to text and displays it within a specific format.
You would need some form of control table lookup ("DataChoiceResult") which would indicate based on the choice made in the combo box, what the format should be. something like:

1 Timeformat
2 Numeric
3 Timeformat
4 TimeFormat
5 Numeric

If there are further calcs on this data then you should probably go with Skip's suggestion of running code and altering the format of the column using that.


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question