I can select the top cell in column "F" after filtering by multiple columns using VBA and arrays, but now want to I want to use the top cell in column "F" to search for all other equipment that uses this item.
E.g. remove filter, and reapply autofilter to column "F" based on selected cell as per below VBA
Note: Row 1 contains command buttons and row 2 Headers.
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.
For obvious reasons, the conditional formatting to shade alternate rows doesn't work when filtered. So I think I need another way of doing it. Luckily, my table is fairly static, rows aren't added or removed. The first column is excluded from the banding. As is the first and last row with data (1 & 67) respectively. I found a relevant thread here, but the code is beyond my understanding.
how to make this recorded macro work with more than the sheet it was recorded on. I need to work with the visible selections. But I'm having all kinds of problems trying to pick just the visible cells. Not having any luck with this project. I tried to included a test book but It keeps giving me an error.
I am looking for a way to autofilter a list in "Sheet2" based on the value in Cell A2 of the ActiveSheet. I have about 20 tabs in a workbook that need to be populated with a monthly sheet that is pasted into "Sheet2" every month.
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.
I've created drop-down lists in cells B3 and B4 (this is in worksheet 1), based on a column in Worksheet 2. I'd like Worksheet 2 to autofilter the data based on the drop-downs in B3 or B4, or both together.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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..
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))................