Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-20-2020, 10:56 AM
DJ0691 DJ0691 is offline Format a cell in a table Windows 10 Format a cell in a table Office 2013
Novice
Format a cell in a table
 
Join Date: Jan 2020
Posts: 18
DJ0691 is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 01-20-2020, 12:54 PM
NBVC's Avatar
NBVC NBVC is offline Format a cell in a table Windows 10 Format a cell in a table Office 2016
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

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))
Reply With Quote
  #3  
Old 01-20-2020, 03:20 PM
DJ0691 DJ0691 is offline Format a cell in a table Windows 10 Format a cell in a table Office 2013
Novice
Format a cell in a table
 
Join Date: Jan 2020
Posts: 18
DJ0691 is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 01-21-2020, 02:50 PM
DJ0691 DJ0691 is offline Format a cell in a table Windows 10 Format a cell in a table Office 2013
Novice
Format a cell in a table
 
Join Date: Jan 2020
Posts: 18
DJ0691 is on a distinguished road
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


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
Format a cell in a table 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 a cell in a table 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:45 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft