#1
|
|||
|
|||
Format a cell in a table
Hello Gurus:
I have a table in Excel that represents a year-long date grid. The 31 columns are labeled 1 through 31 to represent the days. The 12 row labels are Jan through Dec to represent the 12 months. How would I: 1) Color in a cell that represents a date entered in a form, ie, for March 15th, the cell at the intersection of column 15 and row = Mar would be red or yellow filled. Is that possible? Or can an ‘X’ be entered there? 2) A different question and perhaps a bit trickier, if an ‘X’ is entered in this table say at that same intersection, can VBA calculate all the entries in the table before the X (ie, ‘8’ has been entered in several cells before the X) and sum them? Thanks for any help! |
#2
|
||||
|
||||
You can colour a cell to meet your condition with conditional formatting, then use a sum formula to calculate the sum to that point.
eg. Assuming your table is in A1:AF13 and you enter a date in, say, cell A17. Then you would select cell A1 and go to Home|Condional Formatting|New Rule. Select "use a formula...." and enter formula: =ADDRESS(ROW(),COLUMN())=ADDRESS(MATCH(TEXT($A$17, "mmm"),$A$1:$A$13,0),MATCH(DAY($A$17),$A$1:$AF$1,0 )) Click "Format" and choose from the "Fill" tab. Keep clicking "OK" until finished. You should see a cell coloured corresponding to date in A17. Now to sum use formula: =SUM(OFFSET($A$1,MATCH(TEXT(A17,"mmm"),$A$1:$A$13, 0)-1,1,1,MATCH(DAY(A17),$A$1:$AF$1,0)-2)) |
#3
|
|||
|
|||
Thanks a ton for the formulas - I learned a lot by studying them. One thing, if I could impose on you again and, sorry, I should have mentioned: the grid is blank - there are no dates in the table/grid. Your nice formula does not work since there is nothing to match to determine true/false. The table/grid is used for user input of hours per day of vacation, ie, they enter '8' for a full day of vacation for every day in the months prior to the value in A17 and the worksheet calcs how much time they've used and how much time they have left to use. The entry date (cell A17) contains the person's hire date.
Is it possible to have the cell colored using only the cell address based on the date in cell A17? Thanks again!!! Last edited by DJ0691; 01-21-2020 at 07:32 AM. |
#4
|
|||
|
|||
Thanks for your help and input - using some of your examples and a lot debug, I was able to achieve the result I was looking for. It did lead to a rather large formula but appears to be working great:
=IF(MONTH($A$17)=1,0,SUM(B2:INDEX(named_range,MATC H(TEXT($A$17,"mmm"),A2:A13,0)-1,32)))+SUM(OFFSET($A$1,MATCH(TEXT($A$17,"mmm"),$A $1:$A$13, 0)-1,1,1,MATCH(DAY($A$17),$A$1:$AF$1,0)-2)) Thanks again. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Within a table format, a single cell can't automatically flow to the next page? | laurieli | Word | 3 | 12-09-2015 07:09 PM |
Pasting text from Excel cell into word without creating a table, and keeping the in-cell formatting | hanvyj | Excel Programming | 0 | 08-28-2015 01:15 AM |
Move table cell contents from one table to another table or cell in same table | donaldadams1951 | Word VBA | 4 | 02-04-2015 03:54 PM |
Format Painter doesn't work on Table Format? | Joey Cheung | Word Tables | 1 | 08-20-2014 02:24 AM |
Auto-populate an MS Word table cell with text from a diff cell? | dreamrthts | Word Tables | 0 | 03-20-2009 01:49 PM |