Accessing Different Sheets Via A Formula

Jan 10, 2010

I am currently accessing data from one worksheet to another using: =INDEX(AD!C:C, MATCH($B9,AD!$B:$B, 0)). Ie from my current worksheet ‘Claim’, I’m going specifically to sheet ‘AD’. My full workbook contains 18 sheets, one for each of 18 engineers and of which, the name of the worksheet is the engineers initials. What I would like to do, is to use a lookup table located on ‘Claim’ which will hold a list of all 18 Initials, so that when an engineer selects himself from a drop down list, his initials automatically populate cell K7.

My question therefore is, is there a way of substituting the ‘AD’ in the formula: =INDEX(AD!C:C, MATCH($B9,AD!$B:$B, 0)) with the contents of K7? Or, if you can see what Im trying to do here, is there any other way of accessing specific sheets by inserting the sheet name into a formula?

View 4 Replies


ADVERTISEMENT

Accessing WinZip Via VBA

Sep 5, 2006

This Sample is the code that I've adopted for my use.

My problem is this. My normal target .zip files are in the neighborhood of 75 - 80 Mb in size. Yes I know that they are huge..... (They are backup files for mainframe extracts ) Each zip file contains in the neighborhood of 2000 files. Currently, I have coded two ways of extracting data. Well really only 1.

1) I specify a location to extract all files. Which is time consuming ast it takes in the neighborhood of ( 5 min I thing for all files to get extracted ).

2) If I let the user specify a file filter parameter ( part of a file name ) while the WinZip shell is extracting files I'm searching all the file names in the dest directory and deleting the files that I don't want.

So what I want to know is it possible ( without buying software ) to be able to get the filenames of the files in a WinZip file.......

View 9 Replies View Related

Accessing Controls

Mar 21, 2007

I have a ListBox in my worksheet "A", and I'm tryin to acces to it... I want to use that ListBox like a log to print the error i have found while running my code. The name of that ListBox is "IncongruenciesListBox".

now if that ListBox was in a userform, i can access to it easily, but if it is in the worksheet how can i change (add more rows) to it?

I tried:

Dim WS As WorkSheet
Set WS = ThisWorkBook.WorkSheets("A")
If added = False Then WS.IncongruenciesListBox.AddItem ("error")

And there is a message saying it cant find "IncongruenciesListBox".

View 6 Replies View Related

Accessing Isblank In Vba

Apr 6, 2007

Is the following valid code in Excel VBA?

If Application.WorksheetFunction.isblank( Cells(RowIndex, ColIndex)) Then

View 3 Replies View Related

Accessing A Worksheet

Apr 26, 2007

I would like to know if it's possible to access a worksheet in an Excel spreadsheet using a pre-prepared string or character-set? Usage is for years consecutive, so worksheets are '1990', '1991', '1992', etc.

In which case I'd like to be able access these with a prepared string '1990', instead of using the hard-coded method for hundreds of data accesses.

View 7 Replies View Related

Accessing The Properties Of A Shape

Apr 2, 2009

How does one access the properties of a shape? For instance getting the text on a button ( from the forms toolbar ) on a worksheet. This works

View 4 Replies View Related

Accessing Elements Within Arrays

Nov 10, 2009

I've been teaching myself VBA for the purpose of processing some time resolved data from an instrument. I've come up against a problem and I just can't work out why the code is failing. (using excel 2007, VBA 6.5)

I declare some arrays as dynamic, then ReDim them to a user defined number (with Option Base 1) and input data from a spreadsheet into the array. I can then output the data into the spreadsheet again and the lbound and ubound functions return the correct values for the array. What I cannot do is actually access individual elements. If I specify array(i) I get the "Subscript is out of range" error. The code relevant to this problem is as below (simplified as there are more arrays):

View 5 Replies View Related

Accessing A Closed Book

Dec 14, 2006

I have designed a spreadsheet that updates automatically and shows the company's perfomance on several sheets.

To add to the use of the sheet I would like to offer the user a preference set up. So they choose the sheet it opens on for example.

I am happy with how to do all the above except for the fact that the sheet is required to be available to all staff! so only one employee can have it open in read/write everyone else will eb read only!

I was thinking the only way around this would be to have a second sheet that saves their environ user name and preferences.

That would require using the sheet closed though would it not?

View 9 Replies View Related

Accessing Worksheets Via A Variable Name

Apr 21, 2008

i have a simple PO tracking system, i have a workbook with multiple sheets at any one time approximately 20, projects come and go and i need to add a sheet periodically, rename the tab to the project ID - 4x numeric.

All this i have managed to do, and sort the sheets numerically, i now need to copy the last (added) row on the Project specific sheet to the last clear row on the summary sheet.

