Change Formula Cell Reference Based On Another Cell Reference
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
ADVERTISEMENT
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
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
Apr 28, 2014
I have a workbook that contains a column, G, which includes a formula that calculates distances (based on lat, long coordinates) to a training site ("TS" going forward) from other sites within the same district as that TS. There are numerous TS's in the company, usually 1 per district (but some districts do not have a training site), and I am trying to find a way to change my distance formula that will automatically detect which site is a TS, and then calculate the distances of those other sites in that same district to their respective TS's.
As it stands, I have to manually change an absolute cell reference every time I encounter one of these TS locations in the file (designated by a "TS" in Col F), and rerun the distance formula down the column. I would hate to have to do this several hundred times.
Attached is a sample file. Again, Col G contains the formula for distance calculation, and you will notice that the absolute cell references change each time a TS is encountered -- I have been doing this manually. Additionally, I would be curious to know if there is a way to spot any Districts that lack a TS, as in District 493, and specify that in Col G.
View 5 Replies
View Related
Apr 29, 2014
I have an excel workbook that has many spreadsheets (each one sheet has a client name)I have another excel workbook that has these client names on one sheet (on a list) and next to every name I have a number (i.e total turnover of the year).
In the first workbook (where every client has his sheet (tab named after the client) I want to have a cell that equals to the sum of some cells on the other workbook, that refer to the specific client
(it looks like this ='[comissions NF 10-14.xls]comissions 14(auto)'!$J$81+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$197+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$313+'[comissions NF 10-14.xls]comissions 14(auto)'!$J$429)
I want this sum to be added to every sheet of this workbook. each sheet refers to a client, so $J$81, $J$197 etc must be changed for the correct cell that refers to the name of the client. The tab names are alphabetical and so s the list.
Is there any way to do it, without re-entering the formula to each one?
View 1 Replies
View Related
May 26, 2009
I'm working on setting up a chart. The formula I'm using for cell B21 is this: =ROUND(F3,0) & " " & IF(E3<F3 ...
View 2 Replies
View Related
Feb 15, 2010
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
EXAMPLE:
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
View 14 Replies
View Related
Feb 8, 2010
need to do to the below code so that when i drag the formula down it changes the sheet number....sheet1, sheet2, sheet3 and so on but keeps the cell reference the same?
View 4 Replies
View Related
Jun 23, 2009
HTML Imagine the following table (Ax, etc is the cell position):
A1=John C1=Name D1=Date E1=Version F1=IP
A2=10/2/2008 C2=A1 D2=A2 .... ....
A3=Client version 7.01 C3=A1+5pos D3=A2+5pos
A4=172.24.3.5 C4=A1+10pos D4=A2+10pos
A6=Michael
A7=10/2/2009
A8=Client version 7.03
A9=172.24.3.80
A11=Susan
A12=2/2/2007
A13=Client version 7.02
A14=172.24.3.200
I have a table of data on the left side and I want to re-organize just like the right side. Let's take C2 as an example, the value for that cell = A1;
Now, C3 in the other hand, the value for that cell = A1 + 5 positions down;
C4 = A1 + 10 positions down;. The same type of formula applies for the remaining columns on the right. This seems like an easy solution, but it's not easy when you don't know it... :
View 4 Replies
View Related
Aug 14, 2006
I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.
Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following
View 3 Replies
View Related
Feb 5, 2010
i am currently using the macro below to import text files into a spreadsheet. Currently, it begins the import in cell A1 which is what I recorded it to do. how do I change the code to begin the import on the active cell?
View 2 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
Jan 27, 2012
Is it possible to change hyperlink as per the cell reference changed in a cell. for example :- in cell D2 the apply a match formula to find out a cell reference (eg "A"& Match function based on info type on cell A1) Now I want to create hyperlink as per the cell reference mentioned in cell D2. suppose I type Red in A1 and D2 give me the cell reference A51 than automatically Hyperlink create for A51. and it continiously changed whatever i type in A1 and what cell reference is showing in D2.
Sheet1ABCD1Type Abbrivation12Full FormACell AddressA13Is it possible that I click on Cell D2 and it goes to particular cell 45Abbrivation6Short CodeDescription71A82B93C104D115E126F137G148H159I1610J1711K1812L1913M2014N2115O2216P2317QExcel 2007Worksheet FormulasCellFormulaB2=VLOOKUP(B1,A6:$B$1000,2,0)D2="A"&MATCH(B1,$A$7:$A$1000,0)
View 3 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
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
Apr 7, 2007
I am creating a schedule to use for our helpdesk - it has pre-defined tasks that need to be assigned each day - easy enough to accomplish with a formula. And I've done this by using this similar formula on each line of the tasks (This first task is based on a manual entery into one field (B19))
=IF(AND(B6="Liz"),"Paul",IF(AND(B6="Paul"),"Mark",IF(AND(B6="Mark"),"Diane",IF(AND(B6="Diane"),"Jeff",IF(AND(B6="Jeff"),"Dan R",IF(AND(B6="Dan R"),"Chris",IF(AND(B6="Chris"),"Liz","")))))))
So, B4 is one task (phones) and the formula above (B6) is from another task (tickets) and then a third task (admin) looks at the value of B5 and uses the same formula above, etc.
However, I want to be able to change the value of a single cell, based on the (manual entry) value of another (out of task) series of cells (i.e. Sick (B35) & vacation (B36)) - and I don't want any of the other task cells(in the C cell series of 'tasks'), that are basically linked together by the value of the other cell, to change. Thus, in turn, would mean that the same person would now be in two different cells, and thus is creating a circular reference.
Let me spell this out a bit clearer.
The first manual field is B19 - If I enter 'Chris' into this field, then field C5 matches it. Then field B6 uses the formula above to figure it's value; as does B7 thru B12.
Now, If I put another manual entry into 'Sick' (B35), then I want whichever task that person is assigned to (for this example, we'll say I entered 'Jeff" into Sick, and Jeff happens to be on 'tickets' that day) to change to whoever is in cell B12 - but I don't want any of the other cells to change - but, since they are all dependant on each other, this doesn't seem like a possibility.
I've tried creating a count of names in the Sick or Vacation fields and then added this to the formula
=IF(AND(C5="Liz", B38=0),"Paul",IF(AND(C5="Paul",B41=0),"Mark",IF(AND(C5="Mark",B40=0),"Diane",IF(AND(C5="Diane",B40=0),"Jeff",IF(AND(C5="Jeff",B43=0),"Dan R",IF(AND(C5="Dan R",B37=0),"Chris",IF(AND(C5="Chris",B45=0),"Liz",B12)))))))
B38 being Pauls count; B41 being Marks, etc - so as long as their count equals zero, they are 'eligible' to fill that slot.
B12 is the last person on the schedule.
View 8 Replies
View Related
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
View Related
Jun 16, 2009
I need a code which will take a lump of data, B2:AW366 and cycle through the data in a single cell (A1)
So to clarify Cell A1 should cycle through:
B2
C2
D2
E2
...
...
...
AU2
AV2
AW2
B3
C3
D3
E3
.....
.....
Continue until cell AW366 reached.
View 2 Replies
View Related
Oct 22, 2013
When ever I update my external CSV file and hit refresh all data, I get #REF! Errors. The CSV file has a table that changes daily. Thus the "A" Column has a different number of rows depending on the day. The problem is that Monday the CSV file will contain 700 Rows, while on Friday it will have 200 Rows. I end up with a lot of #REF! Errors friday. How do I write the code so that (A561 for example) does not change regardless if it can reference the target cell or not? I even thought of making a macro that just copied the formula into all of the cells after each refresh, but there must be a better way.
Example code
=IF(A561>0,IF(Start!$H$2="Monday",'calculations-mon-sat'!O561,calculation!O561),"")
=IF(#REF!>0,IF(Start!$H$2="Monday",'calculations-mon-sat'!O567,calculation!O567),"")
View 2 Replies
View Related
Mar 11, 2009
I have a table that displays data from another worksheet. This is what the cell reference behind the table look like:
View 2 Replies
View Related
Jul 20, 2014
I have a workbook with 1000+ worksheets, all of which have 3-letter names. On a master sheet, I would like to make a query of how many non-empty cells there are on a subsidiary worksheet. This works:
Code:
=COUNTA(ABC!A:A)
What I'd like to do from time to time is input in column A a varying set of 3-letter worksheet names, say
AAB
ABC
CDE
And have a formula in column B that converts this to
=COUNTA(AAB!A:A)
=COUNTA(ABC!A:A)
=COUNTA(CDE!A:A)
I've learned that simply substituting the cell references A1, A2, A3 for AAB, ABC and CDE doesn't work. What do I need to do to achieve this?
View 2 Replies
View Related
Apr 25, 2014
Let's say I've ended up with the number 8 in Cell D4 for example. Is there a formula that can return the letter "G" (The 8th Column) so I can use it in future cell references ? If so, let's say we store that in Cell B5. How do I now refer to a cell in a chosen Row of that same Column by reference to Cell B5 ? For example if I want to refer to Cell G33 can you refer to this Cell in some form like Cell(Contents of Cell B5;33) ??? Don't want to use R1C1 type references if possible.
View 9 Replies
View Related
Oct 28, 2009
I need a macro to do this small task for me. Preferably it should execute when I have selected a cell and press a command button. Here's the way it should work:
Copy the cell I have selected
View 3 Replies
View Related
Jun 20, 2008
I am a complete newbie with the camera tool. Is there any way to make the camera tool dynamic so that the cells the tool is referencing can change upon the click of a button or some other form of switch? I am creating my first dashboard and this is the last step for my report to be completely dynamic.
-----------------------------------------------------------------------
Never mind, I apologize i just figured this one out...
Create a user defined formulaIn the UDF Create an if statementThe if statement's condition is the logistics in the shift mechanismThe value if true is the first group of cells you want in the snap shotThe value if false is the second group of cells you want in the snap shot, The range you want to toggle betweenRename the cell reference of any snap shot you have taken with the UDF name. In my example my UDF is "Dude" so when you select the camera tool it shows =Dude as the cell referenceReference a scroll bar to the conditional cell you selected in the if statement or choose statement. This is the shift mechanism
If you want to toggle between more than 2 sets of data use =Choose instead of =if
View 3 Replies
View Related
Mar 10, 2013
I have a list of headings and items and I have a set of formulas that work out depending on the heading what items are listed.
Say theres 10 items and the heading starts at C4 and that heading has 10 items, so it puts "C5" as text in G1 and "C15" as text in G2 so i now know my cell range of items
How can i use the text in those cells to put in a formula to call that as a range?
If I use the indirect formula it shows me the value of the cell, but im after using it to reference the cell
View 4 Replies
View Related
Jan 29, 2014
I need to make a cell display the contents of the cell immediately below it, regardless of if there are rows inserted at the referenced row or above or below it on the worksheet. ALSO I need to do this at several additional cell locations down the worksheet. Such as : A1 displays A2, A4 displays A5, A7 displays A8 etc.
Example: I want A1 to display whatever is in the cell immediately below it. In this case A2. I need to be able to insert several rows at row 2 and have whatever new value is now in the A2 position displayed in A1.
After the rows are inserted I need the cell that was in position A4 to continue to display the contents of the cell below it.
I tried using the formula in A1 of: =INDIRECT("A2") and it works good except when I insert the rows my similar formulas below the area where the rows were inserted continue to refer to the specific original cell and not the one positioned now below it.
View 3 Replies
View Related
Oct 1, 2011
Version: Excel 2007 WinXP
I'm basically looking for something almost like an inverse function to INDIRECT. This function would first look at a cell's formula as a text string, parse out the first valid cell reference in A1 format, and return that cell as a text string.
Detail: I have a spreadsheet with cells that point to other values. I would like to get only the row number from the first cell reference in the formula residing in a given cell. For example:
Suppose A1 has the formula =AL267. and A2 has the formula =SUM(AL94:AL235)
I would like a formula in B1 that returns the text string, "AL267" so that I would know this is the first reference.
Ideally it could be dragged down to B2 such that it returns the text string "AL94" (and not "AL235") because AL94 is the first cell reference in A2's
Currently I am copying the formulas after hitting ctl+` and pasting that text into a text editor, followed by text operations to manipulate the results into the desired values. Any solution that didn't involve going out to notepad.
View 2 Replies
View Related
Jul 25, 2013
I have run an INDEX and MATCH formula to give me a resultant cell reference number. I now want to copy a cell to this specific cell reference using another formula.
For example the data I want to copy is in cell A1 and in cell A2 I have the result of the index formula (B1).
I know how to manually do a formula for this but wanting to know if there is a uniform formula that I can use that looks at the formula result of cell A2.
View 5 Replies
View Related
Aug 18, 2009
I am wanting to convert a cell reference text
"=$A$1"
to an actual cell reference
=$A$1
Manually I can go through each cell and click F2 + Enter and Excel automatically changes it.
I have tried recording a macro whereby I click through each cell with F2 + Enter but the VBA writes the actual formula "=$A$1" rather than the process. This does not work as the cell reference is variable.
I'm NOT wanting an external cell to convert it for me
i.e. INDIRECT(CELL)
because I am wanting to copy the answer to another independent spreadsheet
I'm NOT wanting to paste values
i.e. return the answer from cell $A$1
because I want the cell reference to remain within the cell.
View 3 Replies
View Related
