I am currently using this formula: =INDEX('[CTS - 2009.xls]2009 CT'!$D$14:$IV$1432,MATCH($M$4,'[CTS - 2009.xls]2009 CT'!$D$14:$D$1432,0),MATCH("prodhours",'[CTS - 2009.xls]2009 CT'!$D$2:$IV$2,0))
It currently only pulls data from one workbook (CTS - 2009.xls), but I would like it to pull from different workbooks based on the entry in $M$4.
If the entry in $M$4 is between 100.000 and 399.999, I would like it to pull from:
'[CTS - 2009 (100-399).xls]2009 CT'!$D$14:$D$1432
Between 400.000 and 699.999:
'[CTS - 2009 (400-699).xls]2009 CT'!$D$14:$D$1432
Between 700.000 and 999.999:
'[CTS - 2009 (700-999).xls]2009 CT'!$D$14:$D$1432
I 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?
How do I update changes to a linked workbook without having to open it? Let's say that I have two workbooks, "workbook1" and "workbook2". I want to copy data entered from workbook1 to workbook2 without opening workbook2. I know that I can just open workbook2 so that the formula will work but if I do that I'll end up opening up to 3 workbooks, which doesn't really give you much security.
I've seen a lot of code on how to pull data from workbooks in the same folder. However, I'm trying to pull the data in workbooks in various folders.
For examply, I have one folder, called "Master Files". In this folder is 12 folders, one for every month of the year. Within these folders, is one for each week, with the last day of the week being the name for the folder. In other words, it's like \Master FilesApril4-6-07data.xls
Now, I know how to get the data I need if all files are within the same folder. But how would I write a macro to run through all of those files when they're in separate folders?
What I want to do is pull data from columns A,B,C,D in 3 other closed workbooks (export.csv, export1.csv, & export2.csv) to my main workbook (Thunder.xlsm)and append that data in columns A-D on my main workbook. In addition I created a list of 10 names (but need to expand or delete from this list as personnel change) in Column O that I would like to filter the data in A-D. The names in the list must find the data I want to keep in column D, the rest I want to delete (not the entire row, just A-D for that particular row that does not match the criteria in column O, and shift those cells up).
I've attached a sample spreadsheet that will clarify what I am asking. I'm preferably asking for 2 macro's, one for the pull of data, and the other for the filtering.
I have been trying for months to get this to work by altering code from similar requests I've hunted down on google to no avail.
I am looking to have a "Master" sheet that is populated by data from 30+ individual excel workbooks. Each workbook is contained on a server in a directory "O:JobsJobs In Progress." In that directory are subfolders (named in sequence "C12000 Job1", "C12001 Job2", etc) which contain the actual workbook (Named according to number ie. "C12000 Jobinfo").
I would like this "Master" to automatically pull specific values from each C1200* workbook and paste them in a designated cell so that I can quickly look at specific information contained in each workbook. Each C1200* workbook is constructed the same way.
Short Example of C1200* workbook in which data will be pulled from:
Job Number C12000 PM Abe Lincoln Foreman Thomas Edison Contract Date 1/1/10
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 25 files with certain worksheets that I need to move to 25 other files.
Worksheet 1, 2, 3 and 4 in Workbook A needs to be moved to Workbook A-2014 Worksheet 1, 2, 3 and 4 in Workbook B needs to be moved to Workbook B-2014 Worksheet 1, 2, 3 and 4 in Workbook C needs to be moved to Workbook C-2014 etc....
Is there a way to do this with a macro? Preferably I would like to do this automaticly - i.e. runing the macro from a master file that
1. Opens Workbook A copies the worksheets 2. Open Workbook A-2014 paste the sheets 3. Save and close Workbook A-2014 4. Close workbook A without saving
Basically the main workbook opens 2 files at a time performs a calculate in the main workbook and then copies and pastes information in 3 ranges. Then closes the two open workbooks and loops and performs same operations until it hits the maximum loops. My macro is as follows and I have 3 question in capital letters.
I have spread sheet with 2 tabs (MASTER LIST & CYCLE COUNTS), on each tab I have 2 columns(LOCATION1 & DATE1 and LOCATION2 & DATE2) I need to verify if I any cell from LOCATION1 is repetead on LOCATION2 and copy the informacion from DATE2 to DATE1. then if I update the CYCLE COUNTS sheet I want the MASTER LIST update by it self.
This is the formula that Im using but Im having problems with the range..... =if('Master list'!$A2$:$A$15000='Cycle counts'!$A2:$A$15000, 'Cycle counts'$B&2:&B&15000,0)
formula that will pull out the number preceding the * 60 in the text below? In this particular case I would be looking to get 5 as the result. That number could be any number but in the example below it happens to be a 5.
Channel Tune Duration in Seconds} (ID) >= (5 * 60))
I want to be able to identify an Excel workbook in a cell, then have another cell pull the value from a specific cell in that workbook.
In the sample attached, I want the formula in cell C4 to pull the value in cell D35 from the workbook identified in cell C3. When I click within the brackets [C3] it asks me to choose a file name from my Explorer file list. "='[other_workbook.xlsx]Audit Information'!D35. I don't want to do that and want it to choose the file listed in cell C3.
I'm trying to pull some statistics from a workbook I've been sent and am having some trouble working out how to achieve this.
I have a small interface that allows the user to choose a possible answer (drop-down menu) from a questionnaire (Yes, No, Maybe)(cell: C6). On the interface the user also picks the question they wish to see the stats for (cell: C5)
The data I have been sent has been set up with the questions along in row A, and the answers below in each column (the answers run across row A from column G - AH).
So, what I'm basically after is a formula that first looks up the question specified in C5 (I've used HLOOKUP to pull some other data), and then counts how many times the Yes, No or Maybe answers appears in the column where the question data is held.
I have a spreadsheet that has item names and then at the end the manufacturer number. I'd want to find way cut that manufacturer number off the end and move it to its own column.
I pasted some examples below of what it looks like. It is just coincidence that the items below all that the same length...they are all of varying lengths. The one thing they all have in common is that it the last thing in the cell and seperated by a space. I would think there would be a way to find the last space in the cell and cut everything to the right of that. I can do it in a macro if necessary, but where to start.
AVENGERS FIGURE ASST 3 75 A1816 AVENGERS HULK HANDS A1827 AVENGERS SLAMMERS A1822 BATMAN BATMOBILE Y1258 BATMAN DK KNIGHT FIGURE ASST Y7572 BATMAN FIG N VEHICLE ASST Y1252
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 excel to pull the value from a certain cell. The value is pulling correctly when I use whole numbers. But if I format that value as either a decimal or percentage it does not recognize it and only pulls over 0.
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 have 36 cells that will have multiple letter values. The cells in the range (MyRange) in question, have formulas in them. I will be transferring this into a cell range that has over 6000 cells, but I'll worry about that when I get this part down. :D I have 07 excel but this is in the older versions.
I need a formula that will take info from sheet A column K only if sheet A column E is equal to sheet B column E. I need it to paste into Sheet B column K exactly as shown in sheet A column K.
I tried using a Vlookup formula but I got #N/A and I am not sure if that is because there is text as opposed to numbers or if I am using the wrong function entirely.
I am trying to use Harlan Groves PULL UDF. I have inserted a module in my work book and added the code verbatim as posted on his site. My indirect
=INDIRECT("'[FFPSTotal "& YEAR &".xls]" &$R9&"'!B$13") This works of course only when FFPS is open. How do I modify the formula to incorporate PULL. I have tried PULL("'[FFPSTotal "& YEAR &".xls]" &$R9&"'!B$13"), and get #REF!
I have two worksheets. The first is a master listing of all stores for a certain client. They are all assigned a store number. The second sheet is a list of all invoices, I need to be able to type in the store number and it pull up the street address, city, state and zip, all in different columns.
I am blanking out. I want to pull the data out until a space (one formula from the left and one from the right). Ithink it's a mid function but not sure.
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.
