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 RepliesHow 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?
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 RelatedI 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?
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 Relatedneed 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 RelatedI am using below formula to make cell link i m using below formula in column C
I want to copy this formula to range("C4:C2000"), now I want after 43 row ( in cell C47 =(=INDIRECT("V5")) change the V4 to V5
and
cell C90 = (=INDIRECT("V6"))
cell C133 = (=INDIRECT("V7")).......and continue till C2000,,, i mean after 43 rows it change the V4 ot v5, v6, v7, v8, v9.......
I have a set of formula in column C to H. I have set a formula as sheet1+sheet2+ like this for about 24 sheets with different reference of cells. I want to change in the entire range of cells C to H as =sum(sheet1:sheet24!b04). The reference what I have mentioned changes in different cells.
View 9 Replies View RelatedIs there an easy way to drag all the references to a cell in a formula quickly e.g If i have a formula like this is;
=IF(A1>0, A1+1,A1-1)
and want to drag all the references to the cell A1 to another cell in one go as opposed to one at a time?
I have a file that summarizes data by country and by month but then another file with a yearly summary. I need to pull in the same cell references for each month but using each tab's name: 'Jan' tab, 'Feb' tab etc. The problem is every time I tell it to find/replace the tab name, I have to select the file from its location in a browsing box every time. I have also tried the indirect formula without much luck and because I also had to add all the month summary tabs to the original file to get the indirect formula to work. I would rather keep both files separate.
How do I copy over formulas in the Jan column and change the tab reference to the Feb tab for the Feb column without having to change things 200 times for each countries data?
I was wondering whether there is an easy way to change a formula containing a named range back into it's original cell reference in VB. For example I have a cell saying =cogs2008/revenue2008 and I want to change it back into =A2/A3. Any ideas?
I have a range named as follows in a formula:
BaseCase!$O:$O
I have a data validation from which the user can choose another tab.
So, how do I change the reference above to:
TabName!$O:$O
so that TabName refers to the text string in cell B2 (data val. box)
I tried to use TEXT to no avail. I imagine there is a simple solution, but I am at a loss.
INDIRECT perhaps?
In Sheet1,
C14 = Sheet2!D2
C15 = Sheet2!D3
C16 = Sheet2!D5
What i want to do is to change "D" to "E". Next time i run it, it will change from "E" to "G".
I want a macro to do it, so that I don't have to change it manually one by one. For some reason, when i try to use .formulaR1C1, the reference change, and I couldn't get it to work.
I'm dealing with forecasting invoices. The main column 'BALANCE' has a formula referencing the previous cell and then adding or subtracting invoices (ENTRY). The problem I have is that I'll put values in ENTRY that I expect to come in at some point but as they arrive I need to change the order of the cells. So I'm constantly changing the order and then it changes the formula in BALANCE. I tried absolute cells (and even found a work around way to flow absolute cells by flowing then viewing formulas then using the replace function but that didn't work.
So my formula in BALANCE is:
=$I$173+$E$174-$G$174
but if I move G174 to another cell I'd like to keep the BALANCE formula to stay G174 instead of following the move.
i have the table of holiday days with 7 columns(7 countries).Each column consists of 10(or more rows (holiday days).
I have a formula for calculating start day for a deposit which has to be renewed.
formula is =workdays(end date of deposit,-2,A1:A10).
-2 in formula is because all the trades in the market are traded Today+2days.
A1:A10 is column of holiday days.
This formula calculates the start of deposit excluding weekends,holiday days.
BUT I have more then 7 countries and I would like to have a macro so when in cell F10 I type SWEDEN,my formula automatically use the column with holiday days B1:B10 for sweden.
When I type Finland, the formula uses column C1:C10 with holiday days from Finland respectively.
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
i want to change the table_array reference without changing formula in every worksheet. I tried using a new worksheet and naming it the same as what is referenced in vlookup table_array but it messed up all the data.
I have to use a different worksheet every month so need a way to change reference OR how to change array data without messing up the worksheets with the vlookup
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.
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..
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 RelatedI 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)
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
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.
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 RelatedThe 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.
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.
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)
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),"")
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.
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