# Formula That All Reference To Different Documents

Aug 15, 2008

I have 330 lines of formula that all reference to different excel documents. These files are donated by a 4 digit number. i.e. 4186.xls.

The main document that looks at these files needs to ability to acknowledge changes in the 4 digit number in its formulas and look at a different file if necessary.

EG. =sum("4186.xls"!E4:E5)

A very simple example of the formula i am using. I just need to actively change the 4186 that is in the formula to whatever is in the contents of another cell.

I might just be stupid. Infact, i admit i am no expert. That was my first =SUM formula actually.

ADVERTISEMENT

## Circular Reference: Cell References In The Formula Refer To The Formula's Result, Creating A Circular Reference

Aug 14, 2006

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

## Stop Formula Column Reference Changing On Insert But Not Row Reference

Mar 6, 2008

A 'Days Attended' cell (N8) and a 'Days Absent' cell (O8). N8 needs to count the number of "Present" values there are on another worksheet. The other worksheet has dates across the top and names down the side.

When i use
=COUNTIF("Attendance!C9:Z9", "Present"),
and the next date comes along the formula changes to
=COUNTIF("Attendance!D9:AA9", "Present")

ie. the reference moves a column across - the new date's absent or present is not counted. Using =COUNTIF(INDIRECT("Attendance!C9:Z9"), "Present"). is no good because when i add a new name i need the row reference to move down as a row is inserted. ie. both person's formulas count the same row. So, my question: I need the columns to stay the same - C:Z (leyway for future dates) and the rows to change as i insert or delete people from the system.

## Cell Reference :: Formula To Reference New Cells

Feb 15, 2010

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.

## Circular Reference With Formula But No Reference To It

Mar 26, 2009

=INDEX(INDIRECT('Quote Detail IP'!\$C\$10&'Quote Detail IP'!\$C\$5&"!\$A:\$DC"),MATCH(B1,INDEX(INDIRECT('Quote Detail IP'!\$C\$10&'Quote Detail IP'!\$C\$5&"!\$A:\$DC"),,1),0),MATCH(A1,INDEX(INDIRECT('Quote Detail IP'!\$C\$10&'Quote Detail IP'!\$C\$5&"!\$A:\$DC"),1,),0))
Where
A1= "M16" and B2= "185%RPIT630"
'Quote Detail IP'!\$C\$10&'Quote Detail IP'!\$C\$5=QxTermAge63
Can some on tell me why this is raising a Circular Reference!!

## Change Formula Cell Reference Based On Another Cell Reference

Jan 8, 2012

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?

## Hyperlinking To PDF Documents

Apr 28, 2009

This code will take the value of the active cell (which is a filename) and open the corresponding PDF document of the same name.

Sub OpenPDF()

Dim fName As String
Dim fExt As String
Dim fPath As String
Dim fFullPath As String
fName = ActiveCell.Value
fPath = "M:Books"
fExt = ".pdf"
fFullPath = fPath & fName & fExt
ActiveWorkbook.FollowHyperlink Address:=fFullPath, NewWindow:=True............

## Compare 2 Documents

Jul 13, 2009

I receive a Weekly list; let’s call it “Doc1” with a load of order numbers in column A. I then have another excel document, lets call it “Doc2” also with order numbers in column B.

Is there a way to see if an order number in Doc1 already exists in Doc2; perhaps change the font colour to red for example (in Doc1 only)? I don’t want to affect Doc2 in any way I am not allowed to alter it at all.

## Linked Documents Cell Changes

Feb 7, 2014

I have several excel documents all linked to one unique excel document which I will call document A.

Document A is a performance spreadsheet where I have percentages, sales figures and times, for my sales team.

Each sales person has their own unique dashboard (another excel document) that I am currently trying to finalize.

Their personal dashboards have pie charts with text boxes. The text boxes within the charts link up to their relevant cells in document A.

When Document A is open and I open 1 of the sales person's dashboards. Everything looks good and works, however.

