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..
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?
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 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 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
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 RelatedI 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
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?
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
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 running an ordering sheet that I need to attach reference numbers to, no format for the reference number has been determined.as yet. I need this reference number to change when the 5 digit number changes, see example attached.
Capture.jpg
I 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.......
Change "refer to" in Named Range. I have a named range myrange referring to A1:A5. I have a condition for B1, if B1= annual then myrange should refer to C1:C5 and not A1:A5..
View 2 Replies View RelatedI have an Excel workbook with multiple worksheets. One of the worksheets has a live data feed for stock updates. I extract data from the live feed (it's in multiple sentences) and pull it into 6 columns. In another worksheet, I pull those 6 columns into the sheet through the use of Index / Match. That second sheet has additional formulas that essentially analyze the data. As the data refreshes, the existing data moves down the column.
What I didn't think about was the fact that at times, a stock may be listed several times because different brokers are offering updates. Because I used Index / Match, it will look for the first match from the top and display that data in the relevant columns. Unfortunately, some of the data needs to stay with the matching row (and stock ticker) but because of the way I wrote the formulas in Index / Match, it just finds the first matching target and displays that data.
I can correct this by using an absolute reference ($) but I can't find a way to "copy down" formulas using absolute references. I also have one column of cells that contain 1 absolute reference and one relative reference.
I'll re-write this manually if I have to but I have 1,800 cells to change.
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 Relatedwhy won't this change event work? Macro Mail_with_outlook keeps sending no matter what.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("a3").Value 200 Then Exit Sub
End If
End Sub
I have several Excel workbooks that suddenly has converted to numbers for both columns and rows. Sheets that were saved and correct yesterday, upon opening today, are now numbered rather than number and letter. Any of my formulas now reference a RNumber and a number when citing location.
View 3 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?
VB:
Sub RenameTabs()
For i = 1 To Sheets.Count
If Worksheets(i).Range("C1").Value <> "" Then
Sheets(i).Name = Worksheets(i).Range("C1").Value
End If
Next
End Sub
I change my tab names with the above but than my hyperlinks breaks.
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?
