Search For A File And Bring Back Data To Master Spreadsheet
May 22, 2008
To set up the problem, I have a folder that contains files that are all named numerically, ex. 08-100, 08-101, etc. Each file is identical in format but contains different data, ex. cell B1 is alway "material weight", cell B2 is always "estimated man hours" and new files are added weekly.
I am trying to set up a master spreadsheet that all I have to do is enter the file name (08-102) in the first column, and the second column will return the data in a specific cell of that file.
View 14 Replies
ADVERTISEMENT
Jul 13, 2007
I have a dataset which is dumped into my model every month. The Report tab is a "user form" whereby the user can select multiple search criteria. The dataset is then filtered according to the search criteria entered by the user.
Problem is, that if the user only selects 1 of 3 search criteria and the other 2 cells are left blank, the filter filters on these blank cells and doesn't retrieve any data. Any ideas how I can overcome this?
Sub PopulateReport()
Application.ScreenUpdating = False
Dim MyFilter1 As String
Dim MyFilter2 As String
Dim MyFilter3 As String
MyFilter1 = CStr(Sheets("Report").Range("C2").Value) ' convert cell value to string
MyFilter2 = CStr(Sheets("Report").Range("C4").Value)
MyFilter3 = CStr(Sheets("Report").Range("C6").Value)
Sheets("Waste").Select
Dim Rw As Long
Dim Rng As Range
Rw = Range("A65536").End(xlUp).Row
Set Rng = Range("A1:W" & Rw)
With Rng
.AutoFilter
.AutoFilter Field:=20, Criteria1:=MyFilter1
.AutoFilter Field:=2, Criteria1:=MyFilter2
.AutoFilter Field:=13, Criteria1:=MyFilter3
End With
how to display my excel worksheet using HTML maker as I've downloaded the software but have no idea how to use it!
View 9 Replies
View Related
Apr 25, 2008
i have the following macro which finds instances of a search criteria and brings them back to a results column. what i need is instead of having the word GD: appear before each result i need it once at the begining of the results
LastRow = Sheets("design owb").Cells(Rows.Count, 10).End(xlUp).Row
Application.ScreenUpdating = False
For i = 26 To LastRow
Target = Sheets("design owb").Cells(i, 10)
With Sheets("design")
For J = 2 To 54
For K = 4 To 11
If Target = .Cells(J, K) Then
Sheets("design owb").Cells(i, 11) = Sheets("design owb").Cells(i, 11) + "GD: " & .Cells(J, 15) + " | "
End If
Next K
Next J
End With
Next i
Application.ScreenUpdating = True
View 9 Replies
View Related
Jun 28, 2009
I have this spreadsheet that has over 20,000 rows. I was asked to build a search page to will bring back all transactions based on a primary key (account number). Here is a sample:
Account NumberDateComments2343566/2/2009 $ 111.43 3453465/1/2009 $ 89.34 5676552/5/2008 $ 643.23 8078989/3/2008 $1,245.34 12543612/5/2008 $ 56.65 2343562/2/2009 $ 343.54 3482459/9/2008 $ 78.76 9345641/2/2009 $ 356.22 2343565/3/2008 $ 529.66
The idea is to enter an account number like 234356 click a button and bring back:
Account NumberDateComments2343566/2/2009 $ 111.43 2343562/2/2009 $ 343.54 2343565/3/2008 $ 529.66
I got the button part done and using vlookup it brings back the first line. The problem is that it won't bring back all the rows just the first one.
View 9 Replies
View Related
Jan 22, 2014
I'm creating a master spreadsheet and I want it to pull live data from other spreadsheets depending on a variable.
I've a cell that has a variable date. I've files saved that have this variable attached to it in the name format: 'filename variable'.
I want to pull data, hopefully live, from workbooks based on this variable.
For example, I have files: generic 01212014, generic 01222014, generic 01232014.
The date is stored in the master workbook say in A1 that I can manipulate by entering a different date to pull data from a different workbook.
='[generic 01212014.xlsm]Database'!$L$3
='[generic 01222014.xlsm]Database'!$L$3
='[generic 01232014.xlsm]Database'!$L$3
There has to be away to make the bolded portions of the file name variable, right?
View 2 Replies
View Related
Nov 12, 2009
create an array of active printers. I edited the code to write the array to a range, then populate a data validation list with the printer names. All works great. The goal was to select a printer name from the list and run a macro to print some sheets. However, the function doesn't bring back enough of the name to do this. how to edit the function to bring back the whole name.
For example: after running the function the 1st printer returned ="\rizzoHQ-ENG-04"...when i record a macro to print to this printer it needs ""\rizzoHQ-ENG-04 on Ne05:". Here is the function unedited, I bolded the part i think is controlling the name:
View 5 Replies
View Related
Jun 5, 2007
Name Qtr Year
Org 112007
Org 222006
Org 332006
Org 442006
Org 542007
Org 612006
I want the code to say, If Qtr = 1 And Year = 2007, then bring back Org Name, If Qtr = 2 And Year = 2006, then bring back Org Name and so forth.
The code should loop through a range of constant data (Region Name), until the Region Name is blank.
Dim OrgCell As Range
Dim OrgTargetCell As Range
Dim RegionCell As Range
Set OrgCell = Sheets("DATA Removed").Range("A2")
Set OrgTargetCell = Sheets("Overdue").Range("A3")
Set RegionCell = Sheets("DATA Removed").Range("C2")
i = 0
OrgCell.FormulaR1C1 = _
"=IF(AND('DATA Removed'!R[-1]C[11]=1,'DATA Removed'!R[-1]C[12]=2007),'DATA Removed'!R[-1]C,0)"
Do
OrgTargetCell.Offset(i, 0).Value = OrgCell.Offset(i, 0).Value
i = i + 1
Loop Until RegionCell.Offset(i, 0).Value = ""
End Sub
View 9 Replies
View Related
Jan 19, 2013
I am running an Index, Match off of totals, what I would like to do is when the formula finds the column_index_num to move up one row and bring that value back.
For example: if I am going to sheet2 and my formula would normally pull back the information on row 7 with a column_index_num of 5 (or column E), I want to bring back the information on row 6 column E.
How do I add the variable to bring back the information one cell above?
View 9 Replies
View Related
Mar 3, 2009
I want to lookup some data and bring back the info in the last populated cell on the row, rather than column 4. I have written the below but it is bring back info from Column Index number 4.
=VLOOKUP(D4,'A2'!A:Z,4, FALSE)
View 9 Replies
View Related
Jun 25, 2014
Median Indirect: I would like to find the median in a range of cells and then bring back the 2 adjacent cells
Range...............Adj Cell 1............Adj Cell 2
1.......................L1......................L2
2.......................P3......................P4
3.......................O9......................010
So the median of the range is 2, thus we would want the formulas to bring back P3 and p4
Column 1..............Column 2
P3...........................P4
View 12 Replies
View Related
May 16, 2007
Here is a project I can’t seem to do on my own if you can point me in the right direction I would be grateful! here is the best suedo code to describe the needs of the script:
In the open workbook named MASTER, on Worksheet named -MASTER- ( Let’s refer to this all as just MASTER)
(In production, the name of this workbook will be an account #_ date, and the worksheet will be a date)
For each numeric value in row 6 (we’ll call these values AD###) of MASTER
'*****Part one of routine*****
Search directory "H:AccountingAdvertising Accounts" for workbook named AD### AX.xls
(Note the AX suffix of the file name)
If match is found open workbook and proceed to Part two
If match is not found, goto Private Sub AD_MISSING
'*****Part two of routine*****
View 10 Replies
View Related
Sep 4, 2013
I need a formula to check a range of parts and bring back a value of 0 if matched or the value in another cell if it doesn't match.
Part number H-200-3
Part number H-200-3-A
Part number H-200-3-B
If I enter any of the above part numbers then cell B1 returns 0 if it is a different part number then B1 would need to match cell A1.
View 2 Replies
View Related
Jun 4, 2014
=MIN(IF(B3:B32="",IF(A3:A32>0,A3:A32))) ArrayedOldest Date Array.xlsx
Following on from a previous post need to add to my (forums) array.
I need to find the location of a date which has been looked up in a cell based on the above formula which is in the attached.
View 3 Replies
View Related
Jul 27, 2007
I have 2 spreadsheets. One is a promotion calendar that lists the dates that promotions on a certain product runs. The other is a shipment grid of shipments of that product to the customer.
I want VLookup to find the customer and the dates and then bring me back an asterisk in a separate column to show me that that certain week that product was delivered was a promotion week. The problem I have is using Vlookup to lookup 2 things at once (and if they match to the promotion calendar) and return me an asterisk.
Here is my formula now:
=IF(VLOOKUP(J2&" "&L2,'East Data'!M:AU,2,FALSE),"*",0)
J2 is the customer name
L2 is the week
"East Data" is the spreadsheet with all of the promotions and customers.
View 9 Replies
View Related
Feb 21, 2013
We created a database for our employees who have security items and other things we need to keep track. We created a spreadsheet where data was entered on Sheet2 (Entry Form) and then by clicking various buttons would be thrown over to Sheet1 (MasterList) and put in alphabetical order. Then last week he said lets change it to a User form where i can put all the data, be able to tab through my text boxes and make the whole process of entering information faster and more efficient.. but now hes gone and I'm left with his macros and all this stuff i don't really understand! I have been doing pretty well on my own but im stuck "Search Employee" Here is what i have...
VB:
Private Sub SearchEmployeeButton_Click()
LastName.Value = Range("A500").Value
FirstName.Value = Range("B500").Value
[Code]....
use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window.
View 2 Replies
View Related
Jan 3, 2013
I have a "txtQuoteNo" box used in my userform "FrmQuotation", and this is recorded in column A of my Central Data spreadsheet (with a fair slug of other data from the userform).
I can currently recall the data into the userform from the spreadsheet using the QuoteNo, but when I need to resubmit it back to the spreadsheet, I can't see why the following code posts this new data onto a new line rather than the blank cells in the corresponding row of txtQuoteNo and column A (if you see what I mean...)
Set wb = Workbooks.Open("M:Central Taxi Data.xlsx")
With Sheets("Data").Range("A:A")
Set cell = .Find(myVal, LookIn:=xlValues)
rw = cell.Row
End With
RowCount = Worksheets("Data").Range("A" & rw).CurrentRegion.Rows.Count
[Code] .......
View 3 Replies
View Related
Jan 15, 2009
i just want a macro to run over 4 sheets and combine onto one. I found this code -
Sub masterer()
if sheets(1).name "Master" Then
Sheets.Add before:=Sheets(1)
ActiveSheet.Name = "Master"
end if
For i = 2 To Worksheets.Count
Sheets(i).Columns("A:G").Copy
Cells(1, (i - 2) * 7 + 1).Select
ActiveSheet.Paste
Next
End Sub
This places all data in a line in row 1. Can the code be easily changed so
it puts each line from each worksheet onto a new row. So i have one big list of data ranging from columns A:G
View 9 Replies
View Related
Dec 15, 2009
I have csv files auto generated (with date stamp in name) and saved in a specific folder everyday. I need to find a macro that will copy the row(s) every day and add to the next empty row in the master excel file. Some days the csv file may have a single row of data and on some other days it may have multiple rows of data to copy and paste.
View 4 Replies
View Related
Jul 12, 2013
Every week I receive a sheet of data that I need to import into my master spreadsheet. The problem is I have to adjust the data I receive to import it unless there's a better way of doing it. I'm trying to automate both the modification and the importing of this sheet. I've tried to create a macro to modify and import without any success.
To adjust the spreadsheet I receive I need to: delete or find a way to exclude the first 3 rows, the bottom row with data stating "complete" and columns C,D&E from being imported; change font, size, color, bold to match the main spreadsheet in addition to formatting; split data in column A and remove any special characters. Formatting is important because I have to be able to easily remove duplicates. Also, the number of rows in the sheet I receive changes weekly.
I've attached a workbook with sample data. The first sheet is representative of what my master looks like, the second is what I sent and the third what I've been modifying it to and then importing. As stated before, I'd like to have my master automatically update with the sheet I sent, but I'm at a loss having to reformat everything every week before importing. Having the sheet that's sent to me revised before I receive isn't an option. What's the best way to automate this?
SAMPLE1.xlsx
View 1 Replies
View Related
Mar 20, 2009
I have directory with multiple workbooks. Each workbook contains multiple sheets (sheet 1, sheet 2, etc). On each sheet of the workbook, there are various data. The data I am looking to extract from these sheets are in the same cell of each sheet. (See data sheet –datasheet.xls). If I can extract these data without opening the file, it would be preferred. Otherwise, opening the workbook, extract the data, and then close the workbook is acceptable.
Desired Solution:
I would like the assistance to create a macro that will extract data from each sheet of the workbook in the directory at a time until all the workbooks and sheets within the directory are read. The macro shall extract data from cell B5, B6, B10:B20, and sheet name from each sheet of the workbook and copy these data to an active sheet called “US” on workbook, DesireResult.xls. The DesireResult.xls shows how the data should be copied over. Before copying the data to the DesireResult.xls workbook, the macro will prompt the user to enter a cell on the “US” sheet as the starting point to paste the information to. Prompting the user to enter the beginning cell shall happen only once and not for each workbook or sheet being read. The reason for this is because the “US” sheet will already have other data in it. After reading each sheet, the macro shall leave a blank row at the end. The data from the next sheet shall be pasted below the blank row. Note: all the workbooks contain macro and link to other file. When opening each workbook, the macro should automatically choose not to update the links.
View 4 Replies
View Related
Jul 23, 2008
I've created the code below from scratch (ever so proud of myself even though it's basic lol)
Dim N As Long
Dim LR As Long
N = Range("A2").Value
LR = Application.WorksheetFunction.CountIf(Worksheets("Overview").Range("AJ:AJ"), N)
For N = 1 To LR
Here, 'N' is a project number. This code succesfuly counts the number of sub-tasks linked that that project in my data sheet. Now I want to bring all those rows into my template spreadsheet, but dont know how to start
View 9 Replies
View Related
Dec 19, 2013
I have tried to write the below VBA to copy a specific cell to a specific workbook. I have set the folder path in B1 and listed the file names in column E4 onwards. E1 being the number of files in column E. I get a run-time error 91 "Object variable or With block variable not set" on Current File = ActiveWorkbook.name.
Sub UpdateParameters()
Dim CurrentFile As Workbook
Dim wbOpen As Workbook
[Code]....
View 4 Replies
View Related
Dec 30, 2013
I am a lacrosse coach and am keeping track of key stats that our team values that cannot be found in the box score (ex: 'Secondary Assists'). To do this we have created a group of rather simple Excel spreadsheets to keep track of these. We have one master file will all the total data from the season and 10 or so secondary files for each game that we played. To this point, I have been adding the data from the different game files to the master file manually and it is very time consuming.
My question, is it possible to copy a row of data from one of the secondary files and have excel add that data to the existing data in the master file?
View 2 Replies
View Related
Apr 14, 2007
It also renames the CommanBarPopop with the new filename.This allows the user to open both Projectworkbooks/files (If required) and load each CommandBarPopup for different filenames .Therefor opening the Userforms and worksheets for the CommandBarPopup clicked ...
View 9 Replies
View Related
Apr 22, 2013
How to get updated data from connected 3 XL workbooks.(files)
1= ODBC connection ( This brings data from a Basis Query) it will updated 1 or max 2 times a day.
2= Manuel input ( It will update after inserting new data, many times a day) Password protected
3= This One is my master file which brings data in 2 different sheets from above mentioned files. Password protected
My problem is started when i want to see latest updates from file 1 & 2 in my Master file then i have to re-open my Master file, Is it any option which fulfill my requirement that i will keep my main file update just by clicking <save> & I will not re-open file at all times ?
I can't have a shared file. & my all these 3 files are in the same folder.
simple Formula used to bring results are =[Book1]Sheet1!$A1
View 2 Replies
View Related
Jan 26, 2010
I am usuing INDEX and MATCH to pull back employees hours from a master sheet, I am asking excel to Index, matiching firstly the agents name and then the day of the week (Formatted: Mon,Tue,Wed,Thurs,Fri,Sat,Sun). This works perfectly for every day of the week using the following formuls except for Sun where it returns #REF!
=(INDEX(Hours!$B$4:$G$166,MATCH($F$1,Hours!$A$4:$A$166,0),MATCH(C7,Hours!$B$3:$H$3,0)))
View 2 Replies
View Related
Nov 15, 2009
My project requires working with 3 different files, in my example here: Delivery Status, Warehouse, and Clients. Delivery Status is the master file where the information is gathered at first. Then when the product arrives, from the column "AI" I choose the Destination which it could be a warehouse or client. So far the code that I have can paste the information in different sheets within the File Warehouse, but can not make the difference between the File warehouse and the file Clients.
In the Delivery Status file I can choose from the Column AI whether the destination is a warehouse or a client and I need Excel to paste the information in the proper file. Have in mind that I have a lot of sheets in both files. I am attaching an example files with the code.
View 2 Replies
View Related
Nov 28, 2013
I need to segregate a data from a master file with a particular column as a main.
for eg:- i have customer details with name in a column
address in b
mobile in c column,stat and other details in other column.
I want to segregate data according to state in different excel so how can i do the same.
View 3 Replies
View Related
May 6, 2014
I've attached 2 test files, one is the database master file containing the projects (each row represents a project, unique reference number in column A) and the other is the blank template file i'm hoping to export data into and then save down with the naming convention "column A_column B.xlsx"
Kept the test files simple but would need to modify any code to apply to much larger database consisting of many more fields etc.
Master.xlsx‎
Template.xlsx‎
View 9 Replies
View Related
Jan 14, 2014
I'm trying to collect specific data from a collection of different (.xls) files into 1 master file using the following code (which runs in the master file):
Code:
Sub FolderPick()
Set fso = CreateObject("Scripting.FileSystemObject")
With Application.FileDialog(msoFileDialogFolderPicker)
[Code].....
I'm having trouble with the Name-variable in the 'red line'. This variable is set in the 'purple lines' however, the code (which I got online) keeps adding ".pdf" to the name.
I think it has to do with the settings of the 'purple/underlined line' but I don't know if this is true and if so, how to change it.
View 2 Replies
View Related