When I close document A the dashboard information changes. For example, the dashboard information could be showing 25% but when I close document A it reverts to 0.25

If I close down the dashboard and open document A back up, change it to 30% then close it. The dashboard recognizes the change BUT reads as 0.30. This is the same with them all.

The only fix I have figured out is creating another tab in Document A then duplicate of all the information required for the dashboards then save the cells as TEXT (as opposed to currency, percentage etc).

This kind of defeats the object because I have formulas set up in Document A and don't want to be doing twice the work.

## Save Documents In Hyperlink?

Feb 24, 2014

I have a file containing thousands of hyperlinks to respective image files. How I create a macro to save them with respective document names as shown in individual cells ?

## Excel Documents Won't Open On First Try?

Nov 17, 2007

I am having problems opening Excel documents on first try. If I double click a excel doc icon it will start excel, hang for about a minute and a half then just display a excel blank doc. If I then go back to the original doc icon with excel still open and double click it again the document appears.

## Client Search On Two Documents

Apr 17, 2012

I have been sent a document of clients names that I need to check against our own, is there a way on excel in which I can see if there are any matchers between our client list and the one I have been sent. It is for security purposes.

I do not fancy Ctrl - F 4000 client names

## Navigate To My Documents Folder

Jul 30, 2008

Is there any code that can make it possible to navigate to my Documents Folder only that is on a network drive and open the folder but not a document.

## Links To A File In My Documents

Aug 21, 2009

I have a template that I wish to distribute to others. It will link to a second file (also distributed by me) that will be in each users' "My documents" folder (or "Documents" in Vista). I currently have the template set to look at "C:" as that is common for everyone. Turns out that creates other problems. However, now each user's file location will be unique due to the path of their documents folder

Question, is there a robust way to automate the finding of the linked file in the template without having each user "relocate" it? Reason is the template will be updated frequently and I want to ease their pain by not making them go through this every time.

## Linking Documents That Will Update Each Other

Aug 2, 2006

Is it possible to link excel document that will update each other? I am looking for the capabilities similiar to linking worksheets in a workbook.

## Securing Documents By Changings Or Print

Apr 9, 2009

I have an Excel document that I need to secure. By that, I mean that it can be viewed by anyone, but it cannot be changed or printed.

## Running Vblookup Between Two .xlsm Documents

May 19, 2009

I have two workbooks one names "Quote" other named "product supply". In "Quote" i would like to have somthink like a Vblookup that on a givin line can enter price's, disciptions and other data automaticly on that line, From the "Product Supply".

Example:

"Quote" A:1 Dropdown list. Select product, Then refers to "product supply" of that product, then trasphers data into "Quote" A:2

## Lookup Function: Looking Up A Name And Then Returning All The Documents

Sep 2, 2009

I have data concerning documents people have to write, which is updated weekly. Is there a way of looking up a name and then returning all the documents that they have to write. I have tried a vlookup, but this only returns the first entry and not the lot. Also i would prefer the document data for each person to be returned in to one cell.

## Automatically Opening Word Documents

Sep 28, 2009

I need a little assistance opening a Word file via an Excel macro. The two files are linked and upon opening, the Word file asks if the links should be updated. I need this to automatically be "Yes" everytime the file is opened.

## Generating Sequential Numbering For Documents

Dec 5, 2012

If i had a template in excell would it be possible when it was opened it could generate a new number for the sheet in a cell for Ref ID.

## Display Documents Properties From Its Hyperlink

May 29, 2013

I'm putting together an Excel sheet which contains links to other files. I was wondering if I can display the properties of those files in an adjacent cell? Basically I want to show the date the document being pointed to in a hyperlink was last changed. The documents are contained on a local drive.

## Excel 2003 :: Can't Retrieve XL Documents

Jul 18, 2013

