Change Column Referenced In Formula Based On Date

Dec 12, 2013

I'm trying to create a list that references an existing data set where I have staff listed month by month and based on today's date, imports only if there is data in that column. I have figured out how to check if the cell is blank or not, but what I want to do now is change the cell that is referenced in the formula based on the date. Here is the format of the spreadsheet I'm working with:



In this case, the "Team 1" and "Team 2" and "Team 3" references what team they are on that month. If it is blank, they aren't with the company any longer. The formula I am using is intended to import this data elsewhere, and is formatted like this:

=IF(ISBLANK(B2), "", A2) - My understanding is that this checks to see if B2 has data, and if it does, it inputs the employee name (A3) in that cell.

My ultimate goal is to be able to change the column referenced after the "isblank" calculation based on the date. So if today is April 2013, I want it to check B2, but if it's December 2013, I want the formula to check J2. Is there a way to do this? I don't mind if it's two steps (like if I have to put the date somewhere in the spreadsheet in order to run the calculation), but ultimately it would be the type of thing I could do that would leverage the existing data set so that I don't have to maintain two different spreadsheets of information.

View 2 Replies


Automate A Date Referenced In A Formula Pointing To Another Sheet

Nov 14, 2005

I am trying to reference a date in a formula that points to a sheet name.

See Below.

November 11, 2005 ='11-11-05'!$v$35

How can I automate the date to pull from column A and place that value in the date for column B? Column B is the name of a different sheet in the workbook.

View 9 Replies View Related

Extend Formula As Many Data Rows As Referenced Column

Oct 31, 2006

I am after an automatic formula or function which calculates values for long lists instead of me dragging down the formula all the way to the end of the document.

Hence, I have a long list of data in columns A, B&C and I want the formula in column D to automatically be calculated all the way down when the list stops.

View 9 Replies View Related

External File(s) Referenced And File Links Change Based On Row Cell

Oct 22, 2009

I have attached the file I am working on. I am attempting to create a link to an external file based on the value of cells in column A. Then I would like to simply copy the formula down, lets say in Column B, the rows and as I do the external file reference will change depending on the value within the cell in Column A. I hope that I am making sense.

I am using Windows Vista with Excel 2007. The files will all be within the same file folder, however, there are hundereds of files so I won't be able to open them all for the indirect to work.

View 9 Replies View Related

Formula To Change Date In One Cell Based On Text In Another?

Jan 29, 2014

I am trying to put together a report tracking sheet, ie I have different test that take a different length of time to return to the office and I want to be able to see when a test should be back from the lab,ie:

A blood sample takes 1 day, a urine sample takes 3 days.

I need a formula that basically says that if the data in cell A1 = Blood, (and I have the test date in cell A2) then the data in cell A3 should says A1=Blood, A3=A2+1 to give me the test due date

I found the following formula in one of the other posts;


The problem that this will only work with two tests and I have more than two tests, I have fourteen and each takes a different length of time to process.

View 7 Replies View Related

Formula To Enter A Date In One Column Based On Date In Another?

Jun 10, 2013

is there a formula that will look at A2 and if the date in that column shows May 5, 2013, then in E2 it enters June 1, 2013? I would like E2 to automatically populate the first of the following month of A2.

View 7 Replies View Related

Formula To Pull Latest Date From One Column Based On Entry In Adjacent Column

Jun 6, 2014

Any way to construct a formula in excel that will look at a reference in one column and find the latest date from the data in an adjacent column for that specific reference?

Below is an exctract from a much larger sheet of the columns in question.

The result in the last column should be 21/05/2014 for anything with D.O.001 in the second column and 15/05/2014 for anything with D.O.002.

Date Decision agreed
Disposal Order
Latest Decision date for D.O.


[Code] ........

View 6 Replies View Related

Create Formula In Column B To Add 4 Days To Date Based Upon Column A

Apr 3, 2014

I have a form in which users will manually enter a date in Column A. I would like to create a formula in Column B which will add 4 days to the date based upon Column A. However, the 4 days should only be added to a select set of dates which I would like to specify. If the date is not found in this select set of dates, then the result in Column B should be the same date as Column A.

View 3 Replies View Related

VBA Formula; Sum Up Part Of A Column Based On A Date Range

Feb 24, 2009

