Formula Referencing Another Workbook?
Mar 20, 2012
I'm having an issue with a formula that references another workbook, and I'm not sure what the culprit is.
The formula is this: =IF(ISERROR(E4/VLOOKUP(D4,'C:Documents and SettingsusernameMy DocumentsRoom Capacity Master List.xlsx'!Table2[#All],2)),"",E4/VLOOKUP(D4,'C:Documents and SettingsusernameMy DocumentsRoom Capacity Master List.xlsx'!Table2[#All],2))
It works just fine on the initial pass. When I reopen the book and it prompts if I'd like to update the data, and I select yes, everything goes away. Cells E4 and D4 are both part of a PivotTable, not sure if that is important or not. This is happening on Excel 2010 running on XP.
View 2 Replies
ADVERTISEMENT
Nov 8, 2006
I am trying to create a one-page spreadsheet which links info from other various spreadsheets. There is one spreadsheet I am trying to link to which has information listed where Column A is the product name, Column B is the UPC and Column C is the sales rank. I created an offset formula that pulls the top 5 rated items by sales rank (Column C) into my new spreadsheet, but whenever the source workbook is closed, I get error messages. Is there a way around this so the workbook I am pulling my information from does not have to be open.
View 2 Replies
View Related
Apr 19, 2008
I have 3 Workbooks. I want a reference in Wookbook1 that will be valid for both Workbook2 and Workbook3 based on which one is opened.
If Workbook1 is opened and Workbook2 is also opened, a Worksheet in Workbook1 will be populated with information from Workbook2.
If Workbook2 is closed and Workbook3 is opened, without changing the referencing or links in Workbook1, Workbook1 will again be populated with information from the opened Workbook3.
View 9 Replies
View Related
Jan 22, 2009
I have a workbook (Workbook A) which I need to lookup ( Vlookup() )something in a different workbook (Workbook B). In workbook A I need to lookup many (I mean hundreds) of values.
My question is: What do i put in the function tab (where we put the formulas and references) to lookup values in different workbooks? And if possible, is there a way to define a workbook A to ALWAYS look up values in a defined workbook B.
View 3 Replies
View Related
Mar 21, 2013
I have got a master workbook and I have written macro to copy and paste data on another workbook. write a macro to save the new workbook to a file path with a file name where both file name and path are stored in master workbook sheet...
View 5 Replies
View Related
Feb 24, 2014
From the Active Workbook how do I run these commands?
The syntax is way wrong, but will show what I'm after...
Code:
debug.Print Workbook("1990a.xls").Worksheets("Sheet1").cells(20, "A")
Workbook("1990a.xls").Worksheets("Sheet2").cells(20, "A")= "testing"
View 3 Replies
View Related
Apr 17, 2009
I have two sheets in the same work book and I want to pull values through from one to the other. So its a simple =Sheet2!B45 or whatever yeah?
But no! What Excel 2007 does when I hit enter is to swap the Sheet!2 bit for "C:documents and Settingsmy documentsspreadsheetsmyworkbook!Sheet2!B45" or some such.
Why the hell does it do that and how can I stop it.
View 9 Replies
View Related
Nov 29, 2012
I have a challenge with Vlookups!
I have one workbook called Ratecards, which contains various tabs which are named based on a client code. e.g. ABC02
I then have another workbook which has employees in column B and the client code in C3.
The current Vlookup is =VLOOKUP(B8,'[ratecards.xlsx]ABC02'!$A$5:$N$168,4,false) which works fine for one client.
I want to substitute the ABC02 in the vlookup for cell ref C3, as the contents of C3 is variable and I want it to tell the sheet which ratecard to refer to.
View 3 Replies
View Related
Jun 12, 2006
Is it possible to reference a named range in a workbook that is not active, one that has been closed but still available within a directory. This closed workbook has named ranges that are being added to from time to time so the named range is growing.
This workbook is to be use as a master list of all data that will be used for validation, VLOOKUPS and what ever other types of referencing I may need in other workbooks which are ALL created from the ONE template, so each workbook will be setup exactly the same, but NOT the MASTER LIST OF DATA.
If it is possible I'll definately need help with code.
Regarding the Template I'm using, I'd like to add a new sheet and have the functionality of buttons and drop down boxes to access saved workbooks (which will all be in the same directory) and also the ability to create a new workbook from my template by perhaps using a button.
View 7 Replies
View Related
Jul 30, 2007
I've written somewhat complex code (well complex by my standards) on my own computer and I have tried to put the project onto another computer so that they can use it. The code involves opening a workbook and then saving it under the variable name strName1. Then it activates another workbook and puts information into that workbook from the strName1 workbook. The strName1 workbook is open this whole time. However, I get an out of range error when trying to do this part (the code is shown below).
ActiveCell = Workbooks(strName1). Sheets("Basic Overview").Range("B1").Value
I am confused because this works alright on my computer, I have copied the root directories for the file exactly as it is on my computer and when debugging the code I can see that it has picked up the variable strName1 correctly (so this isn't the problem). I've also tried writing the entire root directory in insetad of just Workbooks(strName1) but it still says its out of range.
View 6 Replies
View Related
May 4, 2009
I'm reading data, from specific cells off a closed workbook. When the sheet that needs said cell data is activated, it automatically opens the workbook and references the sheet nessecary. The issue I've come across, is I now need to access another workbook (Easy to open) with 12 sheets 1 for each month, and only read from the worksheet of the actual Month...
Kind of lost on how to possibly make this work. I basically need something like:
=location/[workbook.xls]Month(Today())!cell
View 9 Replies
View Related
Oct 4, 2005
I have tried using PULL from Harlan Grove's posts to workaround this but am coming up with #VALUE errors.
Here's what I have:
Column B contains the acct # being referenced e.g. 5230
Column E="_"&Br where r is the row #
Column F=MATCH($B$3,INDIRECT(Er&"Rows")) where _acct#Rows is a named
range referring to an external workbook e.g. _5230Rows
Columns G-R=INDEX(INDIRECT($Er),$Fr,COLUMN(G$6)) where_acct# is a named
range referencing an external workbook e.g. _5230
I thought that INDEX($Er, $Fr, COLUMN(G$6)) should work in Columns G-R however it returns #REF!
I need a solution to replace INDIRECT so I do not have to have both workbooks open together. The named ranges are static, but reference external workbooks.
View 13 Replies
View Related
Apr 15, 2014
At my work we are using Excel as a CMS to produce SQL scripts for insertion into a database. The data is then turned into mobile web pages. This involves multiple workbooks - 1 for each main page with the sub-pages as extra worksheets in the book.
Much of the data is duplicated so we have created master workbooks then copied them and laboriously referenced the relevant cells back to the original. That way if any data in the master is changed, all copies will change simultaneously.
This is obviously not the most efficient way of publishing web pages but we are stuck with it for now. So I am wondering if it is possible to write a VBA code to create a copy of the master and then reference all required tables in the worksheets automatically? The tables are all named ranges.
View 9 Replies
View Related
Mar 26, 2014
I have a long list of amounts one per day. Sun-Sat repeating. I Total the amounts for each week and place it next to Sun. now i have the total for that billing week.
I need to reference these numbers from a different sheet that only has one row per week. I tried to do ='sheet1'!a2 then next row down ='sheet1'!a9 and ='sheet1'!a16 etc then fill down but when i do it seems to start again and do a3, a10 and a17.
Is there a way I can get it to only use cells with figures in them? IE if cell is empty then move down one else use the amount in cell... or how do I get it to reference 7 cells below each time rather than the next immediate cell.
View 8 Replies
View Related
Dec 8, 2011
I have the formula:
=VLOOKUP(C10,'[Ticket Out Comparison Report.12.4.11.xls]TO'!$C:$O,10,0)
I want to do something like putting 12.4.11 in cell B1 and change the formula to something like:
=VLOOKUP(C10,'[Ticket Out Comparison Report.B1.xls]TO'!$C:$O,10,0)
View 9 Replies
View Related
Apr 16, 2013
Here is what I have
1. Have a COUNT value in a cell (counted the number of rows with values in them).
2. A formula that needs this value (sums another set of values in another column that has the same number of rows).
Example: the COUNT value is 12, and is located in cell G5.
For simplicity sake I have a formula in cell I5 SUM(F1:F12).
Instead of directly referencing F12, I want the formula to be SUM(F1:F(G5)).
View 5 Replies
View Related
Oct 24, 2013
Just got a problem with auto-populating cells with a formula that is made up of variables. My formula references the cells on another sheet, but this will be dynamic.
I'm trying to get the formula to reference e.g. cell A1 in the Calls Taken sheet, with the column held in the srtshortname variable and the row held in the formularow variable.
Code:
set rng = range(cells(introwdate, formulacol), cells(introwdate, formulacol))
rng.formula = "='Calls Taken'!" & strshortname & "" & formularow & ""
View 2 Replies
View Related
Nov 16, 2006
I am having trouble with the following formula in my workbook with many sheets: ="='"&MONTH(A6)&"-"&DAY(A6)&"'!"&"$B$13". I entered this formula into B6 of the current sheet. Cells A6:A33 contains a date (02-Jan-07). So what I'm trying to do is retrieve the value in B13 of the sheet corresponding to the month and day in A6. If I hard code this formula into B6 ='1-2'!$B$13 it works but I don't want to edit each of the formulas in column B to correspond to the correct sheet. When I type in the above concatenated formula it just returns text (='1-2'!$B$13). Is there a way to do this so the formula will actually execute?
View 3 Replies
View Related
Mar 3, 2014
I've come across a problem I can't seem to solve. I have a cell that contains =AVERAGE(D8:AH8) which was working fine. I made some changes to my sheet and now D8 through AH8 contain a number arrived at through a formula ( for example =D6/D7 ) instead of just a hard number, and my average formula no longer works.
View 3 Replies
View Related
Jun 3, 2014
I want to use the values in a range of cells in a sheet for a formula in another sheet. The problem is that these range of cells already has formulas and when I select these cells for use in another formula, the values doesn't get picked up and my formula doesn't work. for example -
cell L4 in sheet 1 has a formula - ='H:VK_2011_onwards1_ACC_BJS_SAA 6
and it has a value - 34.76
cell L5 in sheet 1 has a formula - ='H:VK_2011_onwards1_ACC_BJS_SAA 6
and it has a value - 47.09
Now I want to use this two cell to calculate an average in sheet 2. So if I use the formula =AVERAGE(Sheet1!L4:L5). I get no result.
View 2 Replies
View Related
Sep 8, 2009
I'm a bit over my head on this one. I want a formula that does the following: Look at the date I put in on the last tab and find the correct date on the other tabs. Using that date as the column I want it to return the correct row for the data.reference.
I am using the HLOOKUP function. I'm not even sure this is the right function. Ont the workbook attached I'm trying to get the data on the Totals tab to come from the Sept Wk 1 through Sept Wk 5 tabs. The formula I tried to use is on the Totals page C7.
View 3 Replies
View Related
Feb 23, 2012
Im using the below formula to paste information into a new spreadsheet based on the if then statement. However, as i paste the formula down the sheet, it doesnt reference all the cells. The formula stays on the cells it referenced initially. How can I make the formula reference all the cells as i drag it down?
=IF('[All Cases ATL.xlsx]New_All_Case'!$C$2="DA1",IF('[All Cases ATL.xlsx]New_All_Case'!$T$2="A",'[All Cases ATL.xlsx]New_All_Case'!$E$2,""),"")
View 3 Replies
View Related
Apr 26, 2012
I have data in every cell within a column. I want to pull data from every 7th Cell.
Ex.
Cell CP7 needs to reference AG10
Cell CP8 needs to reference AG11
View 8 Replies
View Related
Mar 11, 2009
I'm having a problem with the below array. The column heading in cells E7:H7 Never change and i would like to sum up the appropriate data below those headings according to the what is in cell D4. The formula in cell A9 works but only if the string is exactly the same as what is listed in cell D4. Is there a way to reference cell D4 like i have in cell B9. I'm looking to get the formula in B9 to work, but i'm missing something.
Sheet1
ABCDEFGH2Assumptions 3Collateral Sample 1{60;90;FCL} MACRO Populates 4Collateral Sample 2{90;FCL} {60;90;FCL} 5Collateral Sample 3{FCL} 6Collateral Sample 4{60;90;FCL} 7 Current6090FCL8 990 123410200 658711360 4682212
Spreadsheet FormulasCellFormulaA9{=SUM(IF($E$7:$H$7={60;90;"FCL"},$E9:$H9))}B9{=SUM(IF($E$7:$H$7=$D$4,$E9:$H9))}A10{=SUM(IF($E$7:$H$7={60;90;"FCL"},$E10:$H10))}B10{=SUM(IF($E$7:$H$7=$D$4,$E10:$H10))}A11{=SUM(IF($E$7:$H$7={60;90;"FCL"},$E11:$H11))}B11{=SUM(IF($E$7:$H$7=$D$4,$E11:$H11))}Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
View 9 Replies
View Related
Jun 29, 2009
After a formula to copy the contents of a cell in the previous sheet in a workbook, so that if I was to copy the last sheet in a workbook the formula would automatically reference the cell from the copied worksheet and so on if I copied tht one.
Hope this makes sense I have a lot of formulas referencing the previous sheet and everytime I copy this sheet to create a new sheet I have to change the sheet number in the formulas.
eg, in sheet 8 this formula get info from sheet 7 cel J30 ='7'!J30 when i copy the sheet to create a new one (Sheet 9) I would like the new formula to automatically be ='8'!J30.
View 9 Replies
View Related
Aug 19, 2009
Reference a worksheet that has not yet been created, avoiding the #REF! result?
The formula I have is correct, however the macro generates new worksheets throughout the month in which the totals sheet is referencing. The problem being that the totals sheet is referencing worksheets not yet created.
View 9 Replies
View Related
Aug 16, 2006
This does almost exactlly what I want. The one problem that I have is that the active cell remains B1 as it enters the formula in the rest of column B. I need it to refer to the row that it is entering the formulas; meaning each formula entered in B2,3,4, etc. is still referencing cells A1 & D1 for sBook & a sFilePath.
Dim sBook As String
Dim sFilePath As String
Dim sLen As Long
Range("B1").Select
sBook = ActiveCell.Offset(0, -1)
sFilePath = ActiveCell.Offset(0, 2)
sLen = Range("A1").End(xlDown).Row
Range("B1").Formula = "='" & sFilePath & "[" & sBook & ".xls]Sheet1'!R1C1"
Range("B1", Cells(sLen, 2)).Formula = Range("B1").Formula
View 4 Replies
View Related
Sep 26, 2006
I have a spreadsheet that for security reasons reads an email address in another proteced spreadsheet using a straight forward formula. ie =Hyperlink('Email!'E2). This prevents users from deleting or changing information. I want users to be able to click on the cell and it will bring up Outlook using the address but it is only reading the formula in the cell and so tries to link to E2. Is there anyway to make excel convert this formula =Hyperlink('Email!'E2) so that it will convert to the email address contained in 'Email!'E2. I thought about having a macro that copies and then pastes value only to another cell so that the email link will work but need to automate this so that all the user has to do is click on the cell.
View 4 Replies
View Related
Jan 17, 2014
How come doing this doesnt work?
=GETPIVOTDATA("Payment",PivotTables!$B$25,"Year",Formulas!J4)
I want to have the item at the end link to a cell reference so it can change.
how to make that work?
View 8 Replies
View Related
Jan 20, 2010
The workbook has a tab for each day, and there are variance columns. The tabs are named for the date, like 1.20, 1.21, 1.22, etc.
For example, the formula on 1.21 in D2 is simply =C2-'1.20'!C2
That works fine, but it's a time-consuming job to always change it. You have one sheet to copy many times for all the other formulas, but then have to re-name and change that variance formula on each worksheet.
Is there a way to reference the prior worksheet without using it's specific name?
=C2-'prior worksheet'!C2 would be nice...I could take my one sheet and copy it about 23 times for each workday, then copy that whole workbook to use in the subsequent months without making any formula changes.
View 7 Replies
View Related
