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.
View 11 Replies
ADVERTISEMENT
Feb 20, 2008
i want to return a value from a cell in another workbook where part of the the workbook name is determined by a date set on the current open sheet.
so on my open sheet i have a cell where the user can change the date and i want the below formula to then use the date to look at the relevant file.
any ideas would be appreciated. know how to do this in VBA but no clue with formulas.
what i am trying to do is concatenate part of a file name and a cell value which contans a date
=VLOOKUP(A4,[accountsummary17022008.xls]Sheet1!$A:$B,2,FALSE)
View 3 Replies
View Related
Sep 27, 2006
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.
View 4 Replies
View Related
Dec 19, 2007
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.
View 9 Replies
View Related
Sep 28, 2009
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)
View 4 Replies
View Related
Mar 13, 2007
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.
View 6 Replies
View Related
Jul 9, 2014
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.
View 1 Replies
View Related
Oct 17, 2008
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.
View 8 Replies
View Related
Jan 19, 2010
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.
View 5 Replies
View Related
Mar 15, 2013
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")
[code].....
View 2 Replies
View Related
Apr 4, 2008
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?
View 3 Replies
View Related
Feb 11, 2013
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.
View 13 Replies
View Related
Apr 10, 2007
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.
View 5 Replies
View Related
Jun 10, 2008
Is there a way to extract a specific part of the workbook and save it as a value in VBA:
For example:
Workbook title is "Data Set 2007" How would you extract 2007?
View 5 Replies
View Related
Oct 15, 2013
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.
View 2 Replies
View Related
Aug 19, 2008
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?
View 9 Replies
View Related
Dec 22, 2011
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
[Code]....
View 2 Replies
View Related
Oct 14, 2006
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.
View 4 Replies
View Related
Feb 20, 2014
I have the following formula/array:
='fake folder location[2013-004.xlsx]MgmtRpt'!$A$4
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.
Example:
A
B
1
2013-004
='fake folder location[2013-004.xlsx]MgmtRpt'!$A$4
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.
View 2 Replies
View Related
Feb 10, 2013
Essentially I have three columns.
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.
View 3 Replies
View Related
Mar 9, 2008
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.
View 4 Replies
View Related
Jul 26, 2007
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
View 2 Replies
View Related
Aug 1, 2007
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
View 2 Replies
View Related
Jan 19, 2008
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.
View 3 Replies
View Related
May 4, 2007
calculate a formula for the following: 1 per 25 for the first 50 and 1 per 50 for the remainder exceeding 50 ad infinitum (always rounding up).
View 6 Replies
View Related
Apr 28, 2014
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.
View 1 Replies
View Related
Aug 9, 2014
I want to replace a part of a formula with its value.
eg say I have a formula =vlookup($a55,$a$2:$e$50,column(),false).
I want to replace column() with its value. There will be plenty of cells with this formula What can be the best way of achieving this.
View 3 Replies
View Related
Sep 18, 2009
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.
View 10 Replies
View Related
Oct 4, 2011
I am trying to modify a cell formula with the Replace function.
Code:
The Formula in the target cell =
='C:Documents and SettingsHoliday macro[Holiday2011.xls]Year 2011'!C5
I am using the below code but it returns just the end portion ='Year 2012'!C5
where I expect just the year to change
='C:Documents and SettingsHoliday macro[Holiday2012.xls]Year 2012'!C5
Where last year = 2011
Yr=2012
Range("C3").Formula = Replace(Range("C3").Formula, LastYr, Yr)
View 2 Replies
View Related
Jul 24, 2012
I have a dump which looks like below sample:
3
3
2
2
3
3
2
2
[code]....
The actual dump will have around 200 - 500 rows of data.
From the above I need to manually group them which are similar.
E.g. the data of (3, 3, 2 , 2) in row 1 & 2 are matching, so they will be group 1 & row 3 will be group 2, row 4 will be group 3 respectively.
The sum & sort did not work for me as sometimes the data with the same range are not in same order.
E.g of the output below for the above data:
Group No.
1
3
3
2
2
1
3
3
2
2
[code]....
View 2 Replies
View Related
