Main Area and Open Discussion > General Software Discussion

classify data

<< < (2/3) > >>

capitalH:
See attached for an attempt (zipped - xlsx attachments not  allowed on DC)

Note that:
1) It is a bit more general than your example (every date can have different text).
2) It does not move the data - it references it (I personally do not like destroying data - and moving the data MUST be done with a macro - this is pure Excel)
3) It has a hidden line 1 and hidden column C - required to calculated the dates properly.
4) You can change the format of the date displayed (change DD-MMM in the formula to the format you want)
5) If you want an empty line between the date and the text, add another ",char(10)" (right before/after the first)
6) Cells MUST be formatted as wrap text, otherwise text will appear on a single line (i.e. linebreaks will be ignored)
7) Created using Excel 2007 - but should work in other versions as well, nothing special done

capitalH:
the target cells appear to have date format in the screenshot, but this is not necessary imo, there will be no problem them being simple text
-kalos (November 03, 2011, 03:36 PM)
--- End quote ---
Good - that is the way I have done it - but it can be customised

but the "calculated dates" must be in date format, because they will update according to the date I have set as "today date"
-kalos (November 03, 2011, 03:36 PM)
--- End quote ---

As long as it is Excel dates and not "text" (Excel dates are numbers - and are just formatted to look like a date. If you change it to "Number" you will get a number of around 40000. If you calculate these dates in Excel, it is an Excel date and should work perfectly)


it would be really nice if inside the calendar cells (that will contain the date at top and the appended text at bottom), the date is highlighted, formated or somehow distinguished from the other text below, so that it would be easier to the eye to see which text correspond to which date
-kalos (November 03, 2011, 03:36 PM)
--- End quote ---
This I cannot do

or maybe each date cell in the calendar should be splitted into two cells, one containing the date only and the other (below) containing the appended text
-kalos (November 03, 2011, 03:36 PM)
--- End quote ---
This I can do - will upload another version if you are happy with the approach of my first upload


last, a shortcut or button preferably should trigger the data migration/binding across the cells (from source "my text" cell, to target cells of the calendar, with calculated dates as pointers)
-kalos (November 03, 2011, 03:36 PM)
--- End quote ---


This I cannot do. The links are live (references) and cannot be moved - unless you use macros. If the source data changes - the cells will immediately change (unless automatic formula calculations has been turned off). Should you want a copy (that does not update), I suggest you copy the data to another sheet/place.

capitalH:
Sorry - version 2 was easier than I expected

1) Does bold formatting - but uses conditional formatting of Excel 2007 - may not work properly in Excel 2003 or earlier
2) Puts the text in a new row
3) Less complex than the previous version

kalos:
Sorry - version 2 was easier than I expected

1) Does bold formatting - but uses conditional formatting of Excel 2007 - may not work properly in Excel 2003 or earlier
2) Puts the text in a new row
3) Less complex than the previous version
-capitalH (November 04, 2011, 01:07 AM)
--- End quote ---

can you tell me what to do to reproduce it, because I have EXCEL 2003 and because I need to customize it a bit?

thanks!!

capitalH:
Replication Steps:
1) In column A - put the dates of your input data (this can be in another location - but then you have to change the formula later)
2) In column B, for each date in column A  - enter the text you want to associate with this date (again can be in another location, but must be next to the dates)
3) In cell C1 (again, this can be another location - however you will need to change the other locations from here relative to this cell - I am going to stop typing location stuff now), type 2011-01-01 (your first date)
4) In cell d1, "=C1+1", drag to I1
5) Copy c1:I1 to c3:I3
6) c3 "=I1+1"
7) Copy c3:I3 to  c5:I5, c7:I7
8) In cell c2 "=IF(ISNA(VLOOKUP(C1,$A$1:$B$100,2,0)),"",VLOOKUP(C1,$A$1:$B$100,2,0))" (Column A- dates from step 1, B-Text from step 2, 100 was chosen as upper limit - change if you need more (or less) than 100)
9) Copy C2 to D2:I2, C4:I4, C6:I6, C8:I8

Conditional formatting (I cannot remember how to do this in Excel 2003)
Set the date cells to be bold when the row below is non-blank.



The attachment should work (I have removed the 2007 conditional formatting and formulas - but I cannot test it for you).

Navigation

[0] Message Index

[#] Next page

[*] Previous page