I have a sheet with a country in it in A1 (validation list).
Depending on the country in A1 a country specific IF command has to be executed on a column1 with first cel = A3. This IF command also relies on the values in column 2 and 3 on the same row. (B3 and C3 are in the IF cmd)
I taught to do it like this : =IF($A$1="Spain";$A$5;0) with A5 being the country specific IF command which should be executed when spain is selected. however when I do this, this only works for the first cell ( I cannot drag this formule down, since it will always give the value calculated in column 2 and 3 on row1.
I'm not sure if the best way to 'select' the country specific IF cmd is with another IF command..
Let's say I have data validation on column A, which only allows 2 values, "Number" and "Letter". Easy enough. If the value "Number" is selected in A1, when the user moves to B1, I want a validation list of 1,2 and 3, when they move to C1, I want a list of 4, 5 and 6 and when they move to D1, a list of 7, 8 and 9. However, if "Letter" is selected in A1, when the user moves to B1, I want a list of R, S and T, when they move to C1, U, V and W, and when they move to D1, a list of X, Y and Z. I tried using dependant validation, but can only get all three columns (B, C and D) to either be 1, 2 and 3, or R, S and T. The validation lists would be existing named ranges on a separate sheet.
Let's say I've got nine entries of data. Column A is aligned like this:
One One One Two Two Two Three Three Three
What I would like to do is write a macro so that the three entries with "One" in Column A get cut and pasted into a newly-created sheet named "One." All of the Two's get cut and pasted into sheet "Two" and the same thing with the Three's.
I have 2 columns and 10 rows (A1:B10). In column A are certain text values. How do I write a macro to go through the table and preform an operation in the corresponding cell in column B depending on the value in cell A? for example, if A3 = "Complete", overwrite the formula in B3 with the value (paste value)?
I have a spreadsheet with names and start / finish dates columns down the left hand side then a row of week commencing dates along the top.
for each name row I would like to change teh fill colour of a cell to green to represent the week in which they started and to red for the week in which they finished. I therefore need to cross reference the start and finish dates for each name with the relevant week commencing dates at the top. Somehow! I presume there is some kind of vlookup type formula that I need to use in conditional formatting, but I am not sure what.
get a cell to return a value depending on whether or not a cloumn is populated for example. If i Have 3 columns EUR, GBP and CHF and there can (99% of the time) only be one entry in any of the 3 on any given row. How then can i get a 4th column to return the value EUR, GBP or CHF on the same row as an entry. I have a relatively complicate nested IF fromula but i think this is slowing down the worksheet as there are 500 or so rows containing it. Ive attached an example segment
I have a file consisting of two columns, called, "bom ref" and "material" I need to show the data as indicated in the file, headed required format required.
In effect where I have a 0, that is the material I need to show for every row with a bom ref. of 1,2 or 3, down as far as the next 0 but not including the 0, when I reach the next 0, the material is a new letter, and that letter needs to repeat down as far as the next 0, but not including the 0 and so on.
I currently written the following formula, but always have issues when adding additional depending on selection.
D8 is the selection ( Validation ) on PERF EXP FORM Tab, Once you enter the selection... you then go to the Accounts per hour Worksheet =IF('Perf Exp Form'!$D$8="Test1 14-18 station",IF(('10220DBHE'!B9=A6),IF('10220DBHE'!C9 > 11000,11000,'10220DBHE'!C9),"N/A"),"N/A")
Perf Exp Form Perf Exp Form *ABCDEFGHIJKLMNOPQ4 *1. GENERAL INFORMATION****************5Customer***Date / Name(s)*6ApplicationSheet Size1UP/2UPOrientationSOG/EOGFold TypeFold Plate# CH***78 1/2 x 142 UPPortraitSOGLetter/ZHeavy1***8Machine / model**Enduro 4-6 station9ConfigurationCHANNEL 1CHANNEL 2CHANNEL 310590-4720-4910 Dual DeckNONENONE11Date / Name(s)*** Excel tables to the web >> Excel Jeanie HTML 4 ......
I have created a color coded calendar on a spreadsheet for work that shows when a book will be published depending on when it comes into our warehouse, this is on sheet one.
I would like on sheet two to have some kind of filter or statement that would eliminate the need to look at the calendar, I.E. the user would just have to type in the date or select the date from a list and excel would show you what day the book would publish on using the data from sheet one.
I can imagine that this is hard to picture so i have attached the file as well.
I have several blocks of data that I have defined under several names to make data processing much easier when writting formulas. They problem that I have right now is that depending on the value of one cell, I need to call up a given defined name block of data to be used on the calculations. These blocks are labeled as DATA400, DATA500, ... user will input information that will allow me to determined what block needs to be used, user will input data like 400, 500, ..., but to be able to call a block of data I need to reference the complete label DATA400. I have tried to concatenate "DATA" to whatever value is input by the user and use the concatenated value to call the specific blocks of information but it doesnt work.
I want the assignment from worksheet 1 to worksheet 2 to be automatically. i will set the start and end date next to the component in the first table and assign it to a person/s and it will be automatically get filled in the second worksheet (the calendar) under the person/s and under the same dates as set in the first worksheet.
I need a formula that displays the word "Day" or "Days" depending on cell entry i know the formlula =IF((B12)>0,"Day","") to show the word day but i want it to show "day" if a 1 is entered or "days" if any number over 1 is entered.
I have drop drown menus that allow you to select 1 of 3 choices and based on those a second set which lists the options for the first three (subset). Now depending on the second value I need to pull a set of x y coordinates that can be of varying length. '
Drop down menu 1 1560, 1669, 1554. Drop down menu 2 1560-1,2,3,4 1669-a,b,c,d. This has been done now lets say I select 1560-1. I need the following to be pulled from a list, the data is a block.
I have tried a few other examples in previous threads that use COUNTIF and ISERROR but I just can't get it right.
Basically I want to average the cells in column J (J2:J6,J8:J12,J14:J18, etc). My aim here is to determine the average value for each day (Averaging 0 values in this instance is OK).
Now, the date in column A (A2:A6,A8:A12,A14:A18, etc) sometimes returns a blank value, depending on the day of the week (see attached example). This is because, in the full version of the workbook, I have Column A date linked to a perpetual calendar and therefore the dates can change cells depending on the year.
For the Date Cells that return a blank value, I do not want the AVERAGE equation to include the 0 value on these days.
For an example I have attached a month of February where there are blank date cells at either end of the column.
I would like to know if there is a vba code doing this I have eg 100 columns starting from the column B. At the first 3 rows there are some data. Now im using the data from the rows 2 and 3. The row 2 has column numbers (TEXT formatted) from the user. The numbers starts from the 01 and goes on. But it mights be more columns numbered with the 01 or 02 or 03 or.. The row 3 has consecutive numbers and stars. The numbers start from the 1 and stop when the row 2 changes the number. The star appears when the user insert a new column (to the following example are the columns J, K, L &M )
I need command that takes in consideration values from column F only if in column B is value 4.8. And than, I need minimum value of those taken in consideration .d.png here is attached picture regarding my problem.
I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].
I have a workbook to record the attendance of students. Sheet attendance9 for September's attendance. Enter 1 for present, 0 for absent, L for late, E for early leave and blank cell for no schoolday. Sheet "remark9" is used to record detail of attendance for September. In sheet "attendance9", I want to display in cell AT2 the number of times a student absent for 7 or 14 consecutive days. How to count from E2:AH2?
I have a multicolumn (3 columns) listbox that is filled from a sheet (ProductList). Col1=product code (column A); Col2=description (column B); Col3=prices (columnC). I fill it when the form loads like this:
VB: Dim rng As Range With Worksheets("ProductList") Set rng = .Range(.Range("A2"), _