I have used MS Office Excel 2003 for many years without difficulty in XP. Suddenly I find I can't open many of my old documents. I get a message,"Microsoft Office (InfoPath2003) cannot open (the following file, etc.) A second message says, "The form contains XML that is not parsed: An invalid character was found in the content. Line 0, Position 0" Then I get the message "MS Office is not currently set as default." I complete the instructions that follow and it is now corrected but I continue to get the same error messages.

## Linking To Password Protected Documents?

Dec 2, 2013

I have a master document which maintains employees vacation an lieu time. I want to create a document for each employee where they can view all of their own person information by linking it to the master. The only problem is is that the master document is password protected. They can't have the password or they could access the master document and other employees confidential information. Is there a way to update there own document without entering the password or codon so the password automatically enters without them seeing it.

## Get Macros To Open Embedded Documents

Jun 11, 2014

I am trying to create a macro to add to a button that will open an embedded document (word, PPT, and excel).

I recorded the macro and it works fine the first time I try to test it but after it tells me that there is an error...

I'm not exactly sure what to do. Unfortunately, I could not find what I needed on youtube

Error: It talks about the object being out of range and allows me to debug... the code that is in the developer is:

Sub Macro1()
'
' Macro1 Macro
'
'
Sheets("Sheet2").Select
Windows("Worksheet in Book1").Visible = True
Selection.Verb Verb:=xlPrimary
ActiveWindow.Close
Sheets("Sheet1").Select
End Sub

The Windows line is what it tells me to fix.. from there I am lost... Is it because I am trying to do a double clicking function?

## Split TXT File To Word Documents

Mar 31, 2012

Below code is for splliting text file into multiple excel worksheet. I want to modify it to splitting text file into different msword documents in folder. Is it possible to change the code

Sub LargeFileImport()

'Dimension Variables
Dim ResultStr As String
Dim FileName As String
Dim FileNum As Integer
Dim Counter As Double
'Ask User for File's Name
FileName = InputBox("Please enter the Text File's name, e.g. test.txt")

[Code] ........

## Hyperlink For My Documents Location In Cell?

Jun 11, 2014

I was just wondering if its possible to create a Hyperlink to open a location with the link ref in another cell?

For example, If I created a shape (Insert > Shape) then assigned a hyperlink to lets say Sheet 2 cell A1 & in that cell the link to open My Documents?

Sheet 1: Shape (as a button)
Sheet 2: Cell A1 - C:UsersUSERNAMEDocuments

Then when I click the 'button' it doesn't go to Sheet 2 cell A1 it actually opens the documents?

## Locating Home My Documents Folder

Mar 10, 2007

On the computers in my office they have a My Documents folder that is in the directory C:Data. There is also a My Documents folder in the usual windows location C:Documents and Settings etc. Is there a function that will give me the path of the folder that is on the desktop? The macro will be run on computers with the folder in different locations which is why I need to look it up.

## Excel 2010 :: How To View 2 Documents On 2 Displays

May 22, 2013

I have MS Office 2010 Home Edition. Is there a way to make 2 different Excel docs show up on 2 different monitors at the same time (one on each, of course)? (I have a PC and 2 displays total.)

## Why Do Some Excel Documents NOT Allow To Undo / Redo When Using Macro

Jul 11, 2013

Some of my Excel files work perfectly in terms of undo/redo, while others simply cannot use that feature. I took a stroll through the internet looking for the answer, but the issues that I found seemed to point to the application losing its ability, which was resolved with a registry edit/update/change. I don't think that's the case here because I CAN undo is SOME documents.

After some investigation with my problem documents I have found that THIS Macro is the one thing all my undo-free documents have in common: [URL]

## How To Copy And Paste Word Documents Into Excel

Dec 11, 2013

I have about 600 Word Documents that contains information about clients. Each Word document file name is the name of the client. Example. Tom Smith. I wanted to be able to highlight all 600 word documents and paste them into Excel, so that I can press Ctrl-F to find what ever name it is I'm looking for, then click the name and the word document open. Is this possible?

Copyrights 2005-15 www.BigResource.com, All rights reserved