Absolute External References When Defining Names

Nov 29, 2006

I would like a defined name (1) in my excel document (A) to refer to another defined location (2) in an external document (B), which is a master document that will not move or be renamed. The main document (A) is one which will be copied to numerous locations within our company's network.

Therefore, I need the defined name (1) to have an absolute reference to the external document (B). At the moment I'm only able to get a relative reference, since Excel 'simplifies' the reference when both files are opened. Here is what I'm currently using: ='J:Invoice TemplateOffice list01 231106 List of offices.xls'!SiteNames

Disable Automatic Absolute References When Linking To External Sheets?

May 30, 2014

Would be a massive time saver but can't find anything here or on Google on how to do this.

Converting Absolute References To Relative References In Formulas?

Feb 10, 2014

I 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*")

Undo Absolute Cell References

Jul 26, 2009

I'm using a lot of "activeCell.offset(row,col).address" type of entries to form formulas for me. All of the references come across as Absolute References - \$G\$24, for example. Is there a different way for activeCell to give me a "normal" address ('G24' vice '\$g\$24') or a function that will convert for me?

Relative Or Absolute References? (each Day Is Exactly 10 Rows)

Apr 25, 2007

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?

Absolute Cell References With Macros

Jul 31, 2006

can't seem to understand how to keep cells absolute when creating a macro

Automatically Put Absolute \$ In Cell References

Aug 27, 2006

When specifying a cell, what do I press to make Excel automatically insert the \$ signs like \$C\$5 ? C

Defining Names

Jun 16, 2009

Knowing Excel adapts its formulas upon inserting a row (i.e. a sum formula will automatically include a new row inserted in the middle of its range). Is it possible to do this with a named range. I have a pivot table that serves as a table for a vlookup, but the table sometimes changes month to month with extra accounts. I need to be able to keep the formula even though the table changes, so I figured a named range would help me out but it still doesn't accomplish what I need it to do. So basically, is there a way to make the Named Range, say AGroup (A1:C5) extend to (A1:C7) if 2 new rows are added in?

Absolute References: The Sub Worksheets To Update Automatically

Aug 4, 2009

I have a workbook with a main worksheet where all the data entry and calculations are made and then I have "sub" worksheets which mirror subsets (specific columns) of the information from the main worksheet. When information in the main worksheet changes, or rows/columns are added, I want the sub worksheets to update automatically.

The only way I can figure out to do this is to use absolute references in the sub worksheets. Does anyone know how to change the references to be absolute (using the F4 function) without having to do them one by one? Or can anyone suggest how I can achieve this in another way.

Relative, Absolute & Mixed References In Many Cells

Jun 17, 2006

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!

Automatically Defining Names

Nov 14, 2008

If I have a column of data with a defined name is there an easy way to update this as data is added? Can this be done when you open the workbook and without? I'd assume an open event could do it but I'm having problems finding a simle solution.

Absolute Cell References For Large Amount Of Cells?

Aug 13, 2014

I want to link a number of cells on one worksheet back to another worksheet within the same workbook. Say everything from Cells A1 to M90.

I find the fastest way to do this link A1 on second worksheet to A1 on first worksheet (by using = and click on worksheet 1 Cell A1).
I then click on cell A1 on worksheet 2, grab small square in bottom right hand side of cell, and drag accross to M1.
While A1 TO M1 is still highlighted, I then grab small square on bottom of cell M1 and drag down to M90.

This makes all cells within this range reference back to worksheet1. In advanced settings I have unchecked for all blank cells to show a zero in woksheet 2.

Therefore I now have what I currently want, although I would like to make all reference absolute?

I tried doing this from the beginning again making A1 absolute at the start, but dragging the cells across and down does not provide the info I am looking for in all cells?

Change From Absolute To Relative References With Excel Macro.

Sep 6, 2009

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

Inserting Rows Breaks Cell References But Can't Use Absolute

Jul 23, 2006

I have a problem where my conditional formatting is broken when new rows are inserted because Excel is auto updating the cell references. I am not able to use absolute references because I need to be able to Copy the entire information many times on one sheet to handle an ever changing number of projects.

I have attached an example file which shows a simple version of the sheet. There are areas for two projects now, but more would be added to the sheet as needed by just copying the entire section of one project and pasting it at the bottom. For each project, there is a cell which has a data validation list, e.g. B6, from which the user can select the current stage. The list of stages is obtained from column A, e.g. A9:A18. Whatever stage is selected, I need it to be highlighted in some manner. I currently have conditional formatting that checks if the value in B6 matches the information in the current cell and will change the text font color if True.

