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.
View 6 Replies View RelatedI 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 RelatedI 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.
View 5 Replies View RelatedI'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 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].....
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.
View 13 Replies View RelatedI am working in an in depth set of files and equations. When I open my main file and click "Don't Update" for the links, everything looks fine. However, if I then change a formula in a cell, that cell then gives me a #VALUE error, indicating that it is not re-evaluating my equation. How can I fix this without clicking "Update" upon opening the file, because there may be other changes in other files that I do not want pulled in upon opening? It seems the errors are occurring only on the conditional evaluations.
=SUMIF('H:Tavares - Temp for PlayTavaresUser Rate StudyCustomer DataBF Analysis[Irrigation.xls]Irr Res In'!$I$2:$I$13835,C7)
gives me #VALUE
but
=SUM('H:Tavares - Temp for PlayTavaresUser Rate StudyCustomer DataBF Analysis[Irrigation.xls]Irr Res In'!$I$2:$I$13835)
gives me 229823.18
and =C7 gives me <=0
My SUMIF function only works when the workbook that I'm referencing to is open. When it's closed and I open the file it shows #VALUE!. How do I fix this problem?
View 2 Replies View RelatedI have a sumif formula in one workbook that relates to information off of another workbook...when the other workbook is open the information populates no problem when you close the other workbook I get the #VALUE error. Is this typical of SUMIF when referring to another workbook? I have a vlookup function that refers to the same workbook and it works fine when the other workbook is closed?
In the attached workbook, see the "P & L" tab, cells I30 to I48. The idea of the formula I have placed into these cells is to drag out the figures from column I in the "Trial Balance" tab of the workbook. A similar formula is used succesfully in the "balance sheet" tab in Column B. I don't understand why the formula doesn't work in the cells i've specified in in the " P & L" tab it seems to work for I8 and the rows immediately below it "revenue".
UK Trial Balance May 2014 V2.xlsx‎
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 pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
View 9 Replies View RelatedI need to perform 2 SUMIF's on 2 columns of data to return a result and I'm not quite sure the best way of doing this. I'll give an example below.
I have 2 columns of data, both numeric and the SUMIF needs to say if H1:H100="10" and also if J1:J100="907". I can perform one or the other but not both.
I have many kitchens using the same recipes. I need to distill information down until I've got a summary of how much is being made. Uploaded is a condensed version of the point in the process I'm having difficulty with. This workbook will pull information from 8 other workbooks and give me excatly what everyone made on any weekday.
And from there, with the kind help of this forum, I figured out how to do a SUMIF based on the recipe number. And it summed up all instances of 'Recipe X' being used. However, it continues to SUMIF itself all the way down the page... which is good, because of how recipes are chosen for each kitchen. However, I only need to report one instance of each recipe.
In the uploaded example (and I apologize for the colorful sheet, but it helped me double check what I was working on.) ... I only need to report the PURPLE results elsewhere... the first instance of each SUMIF.
I am wanting to use these two formulas in one cell. Is there anyway to do this? If "AD3" is 0 I want this =SUM(X3:AC3) and then if cell "AD3" is greater than 0 I want to basically use this formula
=SUM(AH3,X3:AC3)-AD3.
Is there anyway to merge these two formula's?
I have a cell with a link to a closed worksheet.
The problem I am experiencing is when the linked worksheet is open the data found and the cell automatically populated.
When the linked workbook is closed, I get nothing.
If I check links/source I get the message Error: Undefined or non-rectangular name, neither of which are true statements.
Linked workbook closed
Management - Prepared HVHWHXHYHZIAIBICIDIE7 £ - £ - £ - £ - £ - £ - £ - £ - £ - TRUESpreadsheet FormulasCellFormulaHV7{=SUM(IF(ISNUMBER(SEARCH("SALAD ONION",'L:Customers StatsSaladsDatabase salads2.xls'!Description)),IF('L:Customers StatsSaladsDatabase salads2.xls'!Period>=$HQ$7,IF('L:Customers StatsSaladsDatabase salads2.xls'!Period<=$HQ$6,'L:Customers StatsSaladsDatabase salads2.xls'!SaladVal))))/1000}HW7{=SUM(IF(ISNUMBER(SEARCH("SALAD ONION",'L:Customers StatsSaladsDatabase salads2.xls'!Description)),IF('L:Customers StatsSaladsDatabase salads2.xls'!Period>=$HQ$11,IF('L:Customers StatsSaladsDatabase salads2.xls'!Period<=$HQ$10,'L:Customers
Linked workbook open
Management - Prepared HVHWHXHYHZIAIBICIDIE7 £ 3,624.89 £ 4,144.82 £ 373.62 £ 664.80 £ 764.06 £ 373.62 £ 235.14 £ 263.18 £ 314.38 TRUESpreadsheet FormulasCellFormulaHV7{=SUM(IF(ISNUMBER(SEARCH("SALAD ONION",'Database salads2.xls'!Description)),IF('Database salads2.xls'!Period>=$HQ$7,IF('Database salads2.xls'!Period<=$HQ$6,'Database salads2.xls'!SaladVal))))/1000}HW7{=SUM(IF(ISNUMBER(SEARCH("SALAD ONION",'Database salads2.xls'!Description)),IF('Database salads2.xls'!Period>=$HQ$11,IF('Database salads2.xls'!Period<=$HQ$10,'Database salads2.xls'!SaladVal))))/1000}HX7{=SUM(IF
How can I link the names of severall employees in a combo box (or data validation cell), with specific and individual arrays?!?!
View 3 Replies View RelatedI 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.
I have to fill out two cells (lets say A10 and A11), with the same data taken from a list of given data. The given data is placed in one column.
I have created Combo Box where the ListFillRange is the given data area and in the LinkedCell I tried to write two cells but Excel doesn’t accept list of two cells.
For example, I tried to write in the LinkedCell A10,A11 and A10:A11 and A10;A11 but none of this syntax worked.
I can’t write A10 only in the LinkedCell and then to write a function A11=A10 in cell A11, because in some cases the content of cell A11 will be changed by another Combo Box linked to cell A11 and that Combo Box will override any function that will be written in A11.
either writing a formula or coming up with some way to filter out certain values and it's stumping me... it's probably way more simple than I'm making it I've got an attendance spreadsheet with employee names going down the first column and dates going across the top row, managers use codes to indicate sick, personal time, etc.
There is a separate spreadsheet for supervisors to track vacation (don't ask me why) and I was asked to link the data from that spreadsheet into the attendance one described.
Linking is no problem, since it's the beginning of the year I'll just do a copy & Paste special.... paste link and they can still use that 2nd vacation spreadsheet to update both places for vacation time. However.... the vacation sheet has some extra info they don't need/want to see on the attendance one (ie. Lv @ 12:30, Holiday, etc.) How can I filter that out so that the only thing that appears is the V for Vacation that is linked & other attendance codes that are entered (S for Sick, etc).
I'm using Excel 2007
I am having issues sorting columns on a given worksheet. When a column is sorted in one tab, it is jumbling another column on a linked tab. Vlookup formulas are being used throughout the worksheet.
So, for example in the attached file;
Currently the way the file is set up is when a new project comes in, we put initials in the 'Audit Date' column of the 'To Be Audited' tab. That then pulls over to the 'Customer' tab via the 'No Edits-Audit Sheet'.
I want to be able to sort in the 'Customer' Tab the states, city or state A to Z and not have it mess up the 'To Be Audited' tab. Right now, when someone sorts one of the columns, the initials in the 'to be audited' tab jumble, and the initials are no longer with the original project.
Example.xlsm
I have the following details
A B
1 Jim 3rd
2 Jack 5th
Now in the new cell i need A1 + B1 with / in between
Like Jim/3rd
Jack/5th
I want to use the "linked image" feature.This works well but when I don't know how to use it with a named cell (ie : "=$A$1:abcd" where abcd is a cell that I named).
View 2 Replies View RelatedI have several cells in my spreadsheet A which are linked to cells in another spreadsheet B in the same workbook. My problem is that the cells in spreadsheet A only display the link formula in the cell and not the value of the cell in spreadsheet B which it is linked. I have several other cells in spreadsheet A that are linked to cells in spreadsheet B and they display the values correctly.
View 12 Replies View RelatedI have a Drop Down / Combo box and when a value is selected I want it to change/update multiple pivot tables.
I am trying to get it to change for one pivot table first and I have the following
Sub DropDown1_Change()
ActiveSheet.PivotTables("PivotTable1").PivotFields("SMS").CurrentPage = [NOT SURE WHAT GOES HERE]
End Sub
I have read somewhere that I can't link directly to the values in a combo box, but I can use the cell link? If so, how do I do that?
I have a workbook that has a cover sheet which contains the names of all the tabs in the workbook. I need to be able to change the names in the cover sheet and the names in the tabs to be change to whatever the corresponding cell in the cover sheet text is.
View 14 Replies View Related