I want to be able to send out one “master” workbook with the fourteen ranges, and use a pull-down list (or anything else) so when the user selects a state, the proper lookup range will be used. In the attached abbreviated example, I want the lookups in Column B to refer to the state designated in B2. In this case, the VLOOKUP works correctly, since Colorado zip codes are in the ColZip range.
The people in Idaho or Oregon using this will only be working with their respective zip codes in ranges IdaZip or OreZip, so if they enter their state in B2, I would like the VLOOKUP to look in a different range. My whole idea is to only have one master workbook so I don’t have to create one for each district office. Maybe I’m thinking in a wrong direction altogether.
I have some dynamic named ranges in sheet1, and in sheet2 I have data validation dropdown list which has the names of all the DNR's in it.
What I want it to do (in sheet3, no less) is for the user to be able to pick a named range from the dropdown list, and have a particular column in sheet3 then display that entire named range.
I have made a 'data' worksheet which is the source for the data validation in sheet2. Each item in the list identically matches the name of each of the named ranges. I was hoping to be able to use some form of =INDIRECT but alas, no such luck.
So it's almost like a copy and paste function I'm after, where:
If you pick "Schedule_From" out of the DD-list, then DNR 'Schedule_From' is what is pasted in Column B in Sheet3.
I need the array portion of a lookup formula to change based on a cell value.
On a worksheet(named "groupings")that groups students according to ability level, I have data validation lists where teachers select the criteria for the group. In cell I5 they choose the period, and in cell L5 they select which assessment to look at.
Cell B8 will either say high low or medium. Then in cell C8 I have this formula: Lookup_Occurence($B$8, ??????, 1,1,2)
Again the array will depend on what is entered in cell I5 and cell L5.
I have attached one of the worksheets the lookup_occurence will be referencing. In this exit card worksheet if cell B10 (the name of the assessment) matches what the teacher chose in cell L5 on the groupings worksheet, then the array formula will be 'Exit Card'!Card1. If cell G10 matches what the teacher chose in cell L5, then the array would be 'Exit Card'!Card2. Where A9:C339 is named Card1 and cell F9:H339 is card2 etc.....
However, it gets even more complicated then that because within Card1 there are 8 periods, and if a teacher chose period 1 in cell I8 on the groupings sheet, then I only need the array to look at the period one data in the card1 array.
Essentially what I am trying to accomplish is create a page where teachers can group students by ability level according to any assessment they choose. On the exit card page the formulas currently equaling # Div/0 will say either high, med, or low depending on how a student performs on that assessment.
I have a UDF for a lookup_occurrence formula (thanks to Dave), and I was wondering if it was possible to tweak the range it looks at with if statements.
I have a formula like this =Lookup_Occurence(B13, INDIRECT($N$5), 1,1,5) where N5 says exit card 1.
I need it to only look at a certain rows within exit card 1 depending on the value entered in I5.
If value I5 says Period_1 it will only look in rows 12:46.
If value I5 says Period_2 it will only look in rows 48:82 etc.
I believe I need to modify the xl look part of the code with if statements.
Below is the code. I am wondering if I can replace the xlLook line with nested if statements. If I5="Period_1 then rows 12:46" etc.
Function Lookup_Occurence(To_find, Table_array As Range, _ Look_in_col As Long, Offset_col, Occurrence As Long, _ Optional Case_sensitive As Boolean, Optional Part_cell_match As Boolean)
Dim lLoop As Long Dim rFound As Range Dim xlLook As XlLookAt Dim lOcCheck As Long
I get a report each day with a list of issues. the "group" that works the issue and the "priority". Based on these two factors, i need to do a double lookup (vlookup?) to another tab or file to match the priority and group and see what value should be brought back for each lines results. For example, if group1 had a prority3 issue, the lookup would find the value from the other sheet or file and bring back the value and put it at the end of the row where the formula is.
Attached are examples of the sheets. sheet1.jpg sheet2.PNG
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.
I'm trying clear the contents of a column range when the cell above this column range is empty. I know how to do this for one cell, but I would like it to work for a rangefrom R15:BB15.
If IsEmpty(Range("r15")) Then Range("r16:r35").Select Selection.ClearContents End If
I've got a spreadsheet with a 'data' sheet and I want to add and name additional sheets based on the contents of a range in the 'data' sheet - it could be 1 extra sheet, it could be 100, the range is open ended.
There is data(numerical) or blanks in each of the cells in the range H32:O37
I want to clear the contents if they are a duplicate of the previous line.
Looking for a macro hopefully. Found many to delete the whole row but just the specific range based upon the duplicate criteria..As there is data surronding that area do not want to delete whole row just clear that specifc area.
H32:O32 clear contents if same as H31:O31 if any valuse are different then stays the same H33:O33 clear contents if same as H32:O32 if any valuse are different then stays the same H34:O34 clear contents if same as H33:O33 if any valuse are different then stays the same H35:O35 clear contents if same as H34:O34 if any valuse are different then stays the same H36:O36 clear contents if same as H35:O35 if any valuse are different then stays the same H37:O37 clear contents if same as H36:O36 if any valuse are different then stays the same
I am just learning VBA. I need to move to a cell location that is named in a range. As follows:
The cell "Sheet2!A1" has a formula that results in "Sheet1!B3". I would like my VBA code to read that cell and select Sheet1!B3. When using something like:
Application.GoTo Range("Sheet2!A1")
I end up of course at Sheet!2A1. I want to end up at Sheet1!B3 or wherever the cell reads at the time the code is read and operated on.
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 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.
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.
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.
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?
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 sum a range of data consisting of 12 cells (each range is in a unique row going across adjacent columns). The range of data to be summed is based on a lookup value which is a column header - I want the range to start with this lookup value. In column B of my spreadsheet, I have a 'First Activity number code' going down...In Row 4, I have the same First Activity Codes going across the row (the column headers). In Column U, going down the column I am looking for my formula.
So For example, in row 5 of my spreadsheet, I am looking at 73 as my base code (the value in column B), and I want a range of data summed that lies in the same row where the column headers are 73 - 84 (12 rows) - always starting summing the range with the same number that is in column B. So in row 18, 78 is my base number; so I would want the sum of the numbers in the same row where the column headers are 78-89 ( 12 columns).
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))................
I'm trying to figure out the best way to reference a cell's contents as a range in VBA code. So essentially it would be simple like this (except of course this doesn't work)... Range("cell(contents,A1)").Select
I always look in my CD of old forum posts, Mr. Excel articles, Mr. Excel books I have etc. before I break down and ask.
