Is there a way to pull out all of the data from a row and place it into a cell out to the right of the range?
Some of the data is text and some of it is dates.
My problem is that it is a big spread sheet and the data is scattered in the sheet. Not uniform at all.
I have a column of numbers each have a 0. infront of them (example 0.2346
0.5698). I want to pull the number from the right until it hits the decimal sign. So for the two above the result would be 2346 and 5698
I have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...
First I was using a msgbox function to get the info:
HTML Code:
For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b
But it can be up to 30 different new employees... and that is time consuming.
I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....
HTML Code:
Private Sub CloseButton_Click()
Unload UserForm1
End Sub
Private Sub ComboBox1_Change()
[Code] ......
Looking for a formula to accomplish the following:
I'm trying to populate cell A31 on a worksheet titled "VolumeTotals" with the data in Cell E23 from a worksheet titled "CurrentCustomers" if the merged cells F3-F22 on worksheet "CurrentCustomers" are equal to the word "Contract".
I have an excel workbook with 2 worksheets. One worksheet shows the MASTER LIST of COMPLETE Customer Names (e.g. ABB Supplies Incorporated). The other worksheet has information on customers but the customer names typed in are incomplete (e.g. ABC Supplies). I need a macro that would look do a comparison of the customer names in the 2nd worksheet to the Master List worksheet and pull the data (complete name, address, etc.) for those that would match (partial match since company name is 2nd worksheet is usually incomplete).
View 2 Replies View RelatedI have one excel 2010 workbook with 5 work sheets, each work sheet contains a list with first/last name(one column) and the company name, some have a 3rd column with their email address in each sheet represents each year starting at 2008 thru to 2013 i have to find out if the people that attended an event in 2008 also attended it in 2009/10/11/12/13 and if they didnt, put their name and company name onto a blank worksheet within the same workbook without using a macro, how can i do this?
View 2 Replies View RelatedI 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.
Is there a way to use Data Validation that will pull data from a source list and also be able to type in additional data or just new data in same cell?
View 3 Replies View RelatedI have a Pivot Table, with lots of data in, what I want to be able to do is on a separate sheet pull out the data from the first column(A), and then average out the figures from Columns(B-E).
When this has been done on the sheet (with the average data) in the next column, I then want to use Column(F) (from Pivot Table), and do a simple sum to work out 5% of the difference if it is greater than ZERO between Average Column and Column(F) (from pivot Table) Column(F) must be higher than the average in order to work out the 5%.
I've been playing around with VLOOPUP, MATCH, SUMPRODUCT... But I can't seem to get this one right..
See the attached Excel file..
I think it should be fairly explanatory.. I want to use the dates in SUMMARY sheet, to pull out the data in the DATA TABLE sheet. The numbers in Column A on the DATA TABLE sheet, is first date in each month, and the numbers represent the day of the month.. .
Example:
01.01.2009
1
2
3
4
5
..
30
01.02.2009
I don't know if this will require a macro or maybe a function of excel, but I have a large listing about 1000 row in spreadsheet. Each row correspond to a unique customer. But I will have to extract only the customer that I am looking for, which is about 30 of the 1000. The cell A1 is the customer # which will be use as reference, so I have about 30 customer # to pull out from that listing. Which will be the greatest method to do so?
Example:
A1 44334 ERIK JOHNSON 60$
A2 34555 KARIM EDWARD 50$
A3 32555 EDWARD CHRIS 65$
A4 55666 STEPH LAWRAW 45$
...
I want to pull 44334 and 55666 the whole row.
I am trying to get the starting point for this new sheet going but I am not able to formulate the required code. I will explain what I want to do
In the attached sheet, I have three tabs
Over 20K
Under 20K
PartNumList
This is what I want to do...In PartNumList tab if Annual Rev (Col O) is less than 20K then value in Col (A) i.e Part Number should be copied and pasted in Under20K tab... it has to pasted in either B18, B24,B30,B36, if B18 is populated then B24 if that too is populated then B36 and so on..
I want to do that same for Over 20K but if I have a starting point for Under 20K I can work on it offline too..
I need to pull some data from one sheet into another. Here is an example of what I am trying to do if anybody know hows to do this? I need a formula that will look in sheet 1 look at the idnumber, match it with the idnumber in sheet 2 ande fill in the serial.
sheet1
idnumber serial number
12345 66181
12346 66182
12347 66183
sheet2
idnumber serial number
12345 insert serial number here
12346
12347
I want to create a macro that will automatically pull data from the web by simply pressing a macro button. I assume that this would be an easy task if the data is from a website with a specific URL such as [URL]
However, there are times when someone must enter data into the web to pull a specific report that does not have a unique URL. Is there anyway to create data fields in excel that would allow an individual to type in specific information (name, report period, etc.), and then press a macro button that would go out to the web, enter the information automatically, and export it as an excel file. I realize that this is probably substatially more difficult than my first question.
How to pull data from one sheet to another. I have 2 sheets Sheet 1 and Sheet 2. I have data in cell A1 in sheet 1 and i need that same information onto sheet 2 in cell C10. I know on sheet 2 cell C10 i can type in this formula ='Sheet1 '!A1 and it works great.
The problem i'm having is if i clear sheet 1 data it is also clearing out the data in sheet 2, the formula remains but the data is gone. I need that data to remain on sheet 2. Is their a way to do this?
Basically what I want to do is pull data from another file and have it copy it down about 10 lines onto another file. With a seperate sheet for each vehicle. For example:
File 1: Has 2 columns with data
Vehicle Number 1000 Vehicle Type Ford
1001 Chevy
1002 Dodge
File 2: Has a vehicle log sheet, in which i want it to take the data above and copy it down x amount of lines.
Vehicle sheet | Date issued | Hours used
Vehicle Number| (data from file one here) | Vehicle Type (data from other column in file one here)
And have it copy itself down from like A1 to whatever.
These also have to be on seperate sheets within the workspace, and each "tab" be named after the vehicle number,
I am trying to get a excel to list all the file names in a drive along with date last modified, author and file size. I have got the following bit of code to pull back the file names, but i am struggling to find anything that will pull back the other data, I think its something to do with the DIR function, but im not sure........
Range("B1").Select
D = ActiveCell
Cells(2, 1) = "Filenames"
r = 3
f = Dir(D, 7)
Do While f ""
Cells(r, 1) = f
r = r + 1
f = Dir
Loop
End Sub
where "B1" is the file path of the drive
I have a workbook I created that is full of macros, graphs, and formulas. Five of the tabs in the workbook are where I will be putting my data. This workbook will also be used by others that don't even know what a macro is or does so I need to make this as simple as possible. How I get my data:
I have a website on my intranet where I have to login and then I have five templates that I run. I can have the results given to me either in HTML or in an excel spreadsheet. The excel results are very basic. Heading for each column and the data below. I have the report generated with blank columns since my page the data will go on has formulas. Thus far I have been copying all rows starting with row2 and doing a paste special (values, skip blanks). I had a macro in my personal.xls file to do this for me but most users will not know how to import it so I need a resolution that will be saved in the WB I send out.
I got it when I click on the cell it loads the userform but it is populating with row data instead of column data.
I would like when I click on for example cell T4 and the userform pops up to see the textbox data going down the rows instead of across which is is currently doing.
so clicking on T4 brings my userform up filled in with information from
T5 and U5
T6 U6
T7 U7
T8 U8
T9 U9
I have when I click on the cell T4 column
label amount receipt
electricity 384.00 ZgHl2V
This way I can change amount or receipt and update my worksheet.
VB:
Private Sub UserForm_Activate()
Dim r As Long
r = ActiveCell.Row
[Code].....
In the annual table tab, I want to be able to select a sales person or other field, and create a table that will pull data from all the tabs. For example, if I select the salesperson Kelly, I want it to pull all of the data from January, February, and March, and compile it into a table.
I would want the table to be dynamic also - I want to be able to quickly sum/average all of that salesperson's sales for all months.
I have information that I want to pull using an If, Then statement. The goal is to populate the information in column J with the information in column A by matching the information in columns B and C.
Is this possible with an If, Then function?
I'm working on a sheet trying to keep track on which client is working with which employee. The first sheet shows tracking for the year and each additional sheet is the month. Basically the Yearly sheet is there so I can quickly control+f "client name" and see which employee he or she is working with.
I attached a simple version of the workbook but basically it basically looks like this: Yearly sheet - Row 1 is the employees name, Row 2 is the month, and the rows after that are the clients name until it gets to the next month. January sheet - cell A1 says client, Cell B1 says employee. I'm inputing the clients in column A and the employee in column B and using this formula for the Yearly sheet (starting in row 3) =IF(January!$B2=Yearly!A$1,January!$A2,"")
This is working for what it's supposed to do, but it's not pretty and leaves a lot of of blank cells. Is there a simpler way of doing this?
YearlyClientsEmployees.xlsx
I have several excel spreadsheets by which I would like to add together certain values in the column with the same columns of all the other excel spreadsheets. So basically, If one of the values are "SELF" , I would like to calculate how many times that value occurs in all the spreadsheets. Any easier way to organize the data to be able to do this also.
View 1 Replies View RelatedThe spreadsheet will be have 3 worksheets. The first worksheet will be the mastersheet (Form-01 in the attachment) that will be used as a summary/receipt for the person participating in the auction. The second worksheet will be a list of the people participating in the auction (Names) will all applicable contact info. The third worksheet will be the list of all the items for the auction (Auction Items) with all relevant information per item and a unique number for each item.
The idea is to be able to track information during the auction on the "Auction Items" worksheet, there is a column for the bidder ID of the winning bidder. Then after the auction is over and its time to settle up be able to go to the master sheet (Form-01) and enter the Bidder ID# and have the other fields auto-populate.
I am trying to pull data from one worksheet to another. I am using Product ID numbers. The problem I am having is that not every Product ID I am searching has a partner on the second list, so I get an #N/A. In stead of #N/A I just want a "0".
my vlookup looks like this: =IF(VLOOKUP(A1,Sheet2!A5:C500,3)>0,VLOOKUP(A1,Sheet2!A5:C500,3),"0"). If A1 does not find a match on Sheet2, it returns "#N/A" when I want it to return "0".
I have a drop down that provides all of the sheet name in the workbook. When the user selects a sheet name, I would like specific cells to pull data from the sheet name selected. What I thought would work is =Sheetname(A1)!E3 where the cell would pull data from cell E3 from the drop down sheetname in A1
View 7 Replies View RelatedI have a workbook, in which I will have approximately 5 worksheets. The 1st worksheet will be a summary table, which will pull data from the other 4 worksheets. These 4 sheets have data of 4 companies, thus representing 4 companies. In the 1st worksheet, I have a drop-down list that has selections for each company.
How do I make it so that when I change the selection from A company to B company, the data in the summary table will automatically pull data from corresponding worksheet? For example, if 2nd worksheet represents Apple Inc, the 3rd represents Microsoft, how do I reference from 2nd to 3rd worksheet automatically when I change my selection from Apple Inc to Microsoft in the 1st summary table sheet?
I have a file in which records are saved in chronological order. What i want is when i click on a button it should prompt me for a date and when i enter the date it should retrieve the data from the database sorted in chronological order from the date i have entered.
View 10 Replies View RelatedI have a whole group of workbooks. Is there a way to make a new workbook that will look in the folder containing all of the other customer's books and pull information from them? In other words, I want a workbook where in Column A would contain all of the Customer's Names (Cell Info!G6), Column B would contain the Insurance Company (Cell Info!M14), etc. Is there a way to to that?
View 14 Replies View RelatedI'm looking for something that will be able to push or pull data from a workbook (named "Personen - test.xls" = persons) to an other workbook (named "Personen per maand.xls" = persons/month). In attached documents i tried to make clear (sorry, it's in dutch) what i would like to do. In English; the marked data in "Personen - test.xls" should automatically be shown in the marked area in "Personen per maand.xls". I think the biggest problem is the fact that "Personen per maand.xls" is a standard-document which should be filled -time after time- with many different data's from several "Personen - test.xls" documents. These documents are all named different, the data is always given in the same way. So in my opinion i should make something where i can tell excell which document to use and something i can tell where to put the data. But i really don't know how to do this...
View 11 Replies View Related