Change Cell Reference Depending On Date
Jun 11, 2009
The spreadsheet attached is a timesheet I use. The last column on the sheet (IV) keeps a running total of time spent on each project. I need a way that i can find out how much time was spent on projects in specific timeframes (eg 01/01/09 - 07/01/01, or 01/01/09 - 31/01/09). my initial thought is perhaps i can have a "from" cell and a "to" cell to input the dates i require and it will total the relevant cells in the rows below.
NOTE! This workbook uses VBA to auto hide any column which are dates previous to today's date so you may need to unhide them to view times logged.
View 2 Replies
ADVERTISEMENT
Jan 9, 2013
I am trying to create a sheet in XL 2010.
In Column A each cell will contain a date (differnet from other cells in that column) when inspection was last done.
Column B is when the weekly inspection is due.
Column C is when bi-weekly inspection is due.
Column D is when monthly inspection is due.
Column E is when 6 monthly inspection is due.
I need a formula to change the colour of cells B, C, D & E when each inspection is due depending on the date entered in A
I am hoping its possible that the cell colour can stay for 2 days after the due date and then return back to blank after the second day.
For example if cell A1 has a date of January 1 2013 then on January 8 2013 cell B1 turns red then on January 10 2013 the cell returns back to normal.
A1
B1
C1
D1
E1
Inspection Date
Weeekly Due
Bi-WeeklyDue
Monthly Due
6 Monthly Due
January 1 2013
Change red Jan 8 & return blank Jan 10
Change red Jan 15 & return blank Jan 17
Change red Feb 1 & return blank Feb 3
Change red June 1 & return blank June 3
View 3 Replies
View Related
Mar 14, 2012
I have a budget worksheet that uses a spending forecast sheet to determine where the spending level for each budget code should be to the end of the current month. In other words, the Forecasted YTD To Month End fomula for each budget code in the budget worksheet is simply:
=SUM(Forecast!D8:F8)
Where column D is Jan, E is Feb, F is Mar etc in the forecast which distributes the budgeted amounts by month.
In each row on the budget sheet I want to evaluate today's date and if we're in March I want the formula to read as above but when we move to April I want it to use G as the end column in the range to sum.
SUMIF seems cumbersome in this circumstance and I wonder if a UDF might suit better.
View 9 Replies
View Related
Dec 31, 2013
I have some real estate rental so what I did 7 columns and its includes: name, phone number, ....., contract start date, and contract expiry date.
my question is how to make the row color changes depends on the expiry date
for example, the expiry date is 12/10/2014. I want the the row to be red when 12/10/2014 comes.
View 5 Replies
View Related
Feb 27, 2013
I am trying to format certain cells to change colors depending on the date and the content of the next field to show due dates.
cell label
due date
date completed
c5
au5
be5
c6
au6
be6
c7
au7
be7
c8
au8
be8
What I need is for the contents of au5 to be green up until 10 days before the due date, yellow up until 5 days, and red with less than 5 days until the due date. If the date completed is entered into be5, then the contents of au5 should be black.
Is this possible, and if it is, how do I set this up? I tried to use
=$AU5-TODAY()>=10
And choose green formatting (something I found on yahoo answers), but when I enter the date the text is still the original black font.
View 5 Replies
View Related
Mar 16, 2009
Excel 2003 > Attached is a small model of what I am trying to accomplish. Cells B2 and B4 contain the same formula … a formula that calls a simple function. The function has a variable passed to it … and the value of that variable changes depending on the location of the cell. Now, see the function in Module1 … it is called CellCalc. If the variable passed = Jim then value = 3. If the variable passed = Jack then value = 8. That is straightforward.
Note also that when Sheet1 is activated, I calculate the cells from left to right and top to bottom … that is important. Here is the challenge. If B2 > 1 then I want to add B2 to B4 and set B2 to 1. I can set B4 properly but I cannot reset B2. You can see my 2 attempts that are commented out. Is there some way of accomplishing this … or am I simply stuck in a circular reference?
The alternative to this is to write a function that operates externally on these cells. That will work for sure but then the values of the calculations will overwrite the functions in those cells, thereby taking away the dynamic nature of this application.
View 4 Replies
View Related
Jan 8, 2012
How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.
________A________________B_____________C_________D
1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)
If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?
View 4 Replies
View Related
May 22, 2014
I have a few buttons on my page that I would like to change colors depending on the value in cell AI1. Right now this is the code I have:
[Code] .......
What I would like is to add formatting for "rounded rectangle 4" as well. if the cell = 1, number 4 changes color, if the cell = 2 both change color, and if the cell =3 then only 5 changes color.
View 2 Replies
View Related
Feb 18, 2010
I want to be able to do the following: I have data in column a and I want the text to change to strikethrough in, for instance, cell a1 when data is entered in b1.
View 9 Replies
View Related
Apr 16, 2014
I have an if statement which shows either GP% or average revenue per product. The problem is that GP% needs to be in a percentage format and revenue per product needs to be in number format. 500 is now shown as 50000% or 50% will be shown as 0.5 for example. Is there anyway on changing the format of a cell based on the number?
View 1 Replies
View Related
Jun 3, 2013
Is there any way to have a cell change its colour depending on whether a condition is met. E.G., can i have it as green if the result is a plus number and red if its a negative?
View 2 Replies
View Related
Oct 22, 2009
How to a change a cell colour to say red in B6 if cell b12 = 1 and if e6 = 1 to change to green. I thought I might be able to use conditional formatiing but no. I had set the spread sheet to do a cell just for a condition representing a sum from another worksheet and it was working fine but i have to incorporate the two together and am stock.
I have attached a spread sheet.
View 14 Replies
View Related
Jul 11, 2007
I have a spreadsheet with names and start / finish dates columns down the left hand side then a row of week commencing dates along the top.
for each name row I would like to change teh fill colour of a cell to green to represent the week in which they started and to red for the week in which they finished. I therefore need to cross reference the start and finish dates for each name with the relevant week commencing dates at the top. Somehow! I presume there is some kind of vlookup type formula that I need to use in conditional formatting, but I am not sure what.
View 9 Replies
View Related
May 20, 2009
i need to change the colour of 4 cells depending on the one cell.
i have found some vba codes on the net but they are all based on numbers and i need it on text.
so if a1 ="alert" make A1 to A4 red
if a1="passed" make a1 to a4 green... and so on
i cant used conitional formatting as i need more than 3 colours
View 9 Replies
View Related
Feb 15, 2008
In my spreadsheet cell g1 is a name and cells g3:g6 contain data for that name.
I have a macro that exports the data in g3:g6 to another workbook and then deletes it.
What I need is a macro that when I try to change g1, gives me a warning if there is unexported data in cells g3:g6 (ie the sum of them is greater than zero) and gives the option of either continuing or stopping (presumably using a YesNo box).
View 3 Replies
View Related
Feb 9, 2007
Is it possible to use a formula to shade a cell dependent on a condition? I have tried an if formula (see below) but it is incorrect. =if('November 2006 SVOC'!B6>'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [red],if('November 2006 SVOC'!B6<'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [blue],))
View 3 Replies
View Related
Aug 29, 2012
My colleagues are working on multiple projects at once. The projects got different priority, so I'm looking for a formula that change the cell color if a person is working on it, depending on the project's priority.
For example if John is working on project: East(pri.1), South(pri.2) and West(pri.3). On East he got 5 remaining hours (cell E8). Then i would like that cell to turn red. For South green and West red.
View 5 Replies
View Related
Apr 11, 2014
I would like to conditional format an Object depending on the variance between 2 weeks.
If the value is > than 0% the arrow is Green and points upwards.
If the value is = to 0% the arrow is Yellow and points at at 90 degree to the left.
If the value is < than 0% the arrow is Red and points downwards.
Attached is a sample : rrow Conditional Format.xlsm‎
View 4 Replies
View Related
Nov 22, 2007
I want to change the colour of a cell depending on its value, when compared to another row of data identiified by a value in another cell. To try an clarify:
b3 = 1 b5=4 Because b3 = 1 then compare cell b5 with the row g1 as b3=g1 then depending on its postion set a colour
g1= 1 g2:g5 = 2,3,4,5,6
h2= 2 h2:h5 = 7,8,9,10
View 2 Replies
View Related
Dec 10, 2012
As seen from my attached file,
The colour of the percentage cell changes according to its percentage. I would like the S/N cells to have the same colour as the total percentage cells automatically. (Even when the percentage updates)
Secondly, for the cells under "Target", if the target dates are 1 day before today(the current date on a particular day) and the actual date is not filled, the cell fill will turn amber.
If today is on or after the target date and the actual date is not filled, the cell will turn red.
However, if the actual date is filled, the target date cell will be filled green, overwriting the above two condition.
FormattingHelpExample.xls‎
View 2 Replies
View Related
Apr 24, 2007
My head hurts from working on this all day. I have a large spreadsheet and in the top left corner I need to show the percent of vehicles that are not working out of our whole pool. I have a function that detects the color of the cell (Red is broke) and counts the total of red cells and then divides it by the total giving the percent and it is in G118 for Jan 1/2007, H118 for Jan 2/2007 etc. I made a function that will count Julian days from Jan 1, so for today I get 114. I know I need to display G + 114 columns but have no idea how to get that column name from this and always display the current one in the corner.
View 9 Replies
View Related
Jan 15, 2014
I inherited a worksheet with 70k+ rows of data. As shown below each row contains a record number(Col A) with the date(Col B) it was created and value(Col C). There are 5 records per day. You can see in the example below that Col A has the value 30 duplicated for each day. Is it possible to rename only the second "30" in Col A for each day?
Col A
Col B
Col C
10
1/14/2014
10
[Code] .........
View 3 Replies
View Related
Feb 10, 2014
I have 2 worksheets, 1 with a table sorted like this (in a row):
component | start date | end date | assigned to
Second worksheet with a table like a calendar with dates and people (dates in the columns and people in the rows), every component is assigned to each person by dates.
............ | 01/01 | 02/01 | 03/01
---------|----- --|-------|-------
person 1 | comp1 | comp1 | comp2
-------- |--------|-------|-------
person 2 | comp1 | comp1 | comp3
I want the assignment from worksheet 1 to worksheet 2 to be automatically. i will set the start and end date next to the component in the first table and assign it to a person/s and it will be automatically get filled in the second worksheet (the calendar) under the person/s and under the same dates as set in the first worksheet.
You can see a template here: [URL] ........
View 9 Replies
View Related
Mar 5, 2010
I have used the below code to insert a new row when the value in coulmn A change. I now need to evolve it so that the new row will contain a specific value depending on the changing value:
Before:
Column A Column B
one test
one test
two test
two test
three test
three test
After:
Column A Column B
one test
one test
Coz two............................
View 9 Replies
View Related
Feb 11, 2010
Complete and utter newbie here, so I will apologies now if what i am asking is a simple thing or complete nonsense..
I have a spreadsheet which has links in it to another file/spreadsheet.
What i need to do is update/edit those links so that they point to another file and cell reference. Can this be done using a macro? or is this something that cant be done using macros/vba?
New to all this so am just getting to grips with it..
View 9 Replies
View Related
Oct 21, 2009
i'm having trouble finding a suitable solution for a problem in excel, i have multiple columns with data, what i want as result, is to put first value from the first column in a new created column, after that the first value in the second column on the second positon of the newly created column and after that with select and drag over the two rows new created to folow my seqvece -> second value from the first column, second value from the second column an so on.
View 3 Replies
View Related
Apr 25, 2014
I have a massive excel sheet with 300,000 rows and 100+ columns. When reading back through my formulas, it can get very confusing. Example: "=(AZ9*5 + 1)+BH9/2 +(AP9*0.75)" I then have to figure out what each column letter is representing. Instead of this, could I rename a column so I can reference the variable name? Example:
Instead of =CONCATENATE(A4, " ", B4)
have
=CONCATENATE(FirstName4, " ", LastName4)
View 3 Replies
View Related
Jun 23, 2014
I have a cell which is referencing another (=Sheet2!A1) that and I want excel to update the reference by 50 rows each time (Sheet2!A51) .
View 8 Replies
View Related
Jul 25, 2008
I am using this formula
=IF($A5="Select","",MAX(INDIRECT(SUBSTITUTE($A5,"/","")&"m1440"&"!d:d")))
It looks for the sheet based on A5
It finds the max number from the sheet in Column D
This is what i would like to do please....
On this found sheet - Column A has dates in this format yyyy.mm.dd
I would like to enter a date in a cell (say for example AA5 on main sheet)
Can the formula above be adapted to:
Look for sheet based on A5
then
Look at date entered in AA5
then
use Vllookup to find row containing date in AA5 (making table range the ENTIRE SHEET found based on A5)
then
Return value from Column D
View 9 Replies
View Related
Dec 28, 2008
Is there a way to change a cell reference within a formula. ie: If the formula is =A1 (in reference to cell A1) and I wanted a new cell to have the reference =A10. Can I make a formula which was A1+9, and therefore lookup A10.
View 9 Replies
View Related
