Name A File Using Values From Two Cells
Apr 5, 2013
I need a macro to name a file using values from two cells, one is a supplier name that is in a drop down box, the other is a count. Both cells are named in the worksheet and the macro below works on the count, but not the supplier.
Sub Order_Number()
Dim count As Integer
Dim Supplier As String
Range("F13").Select 'where A1 is your cell you wish to store the count in
count = ActiveCell.Value 'picks up last count value on spreadsheet
[Code] .......
View 2 Replies
ADVERTISEMENT
Apr 24, 2009
when I link cells from one file to another and insert a new line on the file I am linking to. the file that is linking goes down as follows:
=Safety.xls!$A$5
=Safety.xls!$A$6
How do I get Cell A5, to link to Cell A5 even if I add a new line in the Safety.xls spreadsheet. Taking the $ away does not work I have tried that.
View 3 Replies
View Related
Aug 1, 2014
I have a file that gives some statistical data to my co-workers every 15 minutes. A common question I get is "How does that compare to last week?" Then I have to open the file from 7 days ago, find the data from the same time interval, and subtract it from this week's number in my head. I'd like to have excel do this for me.
I know how to get data from an external file. The problem is, these files are named with a date on the end of the file name. So tomorrow, the static formula won't work anymore (or rather, it will give data for a file from 8 days ago, instead of 7). I'd like to excel to use today's date, find the file from 7 days ago, and get the data from that file to compare to the current file.
Here's what I've done so far:
Code:
ThisDate = Range("C1").Value 'the cell with todays date in it
ThisDateName = Format$(ThisDate, "yyyy-mm-dd") 'now formatted the way I need it
ThisDate7 = Range("G1").Value 'the cell with the date 7 days ago
ThisDateName7 = Format$(ThisDate, "yyyy-mm-dd") 'formatted correctly
ThisDate14 = Range("G2").Value 'the cell with the date 14 days ago
ThisDateName14 = Format$(ThisDate, "yyyy-mm-dd") 'you know, in case of a holiday 7 days ago
I have no clue what to do next. I want Excel to: Use "ThisDateName7" to find the file with the name "pph_tracker_[ThisDateName7].xlsm"Get data from a cell in that file (say, C15)Subtract it from the data in the same cell (C15) in today's file (this week - last week)Give me the result in today's file (say in cell C20)Do that again for cells D15, E15, and so on (result in D20, E20, etc)
I assume I can figure out the rest from there. Can I use the variable names in an actual formula in cell C20? Something like:
=C15 - '[pph_tracker_{ThisDateName7}.xlsm]Sheet1'!C15
or even
=C15 - '[pph_tracker_{The Date in Cell $G$1}.xlsm]Sheet1'!C15
which would require no macros at all!
View 3 Replies
View Related
Dec 20, 2009
How to save a file, with file- name. but the directory is to be read in worksheet "towns" in Cell1 (brussels) and filename in worksheet "names" in cell B2 (i.e. winter), so it saves to c:russelswinter.txt as a wordpad or kladblok txt file, that keeps a number, so each time we push a button "go back from worksheet names to worksheet towns" the "number" that is saved in the txt document goes up by value +1. In Flemisch, the "old" code goes as follows, and saves the number in the txt file Factuurnummer7.txt. But I want that the file name (here: FactuurNummer7) can be a variable text issue, which has to be read - as already noticed - in cell B2 (with the word WINTER). So the are 2 worksheets: towns, ans names
pad$ = Application.DefaultFilePath
'controle = Dir(pad$ + "FactuurNummer7.txt")
'If controle = "" Then GoTo EerstAanmaken
'Open pad$ + "Factuurnummer7.txt" For Input As #10
'Input #10, Nummer1
'Close #10......................
View 2 Replies
View Related
Aug 3, 2006
I have certain excel file that i want to be able to write a macro in order for it to extract certain information from certain cells on different sheet. In other words, i want cell A2 from each of the three different worksheet i have under different files. And have those data automatically update a master excel or access file whichever might be easier.
View 4 Replies
View Related
Oct 27, 2009
I M trying to get the lower values between to cells and have the lower valued cell highlighted,,,i have over 43 thousand lines of data to go throughand i was wondering if there was a quicker way to do this,,,for example cellA1 is $4.25 and cellA2 is $5.25 i want cell A1 to be highlighted,,is there a way?
View 2 Replies
View Related
Dec 9, 2008
I did my search, but cant find and knows what key search to look/type for...
If i have data A1 through A10, such as 1 1 2 2 2 2 3 3 3 3
How can i get column B1 through B3 as 1 2 3 ?
View 9 Replies
View Related
May 22, 2014
What I have In Column B, I have the datesIn Column I, I have engineers name What I need I want a macro to generate Serial Nos. (1,2,3....... n) in column A If an only if the date in column B is today's date and the engineer's name matches with the PC's username
The following is my code
[Code] ....
Above code runs without errors but does nothing.
View 4 Replies
View Related
Mar 14, 2013
I am trying to replace a range of cells with certain values but I can not figure it out. I'm almost there I think but don't know how to get the varying values I'm looking for. So I have a column (AJ in this instance) that has a bunch of "xx" values at different spots within the column. I want to replace those xx values with numbers 01 through 36. What I have below gets me just about there but it replaces every xx value with 01. How do I get it to go 01 on the first one, 02 on the second one, etc?
/code
Dim cell As Range
For Each cell In Range("AJ1", Cells(Rows.Count, "AJ").End(xlUp))
If cell.Value = "xx" Then _
cell.Value = "01"
Next cell
/code
View 3 Replies
View Related
Apr 13, 2009
I am trying to summarise data from 50 different files (updated by 50 different users) into one file in one place. To do this I created a simple sumif function that links to those files. This sumif works correctly when those files are open. But when I open my output file after changes have been made to one or more of 50 input files, sumif function doesn't work when I hit update at the file opening pop up question and get #value error. If I start opening those files this error goes away and it works great again.
View 2 Replies
View Related
Jun 16, 2008
Basically, I have a column that contains a list of names of files without the extension, ie "A2=File1" What I want, is for excel to open the file associated with the name in the column, "(A2).xml", and extract a value from that.xml file and insert it into B2.
I have searched and all of the examples I find aren't quite what I am looking for. I have an add-in that works exactly the way I want it, but does not extract certain values that I need it to. This is a commercial add-in so I don't have the source code for it or else this would be a snap.
View 9 Replies
View Related
Apr 13, 2006
I am trying to create a macro that opens a .pdf with the name of the pdf equal to a cell value. For a simple example: Cell "A1" contains the text "distinct" and I want the file prexisting file "C:distinct.pdf" to open when the macro is ran.
View 3 Replies
View Related
Feb 20, 2007
I have a file which I am using at work to compile production shift reports. I have created a sheet called Live Report and created a macro called 'New Day' that copies the entire sheet and renames it as yesterdays date (using a separate sheet called Data with a =today()-1 and text formulas thus the sheet tab for 14/2/07 is called 14 Feb) it then clears certain cells on the Live Report sheet so that information can be entered again.
My problem is that I want to now create a table of values that compiles values from each sheet for the month. What I wanted to do was have the names of all possible sheet tabs listed in column A and then as we get to each date the links will become active for the 20 or so values on each sheet. So each row will have the sheet tab name in column A and then columns B to R will have links to that sheet. As I am lazy and the linked values will always be in the same location I just wanted to drag the first row of links down the table using the value in Column A as part of the filepath/link.
My problem is no matter how much I try I can't use cell values in filepaths. If this is impossible I would like help with a macro that can find the right row in the table based on todays date and copy certain information.
View 9 Replies
View Related
Feb 27, 2007
I have a workbook with multiple worksheets. I want to save only a specific worksheet to a new file name and have only the values in the work sheet saved. This particular worksheet references values on another worksheet which uses VLOOKUP to pull data from yet another worksheet. I also want to automatically name the file using a pre-determined name located in a cell reference and save it to a specific location on the LAN drive.
View 2 Replies
View Related
Apr 25, 2006
I want to sum the values in cells E2:P110 based on the values column D. The
values in D are formulas resulting in something that appears to match D112 in
some cases. I'm using the following equation:
=SUMIF(D2:D110,D112,E2:P110)
My problem is that D2 :D10 have a formula in it and it's not matching. If
I enter the result of the formula, all is good. How should I deal with this?
View 11 Replies
View Related
Apr 15, 2014
I want to copy a csv file into a excel, but not all values will be copy into the Excel. Watch out for my code:
Private Sub ImportiereCSVDatei()
Dim wbTarget As Workbook
Dim wbSource As Workbook
[Code]....
dh. Zeros and values after the Column "Stand_NACH" will not be represented in the excel but the header will allways displayed
View 1 Replies
View Related
Jan 3, 2014
I have output in CSV as below
CSV FILE
Business Name
Address 1
City
State
Zip
Phone
Tips & Toes Nail Salon
9430 W 191st
Mokena
Illinois
60448
(708) 478-7420
[Code] ......
I need the above data to be replaced (TEXT File) in the below BLUE highlighted areas
TEXT FILE
Business Name Address 1 City, State Zip
< website details will be placed>
Business Name
Address 1
City, State Zip
Phone
< website details will be placed>
< website details will be placed>
Business Name
< website details will be placed>
City
< website details will be placed>
The final output should be creation of 10 TEXT (.txt) files as per the record count in CSV file in the above Text Format.
View 9 Replies
View Related
May 14, 2014
I need to look at the values in Column B Sheet1 and compare to Values in Column K in Sheet2. If there is a match, I want to put "Cashed" in the cell adjacent to the cell in Sheet1.
View 4 Replies
View Related
Jan 24, 2008
I have the following
1) directoryA
2) ColA - Contains oldFilename
3) ColB - Contains newFilename
I simply need to rename files from values in colA to colB working with directoryA with the extension of .jpg
View 9 Replies
View Related
Nov 11, 2008
I have an external file that is a table with 4 columns and about 25 rows
I would like to copy the contents of a file into a collection so I can analyze the data.
The problem is, I only know how to copy a whole line as a string and have no idea how to copy the data from the same line into different variables.
I also have no idea how to define a collection array
I will explain
say my text (data.txt) file looks like this:
AB 0.5 20 2/2/07
CD 0.2 15 2/2/07
FE 0.4 40 2/2/07
(example of input)
I would like to create a collection called trade which I have defined like this (obviosly wrongly)
Dim Trade() As Collection
Dim bs As String
Dim quant As Long
Dim price As Single
Dim calendar As String
Trade.Add (bs)
Trade.Add (quant)
Trade.Add (price)
Trade.Add (calendar)
so I would like to know how to define the collection array and how to input the data so that I don't have to insert a whole line into a string variable but can, instead break apart the line during the input stage
View 9 Replies
View Related
Sep 22, 2006
I would like to know if the data saved from a text file to an excel file, would affect the Lookup query.
My problem was vlookup kept returning the same values.
View 9 Replies
View Related
Mar 2, 2007
I want to add the columns in the attached sheet...any 4,say A,C,D and F.....as follows,if the number in the cell is less than .8,I want use 1 in the sum,otherwise zero. I would like to do this in 1 column if possible. in the attached sheet,I have filled in manually the answer in column L....Thus row 1 contained no numbers less than .8 in the selected columns,where as row 8 had all four numbers less than .8.
View 6 Replies
View Related
Mar 10, 2007
I created a series of combo boxes to accept values from the user. These values are intended to be used to build the name of a file they want to see. I tried to set whatever gets selected in each of the combo boxes equal to a distinct variable and inserted each variable into the file path at the appropriate point. I then created a variable for the file path and inserted the file path variable into code that opens that file, copies a particular worksheet, and pastes it into a new sheet in the “master” (the one with the form) workbook and labels it according to what it was named before.
The problem I’m having is that Excel says it doesn’t recognize the file path I’ve given it. Judging from the file path it quotes in the message, it’s not seeing my variables (only the part of the file path I entered in quotes) and then, later when I “debug” it says the variables are not defined. I’m wondering if there is more to defining a variable than what I’ve done. On the form page of code, I declared the following variables: The UserForm Code
Option Explicit
Public strPerformBySubtask As String
Public strPerformBySubelement As String
Public strPeriod As String
Public strMonth As String
I populated each combo box ok and remembered to call the sub procedures below, show the form, hide the form, et al. I think the problem might be in the sub procedures below that I thought would define the variables by setting the respective combo box output equal to a distinct variable I could use in the file path:...........................
View 2 Replies
View Related
Nov 11, 2013
I have data on 400 rows. Each row has a maximum of 10 cells with data, but many have empty cells with no data. I would like to sort each row to show values of cells in sequence and eliminate empty cells. I can use the sort row function but its a long process for 400 individual rows. Is there an easier way?
View 1 Replies
View Related
Feb 1, 2010
I'm working on a project for my company. We make plastic tanks and for quality control we want to start recording the thickness of the tanks in different areas/zones of each tank.
Attached to this message is an Excel sheet that I've been working on. From "Sheet 1", it records inputted thicknesses into WorkSheet "1098". On the top of "1098", it shows all of the recordings, and just below that are the "10 Most Recent Entries".
Right below the "10 Most Recent Entries", there are formulas to calculate the Min and Max Values. Whenever a new entry is recorded, the selected cells for the Min and Max formulas change. Is there a way to force the cells to always stay the same?
View 3 Replies
View Related
May 13, 2013
I'm trying to develop a new daily timesheet for my production workers, where non-production items are recorded in 15 minute intervals. The user would put in "Clock in" by the corresponding time, and the same for "Clock out" at the end of the day. Any non-production items will be type in next to their appropriate time. Since clock in and clock out times will vary, I need to set up a formula that searches the array of cells for the day, finds the "Clock in" and "Clock out" values, and counts any blank cells in between them. Basically the blank cells will equal production time, and the result of the Count function will be multiplied by 0.25 to get the hours.
I am having a very difficult time finding a way to set the "Clock in" and "Clock out" cells as the range for the Count function, because it won't always be the same cells. What would be the best way to automatically have excel find the cells containing these values and set them as the range criteria for a Count function?
The formula at the bottom was one of my initial attempts, but it didn't work. I took out the '=' for the screenshot, so that wasn't the problem.
View 5 Replies
View Related
Jan 6, 2008
I have the following data in 1 of the tabs.
[TABLE]
ClassSequenceAB
10SE132422
20SE23212
20SE321
20SE23425
10SE332455
15SE132412
10SE234
[/TABLE]
I want a unique count of sequences in a different for that class only if that particular row in 'A' or 'B' is populated. The result set should be as follows:
[TABLE]
ClassAB
1032
1511
2012
[/TABLE]
Can this be achieved through a formula?
View 8 Replies
View Related
Mar 31, 2007
I have situation which needs expert advice. I have about 22 Excel files of different branches with similar data. I have to link some calculated cells from each of these files into one file to show the consolidated data. There are about 18 calculated cells in each file, so linking each one is practically impossible.
View 10 Replies
View Related
Sep 23, 2013
I'm doing a Vlookup and when I copy down the formula Excel asks me to update the values by opening a new document. But this I can't or don't want to do - but I can't get rid of the box that asks me update the values with a new file.
How to remove this?
View 1 Replies
View Related
Dec 24, 2008
I have a sheet used to process sales orders - about 500 orders per month x ~1.5Mb each. I want to save them as smaller files.
The sheets to save are full of vlookups linked to external sheets and tabs, other formulas, and macros - most of which do not need saving when the sales order is filed. There are lots of merged cells too.
I must save:
1. Cell values
2. Cell formats incl merged cells, borders, colour, font etc.
3. The row and column sizes
4. Print set ups - print area, margins, page setup, header footer etc - (Everything needed to reprint to same as original)
I think I need a "File save as" style Macro which opens a dialogue box for the user to nominate the destination folder (& allows the user to browse for it), and a new file name.
The original file name is "Job Sheet - Omlaw.xls"
The tab/sheet to save is "Front Sheet"
(There are two other tabs - neither of which need saving.)
All cells to be changed to "locked".
The sheet must be password protected in Excel. User to enter it - or not.
The saved file should be "write protected" if possible?
View 7 Replies
View Related
