can anybody tell me how the get the name of a sheet as a string?
i have a formula using the name of a sheet and i dont want to leave it hardcoded.
ex:
=IF(ISERROR(MATCH(_02.07.2009!B6;(OFFSET(_01.07.2009!$B$5;0;0;COUNTA(_01.07.2009!$B:$B)-1;1));0));1;0)
i need that dates as variables. those are the names of the sheets im using.
I have been set a task to do and I wonder if you could point me in the right direction.
Task - extract 2000 emails from a 6000 email database
The 2000 emails have to be proportionate to the original database.
e.g.
The main database has the emails plus town and employee size ranges
Column A - Emails Column B - Town Column C - Employees
So if Column B states that 50% of the entire database is from one town, then my extracted emails must also have half from that town (1000).
Also there are around 5 employee ranges and so they need to also be proportionate to those percentages too in the final extraction.
I want to pull data from another sheet, however there are multiple listings of each and I want to pull the data to another sheet for each line. I maybe easier if I try and show below:
Col. ACol. BCol. C
Smith101
Jones512
Green65
Black1214
Smith 3612
Jones1512
Dual25
Green1225
I want to pull this data for each name in Col. A in to another sheet. I've tried "IF", "Vlookup" and a couple of others, it just seems to be hitting the first option and pulling the data but not the ones below.
So if I selected Green on the second tab it brings through the "6" and "5", but not informtion from the listing from Green below. So I wiould like to list all the Green's, and the applicable data.
I have an excel file with a large list of data in the following format, all the data is in the A column –
Mar 11: category one – process number one (1)
Mar 11: category two – process number two (2)
Mar 11: category three – process number three (3)
Mar 11: category four – process number four (4)
Mar 11: category five – process number five (5)
I would like to extract from the list, ONLY the data highlighted in red i.e. the process number data –
Mar 11: category one – process number one (1)
Mar 11: category two – process number two (2)
Mar 11: category three – process number three (3)
Mar 11: category four – process number four (4)
Mar 11: category five – process number five (5)
In cell A1 I have the text
20. Black Angel Blues (Sweet Black Angel) - Nighthawk, Robert
In cell B2 I would like all of the text from the period/full stop after '20' until the - hyphen character
In cell C2 I would like the text after the hypen, but if as in this case there is a surname followed by a first name I would like them rearranged so that the full name is shown first name followed surname without a comma.
The end result in this example would be:
B2
Black Angel Blues (Sweet Black Angel)
C2
Robert Nighthawk
I have the following code which I know is wrong:
Public Sub WeeksOfSupplyatInitial()
'get the Instore Date Week add 2 weeks, then extract from Weeks of Supply
'and return the value to cell D4
Dim result As Double
Dim firstRowTotal As Double
I have a folder on my drive that holds excel files, some of which I would like to extract data from, using an excel workbook outside the folder.
Can you assist me in achieiving this action? Or, direct me to literature that will have the answer?
I have a table similar to this
L M N O P Q
title1 title2 title3 0
12 5 0
0 23 0
13 1 0
453 1 0
I need a formula to get the following structure (without using macro):
A B C
title1 title2 title3
12 5 0
0 23 0
13 1 0
453 1 0
Attached File : ReturnNonblanks.xlsx‎
My column extraction requirements are:
Using the same file you posted we just need column list; when we press ctrl+z the box should come up and have only columns list showing up and buttons like "select all", reset, and cancel should be present.
In this column list all the column headers should be shown under the column list and the interface box(ctrl+z) should be movable like you have already done in the last post
The other condition is that if any new columns gets added anywhere in the first row of the sheet the user interface(ctrl+z box) should show it in column list so when selected it can be extracted to the output file.
In short every thing is similar to the previous file samplecopy v6.1.xlsm you posted but this time only column list is required and all the column headers under it and remove the note which is applicable to previous query.
Extracting a list from a webpage. I am using the following VBA code below. I've attached the worksheet to which it corresponds. This is the webpage I'm trying to pull from: [URL]
My problem is the website divides up all of the records in sets of 50 on different pages but uses the same URL. Therefore, my VBA code will only bring back the first 50 records for each group I am trying to extract. Do you know how to pull all of the records for each URL?
[Code] .......
JBT Ranking Automation.xlsm‎
I just wanted to ask whether the same code will work for many more sheets or will modification be needed? I have a very basic understanding of vba loops, so plz tell me did you check the entire row for being blank or specific cells? Im attaching a file which more closely resembles the data i work with, just tell me whether the previous macro called "ravi" will work fine or will it require modification.
View 12 Replies View RelatedThere are websites available for data, but what is viable and crucial to me is finding a way to
1) Run a repeated process via a macro / vba script to allow for data input into the site
2) Once input into site, data is to be brought back into excel so that I can benchmark my product pricing vs my competition
There are 2 sites I am most interested in getting this information from specifically and although they do the same thing they have a couple of unique pros to them. they are the following
1) Findchips.com
2) eciaauthorized.com
The biggest pro to findchips.com is that they have a beta tool on their site that allows for "mass searches" to be performed as opposed to 1 part number at a time. Up to 100 results can be returned at a time, so although there would be multiple iterations doing it this way would save processing power as each step is not done line by line (p/n by p/n) as i could group them and send them and perform the operations that way.
From the website [URL] ....
I need the Name, Website and contact details. I tried to extract but there is no ID for the element.
Data from the above site is:
1 Name: Shopping da Midia
2. Website: It is a hyperlink of Shopping da Midia that is [URL] ......
3. Contact details:
Tel: 16 2101-2500
Av. Independencia , 192
CEP: 14010-210
Ribeireo Preto
E-mail: compras@cdvirgem.com.br
This table has one column with people's names, other columns with different things, and one with hours.
The figures will change every month, and my dad doesn't know crap about excel, so anything that he has to go put in equations or sort again every month or whatever, is not going to work. it needs to be so that all he has to do is plug in numbers.
It can be either one or two ways: either I need to pull out all the rows that have hours above a certain amount, and have them sitting to the side/under/above the main table in the same worksheet.
I realize that that may involve a long complicated formula, however.
Alternatively, we can have it so that all the rows with hours above a certain number are color coded at located at the top of the main table. I figured that one out with ease, HOWEVER I don't how to make that automatic, so that it does it by itself when the numbers are plugged in each month. My dad would not be able to handle sorting.
I need the hours that =0 color coded and put at the bottom if possible, it's not absolutely necessary, however. I could not figure out how to put the highest hours on top and the hours equaling 0 at the bottom, considering that all rowsthat fall in between need to be kept in alphabetical order.
I'm doing some simple data extraction, e.g.
A B
1 bob 3
2 mandy 4
3 charlie 6
4 dave 1
5 steve 5
So I had in c1 to c5 = =if(b1 > 3, a1,"") autofilled, which works fine, but I end up with,
gap
mandy
charlie
gap
steve
how would I get,
mandy
charlie
steve
also is it possible to have an if statement in 1 cell change the value of another cell?
e.g.
in a1
if(b1>5, c1="yes",c1="no"), can't seem to get it to work
I have 3 set of data representing 3 countries of data in 3 different spreadsheet.
I want to create a summary page and using a drop down list, when county is selected, corresponding data are extracted and fall into the template.
how can i do this ?
To start off macro should pick selected columns such as owners and their projects, start date, price Final price, priority and status columns and paste into new sheet
At the same time only pick selected owners(Dave, Brian, Ken, Russell, Ben)data along with their high low, and medium projects then format the price and the final price column (that is to first convert them to numbers, then change it to the currency format and set the decimal place to 0) and then sort owners (AtoZ) along with Price and final price columns (largest to smallest) into a new workbook and to a new sheet in the same workbook with a name to the sheet called Extracted data
note owners may change and new members might get included in the list ( I am not sure if something dynamically can be done about this in the macro)
I have included sample data and how the output should look like.
I'm trying to determine a way to populate a listbox with the contents of a few cells full of vblf-delimited information.
It's almost working properly, but placing the first entry in the target cells last in the listbox and have no idea why it's behaving this way.
Examples of content of two cells are:
50.20283625183448da
1195.368480208458hr
where the right alpha characters always count two.
I need the left number as a Double and the right two characters as String (or ?) to compare with Array("mn","da","yr") etc to determine the index I don't know how to write the code.
I recorder a macro while extracting a text file into a workbook. I had chosen delimited format and OtherChar as "|". Along with these i have also chosen the first field format as text
But where is the text format chossing portion in the below recorder code? i am able to figure out only the delimited and OtherChar part
Sub test()
Workbooks.OpenText Filename:="C:UserskkumarDesktop26 BOMs10086043.txt", _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=False, Space:=False, Other:=True, OtherChar:="|", FieldInfo _
:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1)), TrailingMinusNumbers:=True
Windows("Book1").Activate
End Sub
I have following data on my excel sheet.
man
man
man
man ....
john
john
john
john ......
10 man cells are there, and 20 john cells are there.
I need 10% of this data.
It means 1 man, 2 john.
I have a comment that I'm putting into a variable for the purpose of obtaining the persons name.
Cell C3 (in this example) just contains their employee number, but it's comment contains...
Tech ID: 123456
Name: John Doe
The Tech ID is variable from 3 chars to 6 chars and obviously the name is size is also variable. The end result will be a variable with just the persons first and last name extracted from the comment.
Code:
Dim mycmt As String
Dim techname As String
mycmt = Sheets("tech").Cells(3, "C").Comment.Text
techname = Right(mycmt, BIG-OLE-BALL-OF-CONFUSION)
MsgBox techname
My 2nd question. This is the 3rd time in recent days that I've needed a partial string and Mid, Left, Right, Len, Find just boggles my mind.
I am trying to use the string (sPath), I previously define to preform a saveas function after the script runs.
Code:
Sub CombineSheets()
Dim sPath As String
Dim sFname As String
Dim wBk As Workbook
Dim wSht As Variant
Application.EnableEvents = False
Application.ScreenUpdating = False
[Code] .......
These reports are always located in a subfolder defined as "D:DropboxWorkVerification Reportslocation name" where location name is the name that I want to use to save the workbook.
So as an example an excel workbook report generated in the folder:
"D:DropboxWorkVerification Reports!Test"
Would be saved as "!AirCheck AutoTest Report - !Test.xls"
or a report in the folder:
"D:DropboxWorkVerification ReportsHoliday Inn Surfside"
would be saved as: "!AirCheck AutoTest Report -Holiday Inn Surfside.xls"
Right now the code I guessed on isn't working and its generating an error on compile "Can't assign to read-only property".
I have a list of 3000 strings, all in seperate cells, in a column.
Within each string, are ( say 13 ) letters ( for convience) "A" through to "M" which are of interest to me
The 'letters' of interest occur either once or twice , or none within the individual strings. When a 'letter' of interest occurs within a string , putting a marker in a column on the same row is sufficient.
Using vba, I have extracted the cases where 2 'letters' exist within the strings, by using 3 nested FOR NEXT loops, with an IF test in the middle of
If (InStr(Cells(i, 2).Value, IndexLetter#1) > 0) and (InStr(Cells(i, 2).Value, IndexLetter#2) > 0)Then
and then put my marker in cells(i,3)
where : IndexLetter#1 is set in the outermost of the FOR NEXT loops as one of the 'letters' of interest, and similarly IndexLetter#2 is set on the next inner of the FOR NEXT loop . The inner most FOR NEXT loop ( i ) is the list of 3000 strings
I want to put a marker next to the string , when only ONE of the 'letters' is present in the string AND NONE OF THE OTHERS
I am finding that I am often using Max(if(... formulas to pull in Staff ID's that have seen activity in a large data dump. I then use a VLOOKUP to return name based on ID.
Trouble is, the names end up out of order. I want to be able to order them in one of two ways - either by work done, or alphabetically.
I have several large excel spreadsheets with thousands of entries (autocad drawing numbers). In one column it lists the date the drawing was created. Unfortunatley most of the entries were input as text. Even though I have formated the column to a date format (01/01/2009) most entries are not converting. Below is an example of some of the cells:
Jan. 12/02
JAN12/02
How can I correct his?
Creating a drop down menu that allows me to pull data from sheet2.
I have all of my data on sheet2 and my objective is to have a small portion of the data displayed on sheet1 once the user selects the specific "study" from a drop down menu.
how to create a drop down menu that will have a list of all the different studies, and then once the specific study is selected, how do I make it display the pertaining information in the master sheet.
Follow up....more information
The data I’m working with is a master list of different drug studies. All of the data is broken down by the specific study itself, so all I need to do is figure out a way to let the user access the data in a quick and easy way. I figured this could be down with a drop down menu that has a list of each study, and the user could select the study they want to review. Once selected, the data pertaining to the study would then populate the fields on the master silde(sheet1).
I have daily production sheets (Excel sheets) that I fill out every day indicating the day's production totals. The files are named by the day's date. For example today's sheet (4/24/13) would be 042413.xls. However, I would like to be able to pull certain pieces of information into another separate spreadsheet. For example, my boss asked me how many of a certain product we made over the last month. So, I'd like to pull the product info, the date made, and quantity made into another sheet. And I'd like to be able to do this automatically for a date range that I specify.
View 8 Replies View RelatedI created a macro for data segregation and extraction.
My currently flow is:create new worksheets rename the worksheets to e.g., sheet1 to product1, sheet2 to product2, etc manually filter the main data sheet, copy and paste the data into the respective sheets.
How to i edit the codes to allow the renaming of the worksheets to be non-static?
Meaning it doesn't have to be sheet1 to product1, sheet2 to product2
I can have sheet3 to product1, sheet4 to product2 or sheet7 to product1, sheet8 to product2
I feel that my method of data segregation and extraction is kind of rigid. is there any methods i can make it to be more fluid/dynamic?
I've attached a spreadsheet that I'm working on. I want to be able to extract data from a master sheet and copy data to another sheet if the date is within a defined date-range. I've prototyped the problem within the attachment.
Given the code in macro2, none of the records are selected (the "yep"). What am I doing wrong?