I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
View 9 RepliesI set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.
All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.
=COUNTIF(E14:Z14,"*Y*")
=COUNTIF(E11:Z11,"*DI*")
I have a Formula = SUMIF('Master List'!B3:B190,AE197,'Master List'!AG3:AG190)
When I copy this formula the next line is as Follows =SUMIF('Master List'!B4:B191,AE197,'Master List'!AG4:AG191) Is there something I can do to in order to make so that when I copy the formula is would read =SUMIF('Master List'!B3:B190,AE198,'Master List'!AG3:AG190). Elliott Auto Merged Post;Nevermind I used the formula = SUMIF('Master List'!$B$3:$B$190,AE197,'Master List'!$AG$3:$AG$190)
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
To simplify matters each day is exactly 10 rows (including header).
I already have code in column D that populates each D row IF it is the first unique occurance of column B for each day.
The forumla in column E for rows 2-10 put a daily total by each unique occurance.
The problem:
Since I am using absolute references in E2-E10 to contain the totals for that day if I copy E-2 -> E10 to E12-E20 the totals will be incorrect.
If I use relative references in the formula it changes the range for the day which is also produces incorrect totals.
Question:
How would I copy the formulas in column E to the addtional days so that the ranges follow for the specific 10 row day that they are intended?
I am trying to make a formula use an IF statement to identify different cells on a different sheet to pull raw data from.
However I've never tried formulas involving multiple sheets before.
This is the formula i get a circular reference for =IF('Store Input'!I6:J6=Lewis,(('Store Input'!C6:D6/1.2)/100*0.75))
I am not entirely sure what im doing thats causing confusion but ill explain the desired effect.
If a Cell has a value of "Name" then take data from cell "X" and do calculation /1.2 /100 *0.75 on a separate sheet.
Further more i want it to apply to multiple cell possibilities. so like the one above except repeated on different cells.
So as well as the above, i want it to perform the same operation for a set of cells further to its right and so and so forth.
I've read several threads about switching between relative, absolute, and
mixed references across several cells however these solutions seem to result
in formula with all relative or all absolute or all mixed.
I need to change the formula in lots of cells with a mix of types of
reference. e.g. I need to change "$E$4*AD$2" to "$E4*$AD$2" & would prefer
not to have to go though each of the cells with F4!
How to change from absolute references to relative references.
Example :
ws.Range("G" & NextRow).Formula = "=" & Range("H" & NextRow).Address & "+" & ws.Range("I" & NextRow).Address
This code return the absolute references---> =$H$365+$I$365
, and i want change to relative references, like this ---> =H365+I365
Is there a way to absolute reference multiple cells at one time?
View 5 Replies View RelatedI am migrating a bookkeepping spreadsheet from Quattro v12 (c2001) to Excel 2007 and have hit a stone wall over relative 3-D references.
In this workbook each sheet holds data for one month, and this includes some lagging-12-month calculations -- that is, summing a range on a single cell for the preceding 12 sheets (including this one).
Unexpectedly, this formula:
=ISUM('201206:201305'!M6)
gives absolute references, even though I put in no dollar signs after the sheet names. This means it gives the wrong answer when copied to the next sheet to start a new month.
I can find no reference at all to converting the above formula from absolute to relative reference.
Below is my code which isn't formatting the cells it's suppossed to. It looks like it isn't doing anything. I think the issue might be with the highlighted section of my code, but when I go to "Manage" my rules for conditional formatting, excel references the appropriate cell under the "Applies to" section. I am using relative cell references for for the majority of the rest of the code and this section follows a section that selects the correct cell for this conditional format.
Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=""ABS()>.005"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority[code].....
Would be a massive time saver but can't find anything here or on Google on how to do this.
View 1 Replies View RelatedI have two columns. The first is imported NAMES, ADDRESSES, AND CSZ on three lines, repeating over and over again. Several hundred names, addresses, csz. The SECOND column is a formula which picks up ONLY the text name in the first column. The SECOND column will just be one NAME after another, so the formulas will SKIP two rows in the first column. HOW do I copy down / propagate relative formulas to do this
View 4 Replies View RelatedFormula: B2+C2
In columnD I want to reference the "formula" and have it calculate based on the values in whatever row references the formula.
As it stands I can only get the formula to calculate within the same row.
I have a formula that references data on another spreadsheet with multiple worksheets. I have a new worksheet for each month (Jan, Feb, Mar, ...).
How can I copy formulas in series that keep the cell references the same, but change to the appropriate month. So for the forumula below, I want to fill a series down in a column to represent each month.
Right now I have to go in and edit it to change the month to the next month (i.e. change Jan to Feb.)
An example of the formula is below. In this case, I would want to fill the series down a column and have the months update in series but keep the cell reference the same.
=SUM('[New 2008 sales report linked.xls]Jan'!$M$19,'[New 2008 sales report linked.xls]Jan'!$N$19,'[New 2008 sales report linked.xls]Jan'!$Q$19,'[New 2008 sales report linked.xls]Jan'!$L$47,'[New 2008 sales report linked.xls]Jan'!$K$47,'[New 2008 sales report linked.xls]Jan'!$S$47)
I am trying to do a simple copy and paste of number of cells A42:H76 to another sheet (in the same workbook) and have all the references stay when pasting to the new sheet. Instead when I paste it I get #Ref and the cell formulas no longer point to the right cell (they are all off by the same number because I did not paste the cells on the same row/column on the new sheet). Also, they don't refer to the older sheet where I want them to refer to.
View 2 Replies View RelatedI need a script that will look for all the formulas in a sheet and increase them by a digit.
Lets say one that particular formula is =(A1-A2)/A2*100
I need the macro to change this to =(B1-B2)/B2*100
I have already programmed in a Find/Replace command, but realised that this would only work one time only. I need generic code that will bump them up each time rather than having to hard code the Find/Replace command for every single cell in the book.
Take the following simple sheet. My data is in column A, and includes some merged cells. I place the formula INDIRECT("RC[-1]",0) in column B, and copy it down the sheet. I get the result as shown in column C, but I would like the result shown in column D!
| A | B | C | D |
--|-----------|------------------------|-----------|-----------|
1 | Bob | =INDIRECT("RC[-1]",0) | Bob | Bob |
--|-----------|------------------------|-----------|-----------|
2 | Harry | =INDIRECT("RC[-1]",0) | Harry | Harry |
--|-----------|------------------------|-----------|-----------|
3 | | =INDIRECT("RC[-1]",0) | Charles | Charles |
| |------------------------|-----------|-----------|
4 | Charles | =INDIRECT("RC[-1]",0) | 0 | Charles |
| |------------------------|-----------|-----------|
5 | | =INDIRECT("RC[-1]",0) | 0 | Charles |
--|-----------|------------------------|-----------|-----------|
I realise WHY this doesn't work (as Excel refers to the merged cells A3:A5 as just A3, and A4 & A5 don't essentially exist any more), but I would like to know if there's a way to get around it, and achieve the result in column D.
I need to copy a bunch of cells that contain formulas without altering the cell references. I know I could change each formula to contain absolute cell references, then copy and paste special with formulas, but this is alot of work, and following that I'd need to change the references back again from absolute to relative in both locations. So, is there a way to quickly copy and paste formulas in multiple cells without altering the cell references?
View 2 Replies View RelatedI would like to rotate a Line Chart 90 degrees. I tried using an XY scatter chart but my Y-axis would be time values which are somewhat random but increasing. I would like to keep the spacing between plot points consistent (Y-axis spacing). I could simply use a number list for my Y-values to get consistent spacing but then I lose the time information. I don’t necessarily need the time value on the Y-axis if I could get the information to display when mousing over the plot point. Any ideas on how I could accomplish this?
View 6 Replies View RelatedI have a large workbook with LOTS of large formulas. However they dont contain $ characters before the ranges as I haven't needed to manipulate them til now. Now I am changing the sheet and I cant seem to move or copy these cells without the ranges whithin the formulas changing! Is there a way to do this? If not is there a way in VBA that I can check through selected cellls and enter a $ before all the ranges?
View 2 Replies View RelatedI made a spread sheet last year that used various COUNTIF formulas that referenced other cells. =COUNTIF(I40:ER40,"A") It worked perfect.
I copied that spreadsheet to use for this year. I cannot get this formula to work. I have reentered in various locations and forms, but to no avail. If I enter the data and then enter the formula, it works.
However, if I enter the formula first and then enter the data (which I will do over the course of the year) it does not work. I even tried to reconstruct all of this in a brand new spread sheet and it still is not working, although last year's spread sheet still works.
I've attached a sample document of my data layout. On the Compare tab, I'd like to build a sumif formula that takes the data in column B and uses it to reference the other tabs without having to use a large nested IF statement. For example, on the Compare tab, in cell C2, the formula will know to look at the "JAN MONTH" tab because B2 says "JAN", then the rest of the sumif would be looking at the data in column A and matching it with the data in column A in the correct tab.
View 1 Replies View RelatedHow to copy a formula (16 columns) from a workbook to another, without referencing the source workbook.
Some people are using tricks such as replacing the equal sign "=" for another character such as "^" then using replace all to put the equal sign back again... but this is too much trouble.
I'm working on a spreadsheet which will sum values from two source worksheets based on the month and year in certain columns. I put together a summary worksheet and was able to successfully pull the data I wanted. So my next step was to convert the Summary data into a table (because I prefer the aesthetic value of the "greenbar" effect in Excel tables). But as soon as I converted the cells to a table, the formulas returned nothing.
After playing around with it, I determined that the problem seems to be the fact that I use the year criteria as a cell reference (that way, I can later change the year in one cell and update all the dependent formula). I've got "2013" in Cell A1 in General format, and all the formulas use a YEAR=$A$1 criterion. I found out that if I changed the formulas to YEAR=2013 instead of using the cell reference, I got the desired result again. But I cannot quite understand why changing the data into a table would alter the way Excel interprets the formula (plus, I still want to be able to change the year by updating one single cell).
Is there a way to unfix multiple cell references from formulas in a range of cells at once?
View 2 Replies View RelatedI have code that "mostly" works great. I'm copying columns from one sheet to another sheet, but in different columns. I have unique code for each copy/paste. The source is mostly raw data. However, there are a few columns that are formula-based, and I'm having a problem. I've attached my code, and it works, but it takes 20 minutes to complete.
I'm very new to macros, but I think(?) I know enough that a data source with 2,000 rows and 30 columns shouldn't take 20 minutes to complete the macro calculations. All of the columns (copy and paste) in the code are pure data. The only exception is the column labeled "AI". Is there are shortcut, other than creating new columns in the source data sheet and pasting these results as values? I put the specific pieces of code that I'm referencing in bold.
I'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.
I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies View RelatedI have a macro running this code to strip out unwanted formulas and formatting.
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False
Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2
'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If
Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................
A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.