Referencing Or Addressing Second Workbook
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
ADVERTISEMENT
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
Nov 16, 2008
I want to do is copy data to my working sheet (say sheet-1) from other worksheetx (say sheet-2, sheet-3). That's easy enough, but I want to be able to indirectly address "sheet-2" or "sheet-3" from a cell in sheet-1.
Look at the attachment. The data under cost A, cost B, cost C is from other sheets in the same workbook. I want to able to type in "sheet-2" in the first column and Excel to automatically copy over the data in columns 2,3,4.
I do not want a VBA solution. I know this can be done with built-in Excel functions because I did it before. Unfortunately, I lost that spreadsheet and I can't recall how it was done. I tried using Indirect function, but it returns a ref# error.
View 5 Replies
View Related
Oct 16, 2008
I have a named block, used to sort a list of data. But I names the block one row higher to include the headers, so that inserts at the top row of the data would not fall out of the named range. Then I want to sort the data, but not include the header row.
What I want to say in the sort macro is something like:
cell_range( top_row(Data)-1, Bottom_Row(Data) )
Or, to have two names, with one defined in terms of the other:
Data_sort = Data_big - Toprow(Data_big)
View 4 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
Jun 8, 2014
Is it all possible to address controls on a user form indirectly?
Thus instead of say
kv=me.textbox10.value
Could you do something like...
i=10
kfield="me.textbox" & format(i,"99") & ".value"
kv=evaluate(kfield)
View 1 Replies
View Related
Jul 18, 2006
how does one alter a variable without referring to it directly? Eg. One has a combo box (CboPNum) with values from 1 to 20 and textboxes (TxtN1 to TxtN20). Upon selecting a value from the CboPNum dropdown, the background of the corresponding TextBox should be changed. I’ve tried to create something that joins the string “TxtN” and the CboPNum value:
ThisTextBox = “TxtN” & CboPNum.Value
ThisTextBox.Background = Black
But this quite correctly gives the error “Object required”… What do you guys recommend?
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
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
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
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
Mar 16, 2009
I need to ask user to input the row ("rowin") and column ("colin") address in order to activate a cell. The mose natural inputs from the user will be an integer for row and one or two alphbats for column. Say, for example, the user input (82, AY), how do I represent the cell in code? I know I want to say something like Range("AY82"). But I just could not figure out how to concatenate "colin" & "rowin" to make it AY82 to Excel.
View 4 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
Dec 23, 2009
I have a Formula question which I can't seem to figure out. I want to set a cell so that when two other cells are above 80 then the cell is green.
If one of the two cells is bellow 80, the cell turns to yellow and if both are bellow 80 then the cell is yellow.
View 2 Replies
View Related
Jun 6, 2014
I am trying to take the year from today's date then subtract away a date from a referenced date in a separate workbook. Then I want to put this result into an IF statement. For example if the result is between the number 1 and 4, display 1, if the result is between 5 and 9, display 2, etc. Right now, I'm simply trying to reference the separate workbook for the date and subtract that from today's date, but I'm stuck because I only get the date's serial number back.
View 1 Replies
View Related
Apr 21, 2012
I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.
How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?
View 1 Replies
View Related
Oct 1, 2008
I was wondering if using INDIRECT() function, or something like it in VBA, I could set a range as a reference instead of a fixed value... example below: Instead of the range being fixed on the first line,
View 4 Replies
View Related
May 30, 2007
I have a drop down menu where the user can chose between a,b,c and d. I would like to use this further in a formula. The formula is defined by me, although I dont think its relavant.
Lets say the drop down appears in B2, and I have got a formula called
Test1(B2). However this retunrs me an error. The contents of B2 is in string format.
- Test1(B2) does not work
- Test1(a) does work (assuming a is what B2 shows)
I would like to know how I can reference B2 as an argument in Formula, so it does not show error.
View 9 Replies
View Related
Oct 21, 2008
I have 2 columns of info. 1 is a complete list. The other has many of the same values, but is missing a lot of the values in column one. I am marrying the two lists to see where the data gaps are. Both lists are from different databases and I need to know which data the second DB is missing. Currently I am lining the 2 columns up and doing it manually. Is there a way to do this quickly as there is about 1000 records.
View 2 Replies
View Related
Dec 2, 2008
looking for a formula that will get me the sheet name.example: I have 3 sheets each named X, Y, Z. in cells A1 I want the name of sheet 3, which would be X
View 2 Replies
View Related
Dec 19, 2008
I need to create a worksheet whose sole purpose is to return the values from every 17th row from another worksheet in the same workbook.
I have spent a few hours trying to research the correct answer and OFFSET comes close but either I'm doing it wrong or it's not what I need.
I want to be able to drag the (every 17th row from sheet 1) formula down about 100 rows.
Assume A1 as starting place. I would prefer a formula answer as opposed to a macro or VBA.
View 13 Replies
View Related
Jun 24, 2009
way to reference the name of a worksheet in a cell, or even better, use a cell reference to dictate the name of a worksheet.
In other words, I would like to create a new sheet, and set it's name ='Sheet1'!C1 so that if I changed C1, the worksheet would automatically be renamed to whatever is typed in C1.
If that is not possible, I would like to have a cell be tied to the name of a given worksheet, so that if I renamed the worksheet, the cell would change accordingly...similar to the &[Tab] function used in headers/footers.
View 13 Replies
View Related
Dec 23, 2009
I have an XLS spreadsheet which updates cells through an ODBC connection to a database. The information is pulled across ok and 90% of the Vlookup and IF statements work. The problem arises on a summary page.
When i start off the summary page has cell references in numeric order to another spreadsheet and pulls back the information to 4 cells Across and 1000 Down. ='Campaign Responses - Cash Break'!A2
This is a spreadsheet that changes once the queries have run and a number between 1-1000 records are returned. However once the data has changed and the page refreshed. Not all the results have been pulled across to the summary pages. A few are there but the numeric sequence jumps from......
View 2 Replies
View Related
Oct 23, 2013
I have several options in one list, call it MASTER.
When I pick Item A from the MASTER, I want to show all available options for that Item A in another list, called OPTIONS.
I have 10 Items, and they all have a variation of 6 different options, some Items have 1 option, some have all 6.
Anyway, the worksheet I found is attached, but cannot figure out how this is working.
View 14 Replies
View Related
Oct 7, 2008
I have made 2 macros that run one after the other to perform a certan task. My issue is that I want to run Variants that I have defined in Macro1 In Macro2 but I dont know how to reference them macro 2, or if there in the same module do they recognize the varients anyway??.
I'll list the variants defined below; could someone provide me with the proper code to reference the variants form macro1 in macro2. I am also wondering if it would be easier to combine both macros under one macro and simply put a pause in the macro between both section and have a certain command to resume once the user decides to( so it would have to be a user command such as a button). If you think this would be easier could you please provide the code to pause the macro until A button is hit. both these methods woud be useful to me elsewhere as well so if it's not to much to ask could you provide both methods.
View 2 Replies
View Related