I am trying to write a formula with some variables passed into it. i want to sum up part of a column based on a date range (i've got the range already).

View 3 Replies View Related

Replace All To Change A Referenced File Name

May 28, 2014

I am working on a spreadsheet that will be referencing approximately 20-30 separate workbooks which all have identical sheet / column structure and have a consistent file naming convention. ("###-YYYY") Since, this is referencing separate files and not sheets with this workbook, I cannot use INDIRECT. However, I should be able to use index / match referencing provide instructions for users to copy a set of cells down and the globally change the file name. The problem is that the file directory pops up with each instance and the user has to manually select or confirm the file.

There would be approximately 15 cells with a formula similar to this example and would want to change the two instances of "403" to a new 3-digit code, say "444". Then as each year cycles over, we'd want to change all instances of 2014 to 2015.

lagraffDownloads[403-2014.xlsx]Forecast Budget'!$D$15:$D$48,MATCH($P$2,'C:Users
lagraffDownloads[403-2014.xlsx]Forecast Budget'!$B$15:$B$48,0))

Any settings in Excel, or known tricks, so the user won't have to continuously click on the file and the find/replaces executes at each instance?

View 1 Replies View Related

Date And CONCATENATE (add The Date As A Referenced Cell)

Jan 8, 2010

I'm having a problem getting the date to show up when I add the date as a referenced cell to a formula using CONCATENATE. The date shows up as a serial value instead of a date format. The formula that I'm trying to get to work is in cell J7 on the attached file.

View 3 Replies View Related

Automatically Change To Proper Case Once The User Leaves Any Of The Referenced Cells

Nov 6, 2008

In my worksheet there are ranges A3:C37, E3:E37, J3:K37 and P3:P37 that all contain text that I would like to automatically change to proper case once the user leaves any of the referenced cells.

I have tried various codes form this forum and searched for hours on the net for a solution to do this but no matter what I do/try nothing works (for long)

Another forum user did help me out with some code but there was an issue with column C, L & O (which are set as drop down lists) and when the code was put into the workbook these columns stopped working and froze the app.

View 4 Replies View Related

Excel 2007 :: Change All Matching Value Based On Number Change In Column?

Feb 19, 2014

I have a situation that I cannot get my hands on. I have a set of Project Numbers in my Column A. (Will Try to post an example).

I need that everytime I change a number in my Column B that it will change all the numbers, in my column B, based on the matching Text in my Column A.


View 14 Replies View Related

Replace Cell References In Formula With The Header Of The Cells Referenced In Said Formula

Dec 22, 2009

Here is the scenario:

I need to reference the formula in D1 with the cells headers names.

In a perfect world, it would take

and produce:

View 9 Replies View Related

Date Recognition In Referenced Cell

Nov 11, 2009

I have the following two formulas in a spreadsheet, which sum and count, respectively loan information within a certain date range. Both of these are working correctly.

My problem is in substituting the dates with cell references for dates located in a worksheet with a table of dates. The dates from this table work with simpler formulas, but I cannot get them to work here, regardless of how many () "", etc that I try....

View 9 Replies View Related

VBA Code To Change Font Colour Of Cell Referenced Letter Strings Within Range Of Cells

Jun 13, 2014

I am working on a spreadsheet for work, and have managed to do everything I need to so far but I need to colour specific letter strings, certain colours within a range of cells (each letter string will only appear once on each sheet)

The strings I will be looking for vary depending on data entered so I will need to cell reference them

The strings that need colouring are in cells with other strings that must stay black (They cannot be separated from other strings due to the nature of the grid)

I need some strings red, some green, and some blue.

These changes should also apply to the whole workbook not just one sheet.

Is there a way to do this with the VBA code.

View 3 Replies View Related

Is There A Way To Use A Referenced Formula For Different Data

Aug 17, 2009

Here is a google doc of my issue: [url]

An Excel sheet is also attached.

I believe you have permissions to edit.

In H1:H4 I have 4 different functions. When the user specifies which function to use in E1 (add, subtract, multiply, or divide), I would like C1:C4 to evaluate the values in Columns A & B.

My problem is that the formula only uses the values in A1 and B1. When I copy the formula down the column, it still evaluates for the values in A1 and B1, not in A2 and B2, A3 and B3, etc...

View 14 Replies View Related

Return Results Based On Referenced Cell

Nov 21, 2006

I am looking at developing a table that has three possible options for different categories, being Yes, No and N/A. A yes score a certain percentage depending on what column it is, and the use of N/A changes the percentage of other scores. (i.e. A yes in column 5.1 = a score of 5, a yes in 5.2 = 5, a yes in 5.3 = 10 and a yes in 5.4 = 15. However if 5.4 is N/A then this amount is diveded amongst the remaining 3, i.e. 5.1 = 10, etc.)

View 3 Replies View Related

SUM Formula Not Summing All Referenced Cells

Sep 5, 2006

My problem is that when i used the forumla = SUM(Q11:AW11) to add up the range of cells it dosnt do it. It only added up 2 of the numbers in the range of cells. And missed out everything else. This forumla is being used to add up all the volunteer hours for volunteers for each month and for all the other months the formula worked fine but now its just being a pain.

View 8 Replies View Related

VBA To Custom Sort Data Based On Referenced List

Feb 17, 2014

Sort Example.xlsx

I have data in Sheet "Schedule" in range A8:C160 (including column headers), but note the length (rows) of the range is dynamic.

A8 = Customer
B8 = Load Time
C8 = Delivery Time

Columns B & C are formatted as h:mm:ss AM/PM, but Column B also includes text..."PRELOAD"

I would like a macro to sort the data based on column B, but the sort must follow a specific order, which is listed in Sheet "TimeSort", range A1:A50 (including header).

The custom order basically has "PRELOAD" sorting at the top, then sorting everything else chronologically starting at 6:00am.

The desired outcome would sort the range based on column B as follows...PRELOAD, 6:00:00 AM, 8:00:00 AM, 12:00:00 PM, 5:30:00 AM

Example is attached (desired sort is reflected).

View 14 Replies View Related

Column Number Referenced By Alpha Identifiers

Aug 27, 2013

How can you tell what a column number as referenced by the column alpha identifiers.

Ex: Column AN would equal what column no?

View 2 Replies View Related

Countifs Formula Not Working When Referenced To Another Worksheet?

Mar 17, 2014

I have attached a sample workbook which shows what I am trying to extract from sheet "Service Reminders 2014". In worksheet "Results" cells b11:e11 I am trying to extract the amount of vehicles with within age ranges provided that have a magic number attached which is pretty easy everything >0 is a proper magic number and also the amount of vehicles in each age range that have "Booked" associated within the range of "Service Reminders" Y2:AH5000, The formula works fine until I add the final criteria and then it give me a #VALUE!

View 14 Replies View Related

Formula: Looks Up A Chart Showing Scores Referenced Against Each Other

Apr 20, 2007

I have a formula that looks up a chart showing scores referenced against each other as shown below. (Scaled down version)
******** ******************** ************************************************************************>Microsoft Excel - Table Template.xls___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1B1C1D1E1=
ABCDE12006-2007Altofts JnrsCarleton JnrsEmpire ColtsHemsworth Terriers2Altofts Jnrs- 3Carleton Jnrs12-3-4-0 4Empire Colts - 5Hemsworth Terriers -Results
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

The problem is when the score is i.e. 12-3 the formula is only looking at 2-3. Hence this actually then shows as a defeat. The formula causing this problen is an array and shown as
Question. How can the formula be changed to reflect scores like 12-3 to be shown as a home win.............

View 9 Replies View Related

How Can A VBA Macro Find Which Cells Are Being Referenced In A Formula

Jan 15, 2008

I have a spreadsheet with a number of different formulae on it. I have a macro which I want to extract which cells are referenced in the formula in the active cell and then branch based on the which cell is referenced.

Is there a way to find the referenced cells without parsing the formula string?

View 9 Replies View Related

Prevent #REF! When Moving Cell Referenced By Formula

May 14, 2008

I have an IF formula on one worksheet that refers to another worksheet (ex: =IF(Page1!$A13="";"";Page1!$A13). The problem is when I move data from one cell to another the formula becomes #REF. (ex: =IF(Page1!#REF="";"";Page1!#REF)).

View 6 Replies View Related

Populate Column Based On Change In Data Of Another Column

May 30, 2014

I am trying to find a way to populate a column based on the changes in the data of another column...

For example, I want to fill in the code when the customers cost changes, I want a numeric code to populate & change. So for the first 5 lines, the code could be 21, and then when the cost changes to $37.51, want the code to change to 22.

CodeCustomer's Cost

View 7 Replies View Related

Date Change Formula

Apr 30, 2007

I want to change the date on my spreadsheet which runs from May 07 to Apr 08 by just inserting one cell, how do I complete this action. The months are in seperate cells.


View 8 Replies View Related

Change Data Based On Date Field

Aug 26, 2009

I have a file, a portion is attached, where I want to paste the value of the formula (in this case, just that of the external data) once it has grabbed the data from an external source.

If the first time I open the file is Jan 15, I want it to grab the data in columns C, E and G from Jan 1 to Jan 14 and paste each cell's value in the same cell. Any data on Jan 15 or past Jan 15, I don't want the value (hasn't been collected yet).

If I open the file again on Jan 18, I need the data from Jan 15 to Jan 17.

I have this macro which works well for grabbing the data and pasting the value.

View 3 Replies View Related

Change Tab Color Based On Cell Date

Oct 1, 2011

How to Change The Tab Color Based On A Cell Date ( Thursday & Friday ) Green Color, Rest of the week blue.

View 2 Replies View Related

Color Change Based On Past Due Date

May 9, 2013

We have a assigned date, a due date and a date resolved. what I would like to do is any time the date resolved is past the due date change date resolved background to Red. I was playing around with conditioning formatting but can not seem to get this right....

Col G H I
Date Assigned
Date Due
Date Resolved


View 7 Replies View Related

Copyrights 2005-15, All rights reserved