Fixing Cell Reference In A Formula During Macro VBA Script
Apr 27, 2014
I have a template workbook and a macro that finds lines of data in a source workbook and copies parts of it to the template workbook. It then deletes left-over blank rows, leaving a summary section just below the last row. There is a formula in column C of the summary section that gets messed up slightly by the deleted rows, specifically a cell reference within the formula that refers to data on another sheet within the same workbook.
On the template the original formula is in cell C1667 of a sheet entitled "2014" and reads...
[Code] .....
After the macro is run, lets say that for argument sake, we end up with 13 rows of data that got copied across to the template, the data exists in rows 3-13 and excess rows have been deleted. The above formula now sits in cell C16, and reads....
[Code] .....
The last cell reference in the formula has not updated itself to row 13, I believe because it refers to another sheet.
I would like to add in a line of code at the end that will find which row the formula sits at in column C after excess rows have been deleted, and correct it, so that for this example, it would end up being this....
[Code] .....
The formula will always be in columns C but what row it ends up on will vary.
First need to create a "Text" Folder in your desktop, then try running the macro. It will create a number of text files inside the "Text" folder.. And they contain the used cells from each row. It's supposed to create 982 text files, with the text name referred to the first column. Problem is, if you try running it, it will only create around 53 text file
how can I combine the Formula =(SUM(A2:A100))/(SUM(B2:B100)) with setting a value of the cell D2?
What I want would look like this (in column C):
=(SUM(A2:A100))/(SUM(B2:B100)) with the condition that D2=0 =(SUM(A2:A100))/(SUM(B2:B100)) with the condition that D2=100 =(SUM(A2:A100))/(SUM(B2:B100)) with the condition that D2=200 =(SUM(A2:A100))/(SUM(B2:B100)) with the condition that D2=300
and so on.
The point is that the values A2 to A100 are dependent on the input value in D2.
This question would be much easier to get if I could copy paste my sheet for a look!This sheet is being coded for a test that will be taken repeatedly.
B1 to Z1 are various variables(1,2,3,4...25) while B2 to Z2 are the status of each variable(yes,no).AA2 gives me the accuracy of my test(14/26).I have used 2 countif formulas for this with a "/" in between.A1 asks current instance of test which is filled in A2(1st, 2nd, 3rd).
What I want to do is each time the user tries to change the param in A2, cell AA2 gets copied somewhere with the Test instance(Instance 1-14/25, Instance 2-12/25, Instance 3-20/25) so that I can make a dynamic bar graph out of it i.e. the bar graph tells me how I'm trending in my results inclusive of the last taken test.
I'm confused cos while I can make a cell pick up the value of another it takes the most current value with no...memory of the last test.
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
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 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.
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.
Maybe it's not as complicated as i'm thinking, but i'm trying to figure out a formula (or macro) that will let me basically fill in a blank from a (nearly) irrelivant second worksheet. The only similarity the two will have is a PO number, or in my picture diagram, it's a letter. The worksheet that i'm trying to fill in the blank from has maybe 1500 entries, the worksheet i am referencing to has roughly 11,000, so sorting it would make no difference and the columns would be very inconsistant with eachother, but the rows will be similar.
I want to start a formula in Sheet one, that looks at the Letter in that same row, finds that Letter in Sheet two, and gets information from the same row but a few columns over. I might be explaining this poorly, so here's a picture of what i'm referring to. I'm trying to fill in F6 in Sheet 1 with information from F2 in Sheet 2.
I have created a workbook that generates new worksheets with unique identifiers as tab names. I.e. On a "Create New Sheet" worksheet, input three pieces of data, click "Create New Sheet" and this data is applied to a template sheet, macro continues running to copy the template and rename is with a unique SKU.
My problem is as follows: I have yet to figure out how to generate an Index page that is updated as each new sheet is added with the following:
a) Cell containing the SKU and a hyperlink (or some other link) to that particular tab, and b) Beside this cell, pull the contents of certain cells from that SKU's tab (such as description, but also the contents of calculated fields on that SKU's tab like current quantity and current price).
I would like the Index to be live so that if data is changed on the SKU's tab, it flows through to the Index......
I am using the DOB formula of =DATEDIF(AA19,NOW(),"y") & " years, " & DATEDIF(AA19,NOW(),"ym") & " months, " & DATEDIF(AA19,NOW(),"md") & " days". In the next cell I want it to look at the previous cell and determine if "older than 2 years". I have tried the "search" and isnumber, but I keep getting the wrong answer.
I need to display cell AV13 from sheet 2 on sheet 1. To do this I go to sheet one and in the cell I type =Sheet2!AV13 and voila I get my value. My problem is AV13 is determined by two list boxes, one finds the column AV (named the cell that holds this value as StageColumn) and the other finds the row (named SubCatRow). What I want to do is have a cell that gets this information from Sheet 2. I tried to concatenate the string, but all I get is the text.
The concatenation string was =concatenate("='Sheet2'!",F25) I have F25 containing the formula =Address(StageColumn,SubCatRow)
i have a table of data and a formula at the right which gives the max of that data and would like to get the row header and column header of the cell that is giving me the maximum value
e.g on the below the maximum value is 24346.3 and i would like to have in another cell Australia Diversified REITs
I am trying to figure out how to reference the formula from another cell. For example, say cell A1 has the formula =indirect(address(row(),2)). Assume B1 equals 10 and B2 equals 20. The formula in A1 would yield 10. How can I make A2 reference the formula in A1 so that A2 yields 20? I need it to actually reference the formula in A1, simply copying and pasting A1 or dragging A1 will not suffice.
I have a column (A1:A26) with a series of numbers which I want to multiply by a second variable in another column (Sheetb:C1). So I write a formula for a new column - =A1*Sheetb!C1. I want to multiply each value in (A1:A26) with just the value from Sheetb:C1. However, when I drag the 'fill series' pointer down, it automatically increments the cell its is multiplying by i.e. 1st cell is A1 X SheetB!C1, 2nd cell is A2XSheetB!C2... However I want it all multiplied by SheetB!C1.
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
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.
I have a formula to extract the worksheet name and add the "!" to it so it can be used in a copied formula... I'm just having trouble writing the formula to reference the text in the cell with the worksheet name. If it's easier, I also have the list of worksheets without the "!" - e.g., Sheet1, Sheet2, Sheet3, etc...
I want the formula to calculate, in my example, the sum of 2 cells on a different worksheet.
So finally got my sumrpod working using this formula
=SUMPRODUCT(1*('Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$C8:$C10000=A5))
Is there a way i can link 'Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[South Pac Load Tracker - MAR14.xlsm] to a cell so that when the next months tracker is created and the filename changes i simply change the filename in the cell which im using to reference the filepath/filename.
e.g A1 = South Pac Load Tracker - MAR14.xlsm
Formula would read =SUMPRODUCT(1*('Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[' & a1 & ;]SPECIALS TEAM NZ'!$C8:$C10000=A5))
I have one coloumnA with formula =IF(F4="","",(H4-F4)) ------ the result I want to use for reference in which I am unable to use the result value using below formula. I have one more cell B with month & date
Currently using formula =SUMPRODUCT((TEXT(A4:A1501,"mmmyyyy")=$B4&$C4)*('Credit Cards&Loans Transactions'!J4:J1501)) Cell B4 has month & C4 has year.
J4:j1501 cell has the formula "=IF(F4="","",(H4-F4)) " , I want to use the result of cell in above formula.
I am trying to identify some combination of formulas or functions that will allow me to adjust the cell reference within a function based on a value in another cell. For example, I have a series of values listed from cell C50 to cell W50. I want to use a function to calculate the internal rate of return over a range of the cells: =IRR(C50:W50) So far, so good.
However, I want the size of the range to be determined by a value entered in another cell, let's say B2. If the value in cell B2 is "5", I would want the formula to consider only the first five columns: =IRR(C50:G50). If the value in cell B2 is "10", I would want the formula to consider only the first ten columns: =IRR(C50:L50).
I need the cell reference for a formula to + 5 each time I copy down, however, it seems to only + 1 no matter what I try?! The formula is a simple =sum(A4:A8). I would like to be able to drag it down so that the next one states =sum(A9:A13) and so on.
In the following formula, I would like to replace STARK with the value in cell D33. I've tried a couple of different things using &s but no luck yet. I think I'm just missing something really simple.
I need to use the result of a simple calculation in one cell on Sheet1 (=A1+2) as the row in a cell reference on a different sheet. eg =Sheet2!A"n" where "n" is the result of formula.
I have a pretty elaborate spreadsheet for my bills. One cell adds up all the bills due during a specific pay period using named ranges. The formula looks like this:
It works, but when I have to populate cells for upcoming pay periods, I have to go in an edit the dates manually. I'm lazy, and want to simply reference a cell with the date in it instead of typing the whole date out in the formula. What I'd like to do is this:
Is it possible to use a formula to calculate a cell reference within a formula? e.g. is there some way I could reference cell C47 in a formula by saying C(40+A2) where A2 contains the value 7. I often want to sum a certain number of cells in a list, from the first one to the nth one and need to be able to calculate rather than hard-code n.