The problem occurs when new rows are inserted into the project. For example: if the project requires a Beta 1.1 stage, then a row would need to be inserted and labeled for that stage. However, Excel auto changes the cell references so that it no longer looks at B6. I need some way that the current stage can still be highlighted when selected in the Data Validation list.

I know that what I am needing to do may not be the best method to go about this, but I am having to work within the confines of the software available to me and the intended users of the file. Since the number and length of projects can change on a daily basis, the users need to be able to add and remove room for additional stages and projects whenever they want. I have a basic solution available to me using a macro, but the overall solution is clumsy and just leads to more problems.

Copy Non Absolute Cell References In Conditional Formatting Formula?

Dec 3, 2012

I have this fairly simple formula which decides whether to shade a cell or not

=AND(\$X\$1<>"TBD",R3<>"None",AC3="Y")

This is set in cell R3 and I want to copy it all the way down the cells in the R column. However, when I copy & paste (and copy and paste using paste special, formatting) the R3 and AC3 cell references do not update to match their relevant rows. eg If I highlight cell R26 the conditonal formatting formula still refers to cell R3 and AC3, not R26 & AC26. I'm using Excel 2010 but I don't recall this happening in 2003.

Excel 2010 :: Absolute References Do Not Apply To Inserted Rows?

Nov 18, 2013

build a spreadsheet that reads information off of a Master sheet onto 4 other sheets. The hope is that by making changes ONLY to the Master sheet that the other 4 will update automatically.

And then we ran into the trouble of not being able to insert new lines onto the Master sheet without throwing everything off on the other sheets.