I have also achieved this, but in order to do it properly i need to switch between the specific Project sheet i am adding the current PO to, (any one of 20) and the summary data sheet, the sheet i am copying to.

To achieve this i thought i could assign the TabName (of the Project) to a variable and then refer to it

Sheets(TabName).Select. using msgbox throughout appears to provide the correct results, however, this produces a {subscript out of range error no9} on this particular line. Any suggestions welcome. Same issue i believe i could address my problem if i could whilst in the Project specific sheet i could determine the codename of the sheet which i believe i could then use to switch between sheets programatically.

View 9 Replies View Related

Accessing The Buttons In The Command Bar

May 25, 2007

i am trying to access buttons like ' Format - Column - Hide'

View 5 Replies View Related

Accessing Data From Other Workbook

Jan 17, 2008

How can i access data from other workbook i have opened.?
I am new in VBA.So little code snippet would be good to show.
I want to get the cell data from workbook2 to workbook1

View 9 Replies View Related

Accessing Values Of 'alphanumeric' Variables

Jan 3, 2010

I have declared a number of string variables named "hlpYear1", "hlpYear2" etc in the 'Worksheet_SelectionChange' function. "hlpYear1" contains the appropriate text to display if the selected language is English, "hlpYear2" is for French and so on. I was hoping that I would be able to use VBA to display the appropriate text by way of a 'For..Next' statement along the following lines:

View 4 Replies View Related

Accessing Name Property Of Active Worksheet

Feb 23, 2012

In VB when I select a worksheet, I can amend the (Name) of the worksheet in the properties window. There is also the .Name property which is the same as the sheet tag name.

I can use the (Name) in vb code to identify the sheet e.g.

ControlSheet.cells(1,1)

without having to define ControlSheet as a worksheet first.

How do I access the (Name) which appears at the top of the list of properties in the properties window.

View 1 Replies View Related

SQL Queries And Methods - Accessing Different Database

Sep 4, 2013

Below is an example of how I prefer to work with a database. Results are fast; even across the network and I understand the syntax perfectly.

Code:
Sub DBPreferredWay()
folder = ThisWorkbook.Path & "mydatabase.mdb"
Set db = OpenDatabase(folder)
strSQL = "SELECT * FROM transactions WHERE [SimilarItems] = " & myFocus
Set tR = db.OpenRecordset(strSQL)

[Code] ........

I'm trying to access a different database; a database being hosted on my SQL server. Because of security and what not; it looks like I'm needing to use adodb but I can't stand it and the slowness (minutes to receive queries. IS there a way for me to access my sql server database similarly to how I'm accessing MDB tables across the network? Below is what I've been trying with the adodb

Code:
Sub SqltoACCPAC()
Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
phrase1 = "DRIVER=SQL Server;DATABASE=" & "VNODAT;SERVER=192.168.0.91,1433": conn.Open phrase1, "SA", "password"
strSQL = "SELECT * FROM ICITEM WHERE [SEGMENT1] >= 101 AND [SEGMENT1]

View 8 Replies View Related

Accessing Web Pages Organised With Frames

Mar 25, 2007

How is the data organised in frames in a web page accessed?

View 2 Replies View Related

Scroll Bar Control Not Accessing Last 8 Month Of Data

Jan 22, 2014

I followed the instruction in the TechRepublic article "10 steps to creating a scrolling Excel chart"; [URL].... The problem is that the scroll bar stops working at a certain point and will not bring up the last 8 months of year 4 of my data. I get error messages and the chart turns blank.

I also tried a different method (again for a scroll bar and a rolling chart) as outlined in [URL].... There are a number of similar articles with almost identical formulas. Here, the first 8 month of year one are inaccessible = error messages and chart turns blank.

I have 4 years worth of temperature data. The original data table from where the Index function draws the data is 1463 rows long (minus 2 rows for the header). The "magic" number where the scroll bar stops working is 995 for the current value. From a current value of 994 onwards, the entire matrix displays error messages "Ref#".

In the second case, the magic number is 1242 which equates to August of the first year. 1241 and lower for the current value will freeze the chart.

How the scroll bar control generates and gets hung up on either of these two magic numbers is a mystery to me.

I set the control properties at the beginning, i.e. Jan 1 of the first year to: Current value =1, Minimum =1 (I also tried 0), Maximum = 1500, Incremental =5, page =30, linked cell =$E$2. What is most puzzling to me is the fact that the scroll bar does work but only for part of the data set.

View 1 Replies View Related

Accessing Bank Balances Automatically With Excel?

May 31, 2014

Is it possible to have my Excel cash flow sheet access my bank checking account and automatically update with my current balance?

View 1 Replies View Related

Excel / SQL - Accessing DB Where Values Of Column In Range

Oct 5, 2013

