Replace Range Names In Formulas With Range References
Jul 7, 2007
I have inherited an Excel workbook in which the formlas all contain cell names (and there are thousands of names in this book). I need to find a way to change from using cell names in a formula back to a standard absolute cell reference but have no idea how to do this?
I have a workbook with a single worksheet. The worksheet includes some named ranges, which are used in a dynamic chart (so that when I add data, the chart updates).
I want to duplicate worksheet#1 so that I can have worksheets 2, 3, 4 etc.
Thus I can paste new data in the new worksheets, and have all my calculations done.
Naturally, the only probelm is the graph. It is using the named ranges from worksheet #1.
Is there a solution, so that I don't have to re-create my graphs on each worksheet? (each worksheet has varying numbers of rows, AND, I will be updating each worksheet with new data from time to time, thus the need for a dynamic chart)
I inherited a Profit & Loss (P&L) template from a colleague. She developed it on Excel 2013 and I'm on Excel 2010, but it's not clear that is the issue here (tho, never say never).
The spreadsheet was set up for quarters and I've expanded it (by copy and paste) to 12 months. It has the typical tables: sales revenues, cost of goods, admin exp, etc. Each table has a number (varies from table to table) of rows with a subtotal in the bottom row of each table. There is also a label row above all the tables (this now has the month labels).
The problem: The subtotal (bottom) row of a table uses the formula "=SUBTOTAL (109, ref1)". In the original spreadsheet, ref1= "QTR1" (or QTR2, QTR3, etc.). I'd like to change that to be MONTH1, MONTH2.... However, when I enter MONTH1 or MONTH2, etc. for ref1, I get a formula error. Which I suspect is expected.
What I noticed is that if I highlight ref1 in the subtotal cell formula and then select the cells I want included in the subtotal, the first selected cell shows a "B8". With 2 cells, it shows "B8:B9". Good so far. However, when I get to the last cell before the subtotal row, ref1 changes to "[QTR1]", so the final subtotal formula shows "=SUBTOTAL (109, [QTR1])".
I've tried to change QTR1 to MONTH1, but get an error. I used Name Manager, but QTR1 doesn't show up on the list (However, the tables are named and seem to reference the columns correctly). If I do a "Define Name", the window pops up w/the name entry empty and the "Refers To" containing "=tblName[QTR1]" (tblName is the name given to the specific table (revenue, cost of goods, etc.) and does show up under Name Manager).
I tried an experiment and in the revenue table I deleted (move left) the cells (up to but not including the subtotal cell) under MONTH1, and then inserted (move right) a new set of cells. Now the "Refers To" shows "=tblSalesRevenue[Column7]".
So, apparently, Excel can assign some kind of label to a set of cells. My question is, how do I (if I can) change that label, or name, so that the column of cells I want to use for my subtotal formula will work (MONTH1, MONTH2, etc.). It's not clear that Define Name or Name Manager is what I should use. I looked at the original spreadsheet and there are no Name Manager listings for QTR1, QTR2, etc.
Additional Note: I forgot to mention that this isn't a show stopper, just a nuisance. If I use normal cell references (i.e. B8:B11, etc.) the spreadsheet works fine and as expected. This is just one of those "nits" that I'd like to understand so that I can make the spreadsheet programming more "readable".
I 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?
I 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?
eg say you call (ie insert a range name) cell A1 "firstcell", B1 "secondcell", then A2 "divisor1" and B2 "divisor2". if you put a formula in A3 which is "=A1/A2", how do you copy and paste this formula into B3 but getting the formula to reference B1/B2 rather than firstcell/divisor1, as it does by default?
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.
I am doing some work in which I need a very flexible tool. I will be doing regression analysis on thousands of funds at a time. I need to define the range for each fund by a count function that counts the number of monthly returns that are posted. The code I have so far is below (I underlined where the references use variables):
I have a spreadsheet with very long formulas that reference a whole lot of variables in the same column. I tried naming the cells so that writing of the formula is easier to do. However, I need to copy the column across for other entities of varying nature, and these copied cells refer to the same named cell. Can I name a cell and copy it sideways so that it retains some sort of name with allusion to the column that it is in?
Andrew van Gruting"
I have tried copying a formula with a named range and, as expected, it maintains an absolute reference. Is there a way that he might be able to copy but drop the absolute reference and copy with relative references. Can I place something in front - the equivalent to the $ sign, but rather a relative sign.
is an indirect range that resides in cell B15. It is constantly changing and the expectation is that the X_Y plot would adjust accordingly. It represents the data range of the chart. The chart does not carry with it any title.
So how do I approach this without using vba? As always any input is highly valued.
Could someone show me an alternate way of using/writing this code instead of using RC references to the Sheet1. I am trying to select ranges that dynamically change according to the range of data in the next column. This is the code I have but it is static in the range it chooses.
(strFrom and strTo are the column variables, already defined)
I've tried every combination (except the right one!) for over an hour and just cannot get this to work.
If someone can also explain the logic around the number/placement of inverted commas it would be much appreciated - since they're all straight, I'm never sure whether I'm closing a set or opening a new set.
create a script that will replace the names in column A on sheet1 from a Master sheet in the same workbook?
The problem is that different users are entering data on sheet1 col A in different ways example someone may enter Johnc or John C Or John What I want is for something to run down col A on sheet1 and look for the like name on the master sheet if the name matches then do nothing but if the name is like another name on the master sheet then replace the name if they are almost alike.
I am try to get the following VBA macro to work; however, I keep getting hung up on errors regarding the formula I am trying to input. It is getting hung up on the apostrophes and dollar signs. I am fairly new to VBA so I am lost when it come to converting my sheet formulas to VBA.
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks) .Formula = "=RAND()*0+VLOOKUP(INDIRECT(ADDRESS(1,COLUMN(),3),TRUE),INDIRECT("'"&TEXT(INDIRECT("$A"&ROW(),TRUE),"DD-MMM-YYYY")&" Inv'!"&"$J:$K",TRUE),2,FALSE)" .Value = .Value End With
I'm working in Excel 2010. I'm trying to create a dynamic range, using the OFFSET function. I've got it working beautifully but as soon as I save it and close the Name Manager the range stops working. When I go back into Name Manager I find all the cell references have changed (from C1:C600 to C1048572:C595 - or some other strange range).
P.S. What it's doing isn't so important as when the cell references are right it works. But just for full info: It's an OFFSET function, starting at A1. It will look at a list of companies and use a Match function to find out how many rows to offset (based on where the first instance of a particular company. And it uses the COUNTIF to make the range the same number of rows as there are entries for that company.
I'm trying to do a quick find and replace across a range of cells to remove Chr(10) and Chr(13) characters. For some strange reason, some of the cells are being good and duly allow the removal of said characters and some are not - they stubbornly refuse to remove with no error message. The ones that are not allow removal if I copy the individual cell to a new sheet.
Sub removeCharNew() 'Dim x As Integer Cells(1, 1).Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Cells.Replace Chr(10), "" Cells.Replace Chr(13), "" End Sub
I have a list that looks like the list on my sample file. You will see ME28A16 at the top and the bottom of the list. I want to fill the Cells inbetween with that value. In my real file It may start ME or it may start PE
Here is the Script that I am using but I can not get it to work:
Dim CurrentRow As Long Dim LastRow As Long Dim CurrentText
LastRow = Range("A65000").End(xlUp).Row
For CurrentRow = 1 To LastRow If InStr(4, Cells(CurrentRow, 4).Value, "M", vbTextCompare) > 0 Or _ InStr(4, Cells(CurrentRow, 4).Value, "P", vbTextCompare) > 0 Then CurrentText = Cells(CurrentRow, 4).Value Else Cells(CurrentRow, 4).Value = CurrentText End If Next CurrentRow
So have large spreadsheet with grid of elevations. Each column/row is 1 foot grid. There are blanks in it where there was no elevation data available. I need to interpolate those elevations using the surrounding known elevations. I can setup the interpolation just fine using a circular reference but what i am having trouble with is getting formula into all the cells. I need to replace blanks with formula that averages all four cells around it.
This will result in circular references that will interpolate from known points nearby. So for example if cell D4 was blank formula needs to be
=(D3+E4+D5+C4)/4. D5 would be =(D4+E5+D6+C5)/4.
I have been playing around trying to make a macro or something to do this but am not having any luck. Basically need to find/replace all blanks with formula above but so the formula references the cells around it properly.
How to find and replace a range of values. I've looked online, and searched the forum with no success.
I have a list of cells in a column labled "Check #1", "Check #2", "Check #3" ect. I'm trying to build a Macro that would replace any value from "Check #1" to "Check #50,000" with "Outgoing Check". I know this can be done manually with relative ease, but it will need to be done very very very often, so I'd like to automatic it as much as possible.
How to put the steps into a Macro or Formula that could be re-produced with ease.