(I've attached an example.)

For instance, if I go to the Master sheet in the Test.xlsx attachment, I've left out Lima from the alphabet. So, I insert a new row onto the Master, switch back to the Formula sheet where it should (theoretically) just update the cells to display the new data.

Not so. The Formula sheet just skips the new A13 and keeps on going.

the primary one seems to be using Offset. Well, the coworker will be adding and deleting many, many rows over the course of the year, as it is a product log and we change our products often.

Change Multiple Formula Range References Between Absolute/Relative

Mar 16, 2008

Is there a way to absolute reference multiple cells at one time?

External References That Should Be Internal

Mar 1, 2007

I update files monthly and save them each month with a new name (eg. Jan, Feb, etc.).

In the file I have a reference from one tab to another tab within the same file. So every time I re-save this file with a new name, it should still reference the cell in the other tab - same file.

BUT on 2 occasions this reference changed to an "external" reference to the previous month's file.

I've been doing this every month for over 2 years and this problem has only happened 2 times. I know others that have experienced this, but nobody knows what caused it.

Does anyone know what would cause this? The biggest problem is that I may not notice this has happened for a long time.

One other thing I might add. I use an older version of Excel (2000). I share these files with other people who may have newer versions (I'm not sure who has what). Can sharing these files with others over a network system have something to do with this? Can opening "read only" when someone else is in the file and then saving as a copy do this?

Lock External Worksheet References

Aug 3, 2006

I have an excel workbook we use for quoting jobs. All our pricing references are in another workbook on our network. I have reference in the 'quoting' workbook that refer to the 'pricing' workbook.

The trouble is, when some users open the 'quoting' workbook, and have another Excel file open, some references to the 'pricing' workbook change to the other file they have open.

I suppose there is some sort of replicable condition that is happening, but I can't figure out what is going on or why.

So my question is: Can I ' lock' an in-cell reference to an external workbook so that it NEVER changes?

I tried hand entering the reference (network location+workbookname+sheetname) into a cell and then referencing that cell, but I couldn't get that to work.

External Worksheet References Conditional Sum

Apr 17, 2007

I am trying to use a conditional sum formula where the data resides in and external workbook. This is fine until I close the external workbook and the formula no longer shows the external reference. Is there a way of getting excel to use conditional sums in this way (or what is the best way of achieving this?)

Cells Values For External Cell References

Apr 17, 2008

Is there a way of writing a formula to create a reference to an external spreadsheet? I have a folder containing 100s of workbooks all based upon a template. I can create a directory listing of this folder using a macro within Excel.

e.g.
Directory listing
C:My DocumentsReturnsFile1.xls
C:My DocumentsReturnsFile2.xls
C:My DocumentsReturnsFile3.xls

I would then like to use this list of file paths to create links to several different cells within worksheets contained in each of these. I know that if I did this manually for each worksheet the formula would be a variation on =[C:My DocumentsReturnsFile1.xls]Sheet!Cell. Is there a way that I can automatically create these external references? i.e using values in column A (directory listing) to create many external references, say in column B

I use my directory list and then a formula or macro populates automatically these external references? The file names in the directory change daily (100s of worksheets!) so I don't want to have to manually input these each time. If the folder containing the workbooks was held on an intranet would there be a similar solution?

Excel 2007 :: Change Source Workbook For All External References At Same Time But Avoid Browsing?

Jan 7, 2013

Need to create Working order (in Excel 2007) for filling machine in my factory. Working order is changeable from day to day and the deference is product quantity. Product quantity in working order [File - Working order] is changing according to the each sales plan [7-Jan-13, 8-Jan-13] - (File Working order is Linked to the appropriate sales plan in this case - [7-Jan-13]).

Anyway, I need to change the source workbook "sales plan" from [7-Jan-13] to [8-Jan-13] by changing source workbook name in red cell [File - Working order] and not to browsing location?

All source workbooks [sales plan 7-Jan-13, 8-Jan-13]are at the same location in the same folder, only difference is the source workbook names. So, is it possible to change source workbook name (File - Working order - red cell) for all external references (File - Working order - green cells) at the same time, and avoid annoying browsing?

Conditionally Format Cell Using Absolute Values And Relative Cell References?

Mar 25, 2013

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(Selection.FormatConditions.Count).SetFirstPriority[code].....

Converting Formulas To Relative/absolute References With Formulas Referencing Other Sheets

Dec 15, 2008

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.

Dynamic Path Names For External Links

Jun 18, 2008

I am designing a spreadsheet for work.

each of the people i work with have their own workbook with their name as the title, John Smith.xls.

There are about 20 of us on the team with their own workbook with the same design.

We record information on these weekly and so there are 20 files in the folder for Week 1, 20 in Week 2 etc.

I want to make a master spreadsheet with will collect all of the information on these sheets and list them on a master spreadsheet every week.

So the external data i am linking to is found in a location like

Here is my problem,

I need the 'Week 26' and the 'John Smith' to be changeable and preferabilly linked to a cell using the INDIRECT formula so I can change all references to Week 26, 27, 28 etc and also change the workbook name it is referencing.

I have attempted to do this in the following way

I broke down the pathname to seperate cells

Cell 2: A reference linking to a cell containing value: Week 26
Cell 3: A reference linking to a cell containing value: [John Smith.xls]
Cell 4: Sheet1'!\$A\$1

I then used the CONCATENATE formula to link these into a single cell as a text string.

I then tried to use INDIRECT to link to the filemane in the CONCATENATE cell.

This did not work.

I need a way to make parts of the filename variable and linked to one cell.

I also need to do this while the other workbooks are closed.

I have found some information on INDIRECT.EXT which as far as I can make out involves installing mods to Excel to make these work, I cannot do this as the spreadsheet will be used across our network and will eventually be used by thousands of users.

Using Relative References In Range Names

Jun 5, 2009

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)

Cell Range References (Labels / Names)

Aug 6, 2013

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".

How To Reference Other Sheet Names In Workbook But Keep Cell References The Same

Oct 7, 2013

Within one workbook I have 15 sheets, 13 are for separate divisions within the company, 2 are used to present sums across the 13 sheets. All 13 sheets have identical columns and rows, with unique numbers in each cell. Right now I am manually selecting the cells to reference in my master sheet, =sheet name!cell+sheetname!cell+sheetname!cell - so and and so on. How can I reference the sheet name once, and then all of the cells to SUM? =sheetname!(c4+c14+c24+c34) etc?

This would let me be able to copy the formula from cell to cell, and only have to change the sheet name each time.

Copy Relative References Whilst Using Range Names

Jul 20, 2006

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?

Absolute Column And Absolute Row Problem

Jan 14, 2009

I have a simple count formula which is suppose to be absolute.
=COUNT(\$L5:\$V5) and works till I add a column via a macro.
Macro adds a new column at column M then inserts data.
My problem is when I check the formula after macro it changes to
=COUNT(\$M5:\$W5) I thought using the \$ sign means Don't change?