First I have a excel with a selection of customers:
Column A = customer id
Column B = customer name
...
this excel contains about 500 premium customers

Secondly, on an external server I have a database with the details (adress, phonenumber, ...) of about 100.000 customers

Now I would like to display the details from the database into the existing excel. For that I was about to write a sql-query to copy the data I need to have into the excel (sheet2) to access it from sheet 1 with vlookup (based on the customer id).

But the large amount of data makes excel to work very slowly and it is impossible to keep on working like that. So now I was looking for a way to create a selective sql-statement.

Something like this:
select details from db
where customerid in (all the customer id's mentioned in sheet 1)
without writing all customer id's manually

View 2 Replies View Related

Accessing Message Editor Of Web Based Email

Jan 10, 2008

I am trying to fill out my yahoo email's 'Compose email' Form using VBA. I have a workbook with a list of email addresses and text messages to be send. I am primarily using getElementsByTagName(), and able to open a new yahoo page, log in, create a new email, populate the addressfields,and subject field.

However, I am not able to write anything in the message editor ( the part where we write the actual message). The following is a part of the HTML code related to the message editor. Please suggest what I should do?

View 9 Replies View Related

Accessing Data From Worksheets In Multiple Workbooks

Jun 27, 2008

I have read all the rules, and the guidelines for writting a thread title, and can only hope that my title meets those criteria (Dave, if they do not please do not ban me). I am fairly new to VBA, I have been able to edit other peoples code, and have created a few macros myself, and although I think I know how to use a loop and arrays, I sometimes lack some of the basics, this may be one such case. That said here is the problem I am facing.

I am creating a database, and as such I am sent several workbooks, with each workbook containning several worksheets. Within these workbooks, the naming convention of the worksheets is consistent, though each workbook may contain different number of worksheets. (the name of the worksheets represents a specific size/format).

What I have is a "Setup sheet" that I would like populated with all the data from specific worksheets in the various workbooks. So I would need to be able to get the macro to check all the workbooks, and if it finds a worksheet entitled "Sheet B" (for example) that it copies all that data into the "Setup sheet", and progresses onto the next workbook. I have the loop working and am fairly confident on this side of things, though where I am struggling is getting the Macro to know what worksheet to look at when going through the workbooks.

I need the macro to be fairly dynamic in which Worksheet it looks at so I don't want to simply hardcode the Worksheet name into the macro, rather I would like to be able to specify the worksheet name based on a cell value in the "Setup sheet". so going back to my earlier example if the cell value in the "Setup sheet" = "Sheet B" then the macro should get data from the worksheets entitled "SheetB" in the workbooks, if the cell value in the "Setup sheet" = "Sheet F" then the macro should get data from the worksheets entitled "Sheet F".

View 2 Replies View Related

Accessing Multiple Excel Grids With A Program Or Code

May 28, 2014

I am currently looking for a front end program to access multiple excel files at once. In a nutshell, I work for a company that uses subcontractors. Each subcontractor is graded in about 7 different performance categories. I receive daily excel files that give us the results for each category. I get separate files for each category.

What I do right now is use a master excel file. Every day I take each file I receive from our parent company and add it to the master file. I have a huge excel file that has a separate tab for each performance category. Then I have a "Report" tab where I use various formulas to summarize a sub's performance over a certain date range.

Essentially, the report tab is just a neat and concise way to display the stats of an individual sub, an office, a region or the whole company. I use tons of VLOOKUP and COUNTIF(S) formulas to look over the hundreds of lines of data and bring back the stats. It works but it is just not very efficient as I get hundreds of new lines of data every day.

View 4 Replies View Related

Reference A Number Of Cells From Accessing One Drop Down Menu

Feb 11, 2009

I am trying to figure out how to reference a number of cells from accessing one drop down menu. I have a list of products with pricing and logistics. I would like to have the drop down menu in another sheet that will access a specific product and the pricing that goes with it. I know how to do it if i wanted 100 seperate sheets, but I would really love an all-inclusive page that refreshes with the drop down menu. I have a copy of the file if that i hope will be helpful with colour coded cell references.

View 2 Replies View Related

Accessing Multiple Workbooks To Form A Master Database

Jan 22, 2009

I have 100 or so workbooks named A1234.xls, A1235.xls, A1236.xls etc.

I want to gather information from the same worksheet and same cell in each workbook.

I want this information in a separate workbook named Master.xls

In the Master spreadsheet I have the workbook names in column A e.g.
A1234
A1235
A1236 etc.

In column B, I want the information form each of the 100 workbooks. For example, from "sheet 1" Cell C2. This is the same place I want the information from in each workbook.

Is there an easy way of doing this with a formulae rather than a macro. For example, in the Master spreadsheet column B1 formula would read =[A1234.xls]Sheet1!$C$2 ... and then can you drag this changing the filename according to column A in the master spreadsheet?

If not can a macro be used? I have only started looking at macro's and my knowledge on them is very basic.

View 9 Replies View Related

Prevent Workbook Being Left Open Stopping Others Accessing

Feb 20, 2008

I have a spreadsheet on an intranet drive, and many people need to see it and enter information. Sometimes, we have the file opened on a computer, and the user walks away, and their station is locked. That prevents anyone from updating or using the file! locate a macro that will count how long the file is inactive, and then quit?
I'm not at all comfortable coding in VBA, and this sort of thing is out of my range!

View 4 Replies View Related

Excel 2010 :: Accessing VBA Module From UserForm On Workbook Open

Feb 13, 2013

I am using Excel 2010 and late binding to generate reports. The sheets are blank and unused. I have the UserForm being opened on Workbook_Open and the actual VBA for doing the work in a separate macro in the workbook. All the posts I have found are dealing with accessing cells from the UserForm and I haven't found any dealing with connecting a macro to the UserForm output.

My question is this: I want to return from the UserForm the flags of which reports to run to the (unopened) macro containing the code to do so. How do I open the macro and pass the variables to it?

The UserForm is creatively called "SelectionForm" and the macro is called "DailyFTP" with a Main sub as the entry. I know I can use the code under to capture the radio button and checkbox choices. I have other variables like the names of files set as global variables in the DailyFTP macro. The macro doesn't exist until SelectionForm opens it, so they must be set after the macro is in memory.

View 6 Replies View Related

Prevent User From Accessing The Excel File When It Is In Use By Another User

Jul 22, 2013

An Excel file named CLEARING is availble in thr central server system. it will be accessed by 5 or 6 staffs from some other systems what i need is when one person accesses/edits this file the others should not even able to open in the read only file ,when the file is saved and closed ,then only the next person should access it

View 1 Replies View Related

Sum Formula Across Sheets

Jun 19, 2014

I have several sets of financial statements, each in their own sheet. There are multiple years, by month in each sheet across row 4.

I've added a blank sheet at the beginning and end of this string of sheets and labeled them "Beg" and "End" (to assist the user).

I'm attempting to develop a summary financial statement that totals the current month across all sheets; however, this formula needs to find the current month and sum the correct cells across sheets.

The formula uses the Match function to determine the right row number of the account name (ex. Revenue) in column A and then uses another Match function to find the right month and year (all dates are formatted identically).

I've tried to use the ADDRESS function, but have been unsuccessful. I found a few posts across forums using the SUMPRODUCT function, but I wasn't able to find consistency between those workbooks and mine.

Here's the formula I'm using currently. I've also attached a copy of the workbook.

[Code] .....

Attached Files : Book1.xlsx‎

View 5 Replies View Related

IF Then Formula Between Sheets

Nov 11, 2008

Attached is an example with a spreadsheet. I am trying to get the info to flow from the data tab to the VPO tab.

So if the name in cell B1 of the VPO tab showed up in the Data tab in Column D then I would like the corresponding info to filter to the VPO tab to each cell

View 2 Replies View Related

Formula Between Two Sheets

May 20, 2009

how to create a formula to match names on one sheet to numbers on a separate sheet in the same workbook?

View 5 Replies View Related

VBA To Use Formula On Two Sheets

Jun 9, 2014

Code:
iHomopolymer = Application.Evaluate("=IF(COUNTIFS(B$2:B$6713,Q5,C$2:C$6713,
""="" & R5),VLOOKUP(R5,$C$2:$E$6713,3,1), ""No"")")

The original formula is above, with a description in the middle, and an attempt at a code below.

iHomopolymer is column AQ on the annovar sheet

B$2:B$6713, C$:C$6713, D$2:D$6713 are values in the worksheet panel

Q5 and R5 are values in the worksheet annovar

then in the vlookup R5 is a value in annovar and the lookup$C$2:$E$6713,3,1 are values from the panel worksheet.

For example, it is basically matching the value in Q5 in annovar to the B2:B6713 range in panel, once a match is found it then uses the value in R5 from annovar to search the C2:C6713 and D2:D6713 range in panel (this determines a location). Finally a vlookup is performed on the R5 value from annovar in the range specified in panel and the description or column 3 results.

Code:
Dim l As Long
l = Range("AQ" & Rows.Count).End(xlUp).Row
Range("AQ5").Formula = "= IF(COUNTIFS(B$2:B$6713,Q5,C$2:C$6713,""="" & R5),
VLOOKUP(R5,$C$2:$E$6713,3,1), ""No"")"
With Range("AQ5:AQ" & l)
.FillDown
End With

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved