How Do I Subsitute Part Of A Formula Linked To Another Workbook
Jul 26, 2007
I've run into a roadblock in excel 2003 in trying to create an accounting spreadsheet that will pull in information from several separate workbooks (invoice files) using formulas which substitute invoice Nos. (which coincide with the tab/worksheet names).
My obstacle has something to do with order of operation/calculation I presume... I've successfully used labels to bring in information from separate tabs on the same file by using the substitute function with generic formulas.... in this case exel processes the formula, likely because it calculates the linked information first in its order of calculation; then trys to substitute information into the result of a calculation already made....
I'm trying to find a way to tell excel to look at respective cells in the same file but on a different tabs/sheets...
For example; I've been trying to pull in the date an invoice was issued for client "X".
I plug in a formula in a remote cell of my accounting spreadsheet "Y" which basically says to bring in the value on file ["X"]; worksheet "tab"; cell $J$11 - where "tab is just a filler that I would like to use the substitute function to exchange with respective invoice Nos. (i.e. 049; 050; 051; etc.) My hope is that by adding these 3-digit sheet identifiers; all other information from respective cells (in file "X") will be pulled into my accounting sheet - file "Y" (i.e. invoice total; applicable pst; gst; expenses; etc.) I have written as many formulas as required for the different types of info sought after, the only variable being the 3-digit worksheet identifier. The problem has been that excel first processes the formula in the remote formula cell(in file "Y"); then applies the substitute function to info already brought in from File "X"; whether it is a date value; or a currency value. I can't figure how to have the substitute function change the formula... not the result of the original function...!
I've tried to format the source formula cell as text (therefore not calculating a result at that instant) however the substitute function inherits the format of the source formula cell.
I have a datafeed program that offers multiple DDE link types to retrieve different types of data. After pasting the DDE links into Excel, all works well. However... To modify the links means manually editing upto 12 different array formulas located on different pages then pressing Ctrl + Shift + Enter after each to change the data source being analysed.
I found that I can manually copy and paste a few characters into each linked array formula, Ctrl+Shift+Enter but it needs to be by Macro referring to a couple cells (Say A1, A2) containing the variables that alter the data source being linked to.
I need to make a VBA Macro that refers to these two cells containing text, and modify the 12-odd linked array formulas.
eg. {=SUB33|getlocation!'N,pg,9,vp,A,30'} In this example Linked Array formula, the bolded PG and 9 would be variables pulled from cell A1 and A2.
All other components of the array formulas stay unmodified, it is only the "pg,9" text that needs to change in each linked array.
When i open my workbook that has links to another workbook it asks to update. When i select yes after a good 5 minutes (dont think it should take that long) it returns N/A#. The formulas are using a =sumif(from other workbook,this workbook,from other workbook). However as soon as I open the other workbook everything automaticaly updates and is fine.
I have a list of names in column A with monthly budget figs next to them in columns B,C,D etc. At the bottom of the list there is a total spend summary on line 7. This spreadsheet is then pasted with links to a separate worksheet (see example)
If an additional name is added to the list in Row 7 in the original list, the total is now in row 8 - how do I ensure that the linked sheetl picks up this addition?
(I have a project where I am drawing data from numerous worksheets. I have paste linked them as tabs to a summary worksheet so that funtions such as SUMIF can update when the file is closed or open)
I have several Workbooks, say BookA, BookB, BookC (or more). Let BookB and BookC be linked to BookA. I don´t know the actual names of the Workbooks though as the user may change those any time. Let BookA have several Worksheets, one named SheetA1. Let BookC have several Worksheets, one named SheetC1. Now when a specific cell in SheetA1 changes, I want to set the value for a specific cell in SheetC1 (using Worksheet_Change for SheetA1). How can I get at the actual name of that cell in SheetC1? I did imagine to loop through all the Worksheets in all linked Workbooks till I find one named SheetC1, but somehow I can´t manage the right syntax for that.
I have linked two workbooks together but now need to be able to convert that linked data to straight text. I have a v-lookup in the report that needs this to be this way. I do not want to use paste special values because that would defeat the purpose of linking the two pages.
I hope I can explain what I am trying to accomplish properly. Many years ago (5+), I had this exact workbook setup, but am having a lot of difficulty remembering VB & Excel and making this again from scratch.
I am looking to create an Excel Workbook. The workbook will contain numerous tabs, each tab will essentially be a template, and contain basic formulas.
On tab 1(Sheet1), I would like to have a listbox. The individual items in the listbox, should have the same name of, and be linked to all tabs in the workbook.
There will also be three buttons.
Button1 - Generates the names in the listbox based on existing tabs within the workbook Button2 - Opens a new workbook, and copies over the selected tabs (AKA - creates a working copy of the workbook) Button3 - Clears the selection and resets the listbox.
The number of tabs, and contents of tabs, within the workbook will be changing. So the listbox should know to look for all tabs, be it 5, or 50.
The end result would allow someone, who knows nothing of Excel, to open this workbook, as a read-only file. Generate a list of available tabs, select the tabs they need, generate a new workbook containing these tabs, that they can then edit to suit their needs.
In my line of work (and I am sure everyones) there is a huge problem with consistancy. This leads to a snowball effect of problems carrying over, and being copied from one project to the next. As I mentioned earler, I once already had this workbook setup, and it solved all our consistancy problems. I am hoping to do the same.
I have an excel program that uses a modeless userform. often times i will open other spreadsheets while the form is active or minimized. if i return to the form and run it without manually bringing focus to the appropriate workbook, i get an error because it references the wrong workbook. is there a way to keep that form tied to that workbook no matter what? also, it is important that this procedure works despite the workbook name changing.
I have a Reporting workbook I designed. There is a "Parent" workbook with the following design.
It has 6 Pivot Tables on a Sheet called "Data"
It has 6 Pivot Charts that were created from these tables on a page called "Summary"
These 6 Pivot Charts are all linked through a pair of slicers.
There is a "control" page which has instructions and buttons that trigger "Refresh All" and "Create Child Workbok".
This design is so that someone unfamiliar with excel could conceivably create the final product.
Essentially I have written/put together VBA to Create a new workbook "child" which has should be a funcitoning copy of the "parent".
The problem is the copiedmoved (I have tried both) Pivot Charts no longer update/refresh when the pivot tables change. I have script that reconnects the Pivot Tables to the Slicers. Slicers are fully funcitonal. Links in cells are fully functional.
Some of this code might look familiar.
Code:
Sub createWB() ' Copies VBA modules, Calls Dim wbNew As Workbook, wbT As Workbook Set wbT = ActiveWorkbook On Error Resume Next Kill ("PATHmod1.bas") Kill ("PATHmod2.bas")
The situation is that I have created a workbook in a network folder: xprojectsexcelclientAApril umbers
The workbook links to a few other workbooks on the same network, but in the parent folder: xprojectsexcelclientAApril
The problem occurs when my colleagues have to updates the links in the main workbook, because they have not mapped the network drive in the same way as I have. The problem is NOT the prefix, but the fact that they have a more direct link to the network folder. My collages have mapped the folder like: xclientAApril Is there a way where I can make the links in the workbook, so they don't depend on the entire string, but only on the fact that it is in the parent folder?
Using the developer tab I inserted an Active X combo box. Under properties I referenced a 'linked cell' that I want the data to appear in. This box works great as long as I keep the sheet open. Once I save and close the workbook and re-open, that reference is lost. It shows #REF! in that field. Why does it lose the reference? I have tried to reference a cell on the same sheet as well as a cell in a different sheet and it keeps the sheet name but not the individual cell.
I need to have a macro written which will allow me to extract information from any given workbook and copy them to my workbook. I'm thinking along the line of creating an input box upon opening the workbook, asking for the name of the workbook which contains the data. I would then use the data from the workbook and extract bits of information which I would copy to my current workbook.
However, I can't seem to figure out how to direct the answer of the input box to extract the information out of the target. And how would I refer to the workbook with the data in my code? I would need to refer to the data workbook as I need to extract information from it.
I am trying to find the last row of worksheet "A", return the value, then in Worksheet "B", reference Worksheet "A" Column J - last row. It gives me an error for the formula. I don't know if I am close to the solution, or way off. Here is my code:
Dim LastRow As Long Sheets("NICMap31 Data").Select 'goes to worksheet A Range("A1").Select LastRow = Cells(Rows.Count, "A").End(xlUp).Offset(1).Row 'finds the last row of worksheet A Sheets("NIC MAP Data Table").Select 'returns to worksheet B Range("C7").Select ActiveCell.formula = "='NICMap31 Data'!(J & LastRow - 1)" 'link to worksheet A with this formula.
Is is possible for me to copy a linked formula down instead of accross. For example, i have two tabs in a spreedsheet. the first tab have data in the following cells a1; a2; a3 but going through column M. Basically, a1;a2;a3 have data through m1; m2; m3...
On the second tab, i am linking from one cell to the data in a2 from the first tab; however when i copy my formulas it is giving me data from b2, c2, d2, etc instead of pulling cells a2, a3, a4, etc...
Is there a way to copy the formula to pull down instead of across?
Ive wrote some code that goes into a workbook. This code then opens up a "master" workbook which has 18 blank tabs in it and then proceeds to open up 18 other named workbooks one at a time and copy some date from these workbooks to the master (i.e workbook 1's data goes to the master workbook on tab "1".)
I have this working no problem but here is my snag.
The workbook name changes every week to correspond with the date (i.e 1_14DEC2011.xls then 1_21DEC2011.xls).
Ideally id like to be able to create a variable for the latter half of the filename so that i can apply this variable to each filepath but i cant quite get it to work.
A small sample of my code is below (and yes i know it is very blunt but so is my knowledge at this stage!)
Code: Sub collate() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual
We have a set of workbooks with a linked Vlookup formula. When we email the file to staff that do not have access to the linked file, the linked formula seems to change the directory. see below
Original Formula =VLOOKUP($A$30,'G:Variance Reports FY07[Salary Dist Var Repts_Cur Mth.xls]end of July'!$E$76:$G$200,3)
Formula after user opens email with the drive changed automatically to C =VLOOKUP($A$30,'C:Variance Reports FY07[Salary Dist Var Repts_Cur Mth.xls]end of July'!$E$76:$G$200,3)
Note that the user does not have access to the G drive and they are not updating the links when they open the file.
Now this works perfectly fine, but I am wondering if it is possible to replace 2013-004 so that it reads the text from a cell on that sheet to complete the file name.
I want to replace the 2013-004 in B1 with text from A1. I have tried multiple methods but it just always assumes it is part of the file name or breaks the formula.
Column 1 is the name of a property Column 2 either says "Primary" or "Secondary" Column 3 has a date of when some building work is due to be completed.
I need a formula that can tell me how many entries there are in the second column of "primary" sites that have a completion date that is less than the current date.
I currently have numerous workbooks with linked cells to a master workbook, but the linked cells dont appear to carry over or retain the formating from the master workbook (e.g. font style, bold, underlined, font color, cell color). I make changes quite often to the master workbook and would like the changes carried over to the other workbooks. Does anyone have a script where it would automatically carry over the font formatting? I hope this is understandable.
I have create a menu with buttons that are attached to the different projects of vba. This menu will be put in the server of my department so all my colleagues can take profit of the macros. Is a menu in an excel file that only contains the vba projects, not the sheets where the macros are supposed to run. My problem start when you want to open the file. It will always be in the same disk "P:COAP", and then there are more subdirectories (year, month)... so the total path, for example, this month is:"P:COAP2007200706". But every month, it changes, logically.
The problem is that i need to open a file with the name "COAP-200706 Concentració Empresa Emissora" in this case... the only thing that changes is month, and year... I want to close the booke i choose, to avoid errors, if the name of the workbook chosen is different to a part of this string. In this case, i'm trying to close the book if the name of the file or the book don't contains the string "Concentració Empresa Emissora", without saving changes, of course... because sometimes people have chosen badly the files and they've made a disaster. If the file chosen contains this string in the name i want to continue with all the code that is written after.
ChDrive "P:" ChDir "P:COAP2007" NouFitxer = Application. GetOpenFilename If NouFitxer = "" Then Exit Sub Workbooks.Open Filename:=NouFitxer If ActiveWorkbook.Name <> "*" & "Concentració Empresa Emissora" & ".xls" Then Msg = MsgBox("Error in the file opened. Look at the name of the file next time." _ & vbCrLf & "The program will close without finishing the process. Start again. Thanks", _ vbOKOnly + vbCritical, ("END OF PROCESS")) ActiveWorkbook.Close Savechanges:=False Exit Sub End If
I'm trying to find a way to create an excel sheet for my company. It will be used to import into our CMDB. Its for computers(ID) and their monitors(LINKED TO) We have made 3 rows: ID, CHECK and LINKED TO which consist of the ID's of each computer and the attached monitor. The CHECK row is where we put the word "OK" if that computer is physically present at that location. After this list was made, we decided NOT to link the monitors to the computers, so afterwards we will delete the row LINKED TO.
What i need to do now, in order to not have to seek every monitor manually, is to have a formula which looks up an id (i.e. WBE01111) in the LINKED TO row, and if that one exists in the ID row, it will place the word "OK" in the CHECK row.
Example: (with WBE01111 being a monitor attached to computer WBE03333)
[ID]_____________[CHECK]______[LINKED TO] WBE03333________ OK _________WBE01111 WBE09999________ OK ________ WBE08888 WBE01111________(**)_________
**=formula must put OK here
So the WBE01111(monitor) below [LINKED TO] exists in the [ID] row, i want the formula to see that, and put OK below [CHECK] for WBE01111
I am trying to write some VBA that will select a row of cells that each have links to cells in another workbook, and then either autofill the formulas down (or pastespecial them down) for 20 rows. I have that part down using either pastespecial or autofill, but -
Since the forumulas are links to another workbook, Excel wants to resolve that link to calculate the values at the time of the pastespecial or autfill. If the sourced workbook is not open, the "browse for file" popup displays. I don't want the popup to display and would rather just get the #REF in the pasted cells and let it resolve the next time the sourced workbook is open. The end result would be like clicking "cancel" to the "browse for file popup", which I'm tired of constantly doing... I would just like to skip that popup all together and get the #REF value.
I have an workbook with pictures that are linked to external picture files, and they are placed in the workbook via macro. When I email the file to another computer and open it, the pics do not display and show an error:
The linked image cannot be displayed. The file may have been moved, renamed, or deleted, Verify that the link points to the correct file and location.
I would like to be able to save the workbook with the pictures in them (without having to archive them) so that I can open it and see them on another machine.
I am trying to create my household budget in Excel, but there is a sub-category.
I need a formula which will calculate the following:
Under the "Daily living" section, I have typed the names of the sub-categories, eg: , Groceries General Household Cleaning Education/Lessons Cash
I want it to find all totals next to the word "groceries" in the main part of the spreadsheet, where I type in my credit card statements according to date.
In other words, I want it to add up all my entries/totals next to the word 'groceries' and show the total up at the top of the document, in the "amount" column so I can see the monthly category totals at a glance.
