Create Zip Files Based On Cell Contents?
Jul 28, 2014
I have a excel file where I have the list of File Path in Column A & list of File names in Column B, Default path location is available in Column C. Now I want create a zipped folder based on the file name in Column B to the location Column C. I had browsed in Internet and found some code for creating the zipped file by selecting the files in Windows browser window. However my case is need to create the Zipped file based on excel cell contents.
I had found the code in the following location
Zip file(s) with the default Windows zip program (VBA)
create a code for creating the zipped folder based on Excel cell contents
View 2 Replies
ADVERTISEMENT
May 6, 2013
I am trying to create a macro that will open certain files based on the contents of a cell. There are three possible files that I will want to open.
File1.xlsx
File2.xlsx
File3.xlsx
If the contents of cell Q2 = 10, 20, 30, 40 or 50 then open "File1.xlsx"
If the contents of cell Q2 = 60, 70 or 80 then open "File2.xlsx"
If the contents od cell Q2 = 90, 100, or 110 then open "File3.xlsx"
If the contents of Q2 do not equal any of the possibilities listed then I would like an error box to show with the option to end or debug the code.
View 3 Replies
View Related
Jun 23, 2014
Can a macro rename Excel files based on a cell's contents? The problem maybe that I download these files en masse from a website and they all have 54-character long randomly generated filenames which I can rename by selecting all and then use the rename function to get a series of filenames like a, a(2), a(3), ... but I would rather have the files renamed based on the text that exists in cell B1 which is the title of the report. I also read somewhere that this kind of macro only works if the tabs have names like "Sheet 1"
View 1 Replies
View Related
Jan 15, 2014
I have a spreadsheet that lists dates in row 6, columns V through BE. We use these dates to log attendance for kids, so under each date there may be one of many symbols, such as "x" if the student was there. I would like to create a list of the dates that meet one of two criteria: either blank or containing "WR". So, let's say column Z is blank and Y has "WR" in it. In column FB I would like a list of all of the dates that were blank or WR, so I would want the dates contained in row 6 for Y and Z. Is there a function that will 1) look for a blank or WR across a row, 2) find the associated date for that blank in row 6, and 3) write the in another column in the form of a list, each date separated by commas? Here is an example of my information. I would like for a function to automatically make the list in column FB for any dates that are blank or contain "WR".
V
W
X
Y
Z
AA
AB
AC
...
FB
[code]....
List of Dates Absent
x
x
x
WR
x
x
x
1/23, 1/24
[code]....
View 2 Replies
View Related
Jun 11, 2013
In every month I have to send report to each sales person provided one sales person should not get info of another sales person. Now I am doing it by making file after sort using copy and paste which is time consuming. Is there any way that I can make individual file for each sales person in shortcut way.
View 1 Replies
View Related
Mar 12, 2014
I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.
View 3 Replies
View Related
Jun 26, 2008
I have two columns one of which has filenames and the other having values. I need to creating a macro that takes each value from a cell outputs it into a text file and then saves it with the filename of the cell next to the value. I'll need it to go down the columns and create separate text files for each.
View 7 Replies
View Related
Dec 24, 2008
I want to have a cell that looks at another cell based on a number that is in another cell. So imagine if you will: A1 contains the number 25. A2 contains the formula I can't work out. I want it to look at the row that is the number contained in cell A1, so in this case it would look at row 25.
Now I want to narrow it down further to a cell in that row but this will be a constant so lets say column F. So if A1=25 then A2 would display what ever is in F25. If cell A1 were 16 it would display the contents of F16 etc. The best I got was something like =IF(A1=>1,(F(A1))) but obviously that is wrong as it doesn't work.
View 2 Replies
View Related
Apr 25, 2006
I want to add a row underneath a row if the value in the in B cell is not = "". Then i want to write a value in the C cell of the new row.
View 2 Replies
View Related
Oct 15, 2008
I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.
Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
View 5 Replies
View Related
Feb 18, 2009
Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.
View 2 Replies
View Related
Apr 30, 2009
I am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells.
For example:
Cell 'A1' starts out blank.
Cell 'B1' has a drop down to select specific items.
Cell 'C1' is the date of the order and is a locked cell.
Cell 'D1' is the date of completion and is a locked cell.
The worksheet is protected to prevent direct changes to cells C1 and D1.
If a user enters any information in A1, then C1 displays the current date.
If a user selects 'Complete' from the drop-down list in B1, then D1 displays the current date. Both of these actions are independant of one another. This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at.
View 5 Replies
View Related
Apr 28, 2014
I am working with a spreadsheet that has our user data; User(First) Name, User(Last) name, Computer Name, MAC address, Phone Number, Seating Position and Network Port Number.
I have let this get horribly out of sync, moved computers around, and moved them while the MAC address column was hidden, As a result, I have a bunch of users listed as being at the wrong computer. I have a second spreadsheet I generated that gives me the logged in user for about 2/3 of my computers, with the computer name(Just 2 columns, 'A' and 'B'. I would like to have Excel match the computer name and then overwrite the user name. For example, if the first computer in my correct user list is DELL-99945ty2, and the user name is "Jimbo Jones", I would like it to search the first Excel doc(The one with all of the user data), and replace the user name with "Jimbo Jones". Is this simple and straightforward?
In my main spreadsheet, the user name is in column B and the computer name is in column G, So I want to use the values from Column B in my second document to find its match in Column G of the first spreadsheet, and then replace the data in Column B with Column A in the first spreadsheet.
View 5 Replies
View Related
Oct 13, 2013
Is it possible to dynamically chance the name of at a worksheet that already exists based on the contents of a cell on another sheet?
I.e. If the name of a sheet is linked to the value of the Cell A1 in the "Data" sheet and I change A1 to read "Group1" it changes the name of the sheet as well?
View 10 Replies
View Related
Dec 14, 2013
I am working on a sheet where I want the contents of cells D1:D10 appear if cell A1 has a text in it and the contents of cells D1:D10 disappear if cell A1 is blank. What is the right conditional formatting formula for this.
View 3 Replies
View Related
Oct 17, 2008
I wish to be able to lock an entire row after an entry has been made in a certain cell.
e.g. let's say i want to lock row 9 in its' entirety following entry of "Yes" in cell G9. If there is no entry in G9 the the entire row should be still unlocked. Likewise with any row with a corresponding entry in column G.
View 14 Replies
View Related
Aug 28, 2009
I have put together a spreadsheet to help with the creation of work orders. I have a summary page that the user can define which category the work order falls under along with this I have a couple of macros set up that will print a specific work order pertaining to the category they selected. I have run in to a few problems (mostly training users) that I would like to eliminate up front.
I am looking for a macro that I can use to look at the contents of a cell (contains a word that corresponds directly to the name of one of the sheets in the work book) and print the worksheet that has the same name as the contents in the referenced cell.
View 3 Replies
View Related
Jan 11, 2010
I have thousands of items each with a unique barcode(column A) and each is catorgorized either KEEP or DISCARD (column B). What I want to do is scan the barcode (column A), Vlookup and return KEEP or DISCARD (column B). Now based on what the vlookup returns can I have a sound file play for KEEP and a different one for DISCARD.
View 11 Replies
View Related
Sep 12, 2013
How to Lock/Unlock an Excel Cell Based on the Contents of Another Cell?
View 1 Replies
View Related
Oct 20, 2013
I'm trying to set up a dependent cell (B) based on what is in the independent cell (A)
If A is blank, B should be blank
If A has any value other than X, B should display a set string Y
If A contains X, B should contain a drop down list with a range of choices.
I've tried setting it up as a dependent validation cell and I've tried using OFFSET, but the best I can do is to get three different drop down lists to populate cell B. One with only string Y, one with only a blank, and one with the range of choices. The problem is in the first two cases above, I want the cell to fill automatically with either blank or string Y -- I don't want them to just be drop down options, with the cell displaying the last value that was input.
View 2 Replies
View Related
May 2, 2012
I am creating a macro to automate data analysis for work, but I've become stuck.
I need to find the coordinate of the first cell in a column with word "reserved" or "extended" contained in the cell, and then assign the row number of that coordinate as a variable to use for moving data around. Basically, I want something like VLOOKUP, but instead of returning a value, I want it to return the coordinate.
View 1 Replies
View Related
Mar 9, 2013
I am relatively new to VBA. I am creating an attendance calendar that tracks employees calling sick, late etc.. It is a point based system. What I am looking for is, a way to clear the point value that was manually entered in a specific cell (I3), if there is a Value manually entered in (CU3). Each column in my worksheet is for a specific date ie; I3 is the cell where I enter the points (1.00) for that employee by calling in sick on 3/1/2013, (Column "I" is for 3/1/2013). After 90 days, this point accumulated by the employee does not count against them, so I need that point entered in (I3) to either = 0 or the cell contents to be cleared if there is a value entered in cell (CU3) which is 91 days after, so my (A3) cell does not add that point acquired on 3/1/2013.
I need this to run in a range (I3:I450) so if any value is entered into (CU3:CU450) it has the same result and continue to for (J3:J450) so if any value is entered into (CV3:CV450) and so on..
View 9 Replies
View Related
Mar 7, 2007
I am a financial administrator & every month I have down load 4 bank accts as CSV, import into xl & code (CACode) the amounts for our accountant. I have VBA that formats, adds headings & formula etc but I have a problem/s. In H col I place CAcode & I use a sumif formula in I col to sum all the amounts with that have the same CAcode. For simplicity sake I copy the sumif down & then sort H col ascendindingly (this is done by VBA). Now I am trying write a macro to clear the contents of the cell in I col
if eg h60 = h59
then I60 clearContents, Select h59
Else select H59
Do until H3 is selected
Psuedocode
Select table (A2:I Xldown)
Sort Table by CAcode (H Col)
Set Range as H3:Xldown
Select Last cell with CAcode (Xldown) in H col
For every cell in Range (H3:Xldown)
Use If/thenIf Last cell = 2nd Last cell (H Col) then
Clear contents of I col (last row)
select 2nd Last cell (H Col)
Else 2nd Last cell (H Col)Next Cell
I have to use Xldown to select range as the range will be variable each month & for each bank acct. I need to clear contents of cell to verify that all the sub-totals of unique CAcodes of the CAcoded amounts = the totals Because I may have up to 120 rows X 3 bank accts I am slowly using vba to do my work.
Sub sort_And_delete_Sumif_amounts()
Dim r As Range
'Select range to sort
Range("A2:I98").Select
'Sort CAcode in H col ascendingly
Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'select range for comparisomn of CAcode
Set r = Range("H3", Selection.End(xlDown))................
View 4 Replies
View Related
May 13, 2013
Basically lets say I have in cell A1 to A10 floor access data i.e. Users who used their ID cards to access a room.
Now the data always starts with either Rejected or Admitted then the user's name and then the card number and the access floor etc. the card currently has.
What I am looking for is that the macro should first check if the cell has Rejected or Admitted written in it - this I can do myself using =Left(A1,8) which should give me the helper column I need.
Then based on this I want it to only copy the name of the individual i.e. it should look in the cell and only copy whatever is written between "Rejected" and "(Card". The cell data is something like this:
Rejected Doe, John (Card #123456) at ABC 123 Floor1/Floor2/Floor3/Floor4 Door 1 [In] [Clearance]
View 4 Replies
View Related
Dec 18, 2013
We do large loads of new part #'s/descriptions/UPC's/etc from excel into our database. The fields are limited on character count and I would like writing a formula to break the contents of a cell based on character count without breaking a word.
Examples
Column A Current Data
Row1: 12Pt HD Offset Striking Wrench 1-3/8" & 35MM (44 characters)
Row2: 12Pt 1/2" Drv Socket 27MM (25 characters)
Row3: 12Pt Satin Combo Wrench 11/16" (30 characters)
Row4: 12Pt 1/2" Drv Deep Socket 1-1/8" (32 characters)
Results I'm Looking For
Row1: Column B-12Pt HD Offset Striking Wrench Column C- 1-3/8" & 35MM
Row2: Column B-12Pt 1/2" Drv Socket 27MM Column C-No Data Returned
Row3: Column B-12Pt Satin Combo Wrench 11/16" Column C-No Data Returned
Row4: Column B-12Pt 1/2" Drv Deep Socket Column C-1-1/8"
View 8 Replies
View Related
Mar 14, 2009
I'd like excel to calculate 3 totals for me based on the colour and value on a worksheet.
Basically, I work for various people and they pay at different rates per hour.
I currently have a spreadsheet with their names, times, and rates (see attached for example), but I calculate the amounts paid and due manually.
If possible I would now like excel to do it.
To explain further, 'J' gives me $10 per hour, and 'V' gives me $5 per hour.
Cells shown in red show work done but not paid for.
Cells shown in green show work done and paid for.
I'd like excel to automatically create totals as shown on the spreadsheet, namely:
Total due: xxxx
Total paid: xxxx
Total outstanding: xxxx
At any time during the month I can be asked to take on more work - I would then enter the code into the spreadsheet for the hours requested...and I'd like the totals to be update automatically.
View 7 Replies
View Related
Jul 25, 2014
I have inlcuded my workbook that I use for tracking the NFL season and I'd like to add in some team logos just to make it look a little cooler.
The attached workbook includes three sheets, the first of which uses a drop-down for selecting picks. The second sheet has the team logos and the third sheet feeds off the first.
I'd like for team logos to populate into the third sheet based on what is selected in the first sheet.
I've already tried a few methods, all unsuccessfully. I tried this [URL] which was a great idea but it didn't allow for the same image to be repeated potentially 100 times. I also tried a method involving named ranges and indirect lookups to the linked picture image but that would require a different named range for every person and every pick which isn't feasible.
if it's possible to just do a massive nested if statement with a vlookup to return an image but I haven't been successful in that either.
View 5 Replies
View Related
Aug 8, 2014
I'm trying to create an autofilter that can filter each category that gets listed in a specific cell.
I have some code from another sheet which I'm trying to modify to accept more than 1 filter criteria.
[Code] .......
Where W2 lists something like:
"Blasting, Flotation, Drilling" and I want it to filter all three categories in the location J24 to the last row with data in it.
I believe I need to apply "*" in locations but I'm not sure where?
View 4 Replies
View Related
Dec 18, 2008
I have a range of cells that I want to watch for a change in contents. If any cell in the range has a change in contents I want to launch a MessageBox that asks if they wish the new value to be entered automatically in all of the other cells in the range. Example:
Range = B8:M9
User changes F8 from "empty" to 38. MessageBox should launch asking if they want 38 to be entered into all of the cells in the range. Upon answering yes new contents of F8 should be inserted into the rest of the range.
View 4 Replies
View Related
Jan 13, 2010
I'm trying to clear cell contents based on a defined name given to a set of various cells in my worksheet. The cells are not continuous, but since they're given that defined name, I don't think it matters.
The defined name is listed as "CommentsFields".
The worksheet name is listed as "QPRForm_V6"
I found a similar thread, but cannot get it to work with what I'm looking for. I'm relatively new to writing VBA/macros, so I'm not sure what to do.
View 6 Replies
View Related
