I have a worksheet that I want to populate with specific data from another worksheet.
If all of the following are met:
1) Column A equals "763" and
2) Column K is formatted Red (255, 0, 0) from conditional formatting
3) Column P is blank
Then in my blank worksheet I need to display the data from Columns A, B, D, K, O
Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
View 9 Replies View RelatedI have been using a macro to search and highlight customer addresses for me, however I have changed my worksheet and now have the addresses in two columns instead of one (D for the numbers, E for street names) for sorting purposes.
What I would like to do is either; find a way to search using data from 2 cells to get a result, or have multiple results highlight and popup.
Using: Excel 2010
Code:
Sub SEARCH_FUNCTION()
Set FoundCell = Sheets("Route").Cells.find(What:=Sheets("Intro").Range("G15"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not FoundCell Is Nothing Then
With FoundCell
[Code] ...........
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
I am trying to find a way to write a Macro that will auto color multiple cells based on what data is displayed in one cell. The cell I want to reference is a vlookup cell.
Basically this is a part label. And depending what part is selected from the list my vlookup will display its position on a vehicle(i.e.. FR, FL, RR, RL, Etc..). So if vlookup comes back with FR I want the various cells on the label to be orange, etc..
ALSO: if there is a way to embed it so it does this automatically (rather than run the macro each time).
I am setting up a spreadsheet for a Stock Broker. I need to have a cell produce a value dependent on the value of another cell. Specifically, I need the calculation cell to change depending on whether another cell has the letter A, S, Q, or M. I am calculating the value of investment income based on the Payment terms (A;annual, S;semiannual, Q;quarterly, M;monthly) So if for example Cell A7=A, then Cell C7 =”specified formula for Annual income”
View 5 Replies View RelatedThe data attached shows the length of individual fixations (A4:A8) and whether the fixation was ON (=1) or OFF (=0) a specific target (C4:C8). I want to know how much time was spent ON target for each 250ms time bin (i.e. between 0-250ms, then 250-500ms etc).
My problem is that each fixation is a different length and may cross over the time bin boundaries. IF(AND(THEN ) works for the first few time bins but I get stuck once the first fixation is accounted for. I have filled the rest in manually so you can get an idea of what I want it to do.
So I've been trying to set up a table.
The table is made of 4 columns, with several times the same values. Each column has a specific range of value. No possible combination can exist twice. Meaning I won't have two rows that are identical. It looks like :
A1@X
A1@Y
A1&X
A2&X
B3#K
B3#O
B3£K
B4£K
C5$P
C5$M
C5*P
C6*P
The user tells me the value he wants in the 3 first columns, and I want to tell him what's in the 4th one.
I need help on using the If function. Basically I am trying to calculate the cost of shipments. For example, cost of sending 1 box is 150 (eg cell A3), 2 boxes is 280 (cell A4), 3 boxes is 400 (A5), 4 boxes is 500, etc upto to 10 boxes (cell A12).
What I am looking for is cell A1 to provide me the cost based on the manually entered value of cell A2, i.e. if I enter a number between 1 to 10 in cell A2, then A1 should return a number from A3 to A12, e.g. enter 3 in A2 then A1 should return 400.
Is there such a formula or do I have to enter manually the amounts into an If function?
I have 5 options that I am designating as "Allocation Methods" for a number of line items. They are numbered 1-5, but i have custom formatted them so that they have text in the formatting. They are as follows;
Allocation Method
1 - Allocation per Hard Cost
2 - Allocation PSF of Proj. or Hotel
3 - Allocation per Loan
4 - Allocation per Parking Spaces
5 - Allocation for Park
I will allocate one of the above allocation methods to every line item in an allocation column by hardcoding 1 - 5 in a cell for each line item. What i cannot figure out (or figure out if it is even possible) is how, for instance, can I type in a 1 and it have it automatically formatted per number 1 above & then if I change my mind to method 2 and type 2 it will automatically format per number 2 above. I know I can do this by creating a reference column right beside by allocation column, but I would prefer not to use that method.
On the "CurrentCustomer" worksheet tab I am trying to accomplish the following:
When the merged cells in the F column (F3, F25, F47...F443) equal the word "Contract", I need the corresponding total cells in the E column (E23, E45, E67...E463) to add up, if and only if the corresponding F column merged cell equals "Contract".
For example, using the first three ranges, if F3, F25, & F47 all equal "Contract", I need the corresponding E23, E45, & E67 to add up. If F3 & F47 equal "Contract", but F25 equals anything other than contract I need E23 & E67 to add up only.
IF there is a whole number in either column a, b or c, I need to have column e reflect that with the insertion of a / for each column that has a whole number. i.e.
Column - A - B - C - D - E
3 4 / /
5 6 9 / / /
I am attaching a copy of the spreadsheet showing the examples, I can't seem to get the function to work.
I have a bit of an odd issue I need to solve. I use an Excel spreadsheet to track daily income for the store over a months' period. I have each day set to give me a rough estimate of profitability for that day. I also want to create a cell that tells me how much we've made so far, based on the number of business days so far in the current month. For example:
Day 1, $1500, $800 in expenses (monthly expenses averaged over the month, plus labor costs for the day), so a total of $700 profit.
Day 2, $1700, $749 in expenses (same as before), so a total of $951 profit.
The formula I'm using to calculate that isn't an issue, but I have the same formula for every single day, and each day has it's own cell with the estimated profitability for that particular day. In two other cells, I put the total number of business days in that month (in this case, 26), and in another cell I put the current number of business days we've had this month (in this case, 4), which is used partially to calculate the other cells. I want to use them to create another cell to contain the estimated profitability based solely on the number of business days out of the month we've had.
So E3 through E33 contain the individual days, I want to calculate - since we've only had 4 business days - what E3 through E7 would total. When I change it to 5 business days, i want it to calculate E3 through E8. So if we've had 4 business days, it only calculates the totals from the first 4 cells, if 5 business days, then the first 5 cells, etc, etc.
I can sort of picture it in my head, but I can't seem to find the necessary equation for it. It may require a macro, but I'd rather use a formula if possible. I'm not a big fan of macros.
Please check the attached workbook. Sheet "Data" has some bank account data, and whether the dues are paid or pending.
What I need is, I need to extract only the data of Column A and B into a new worksheet, only if G is in the Pending status.
I don't want to use a macro, nor a pivot, nor autofilter. I need to do it through formulas. I have seen this been done before, but can't figure it out. The max number of pending accounts wont be more than 10, so need to just pull the formula down 10 rows. I have already entered the data in the second sheet that would be the result.
This second sheet is sort of a supervisor sheet, so that whenever they want they can open it to see the number of Pending accounts.
I have been stuck for about a week on trying to create a Case Select macro. I am a novice in using VBA, so I am not sure what I am doing right OR wrong. What I need the macro to do is go through all of the text in a column and if the text is one of the 50 states, I need it to return one specific value, and if it is anything other than one of the 50 states, it needs to return a different value in a column 5 over from the column with the states in it. I have changed the code MANY times and so far I haven't been able to get anything to work. Here's the gist of what I have been trying:
Sub usstates()
Dim R As Range, s As String
Set R = Sheets(1).Range("J1:J50000")
For i = 5 To R.Rows.Count
X = UCase(R.Cells(10, 1))
If X = "AL" Or X = "AZ" Or X = "AK" Or X = "AR" Or X = "CA" Then
R.Cells(i, 2) = "SC"
End If
Next i
End Sub.................
I have a step in a very long macro to populate a sheet (SheetMaster) with the course completion status based on another sheet (SourceData) in the same workbook.
On "SheetMaster" Col E starting with row 2, I want to look at whether a concatenated value of E1 + A2 is in Col G in "SourceData". The macro should run until it gets to a blank row in Col A.
Here is an example to help illustrate the logic:
E1 = Ethics101
A2 = 123456
A3 = 654321
If Col G in "SourceData" contains "Ethics101-123456", E2 = "Complete", if not "Incomplete"
If Col G in "SourceData" contains "Ethics101-654321", E3 = "Complete", if not "Incomplete"
...and so on until it gets to a blank row in Col A...
I am trying to get Excel to search A1 and B1 for specific information and return a result in C1.
E.g. If A1 contains the text "Jumping Castle"
and
If B1 contains the text "4"
Then
C1 will produce text of "170"
But the formula must also detect and return a different amount if the information in B1 changes.
E.g. If A1 contains the text "Jumping Castle"
and
If B1 contains the text "7"
Then
C1 will produce text of "250"
I wish to copy a merged cell (3 cells) based on if only 1 of 3 cells to the right contain "X". if the top cell does not contain "X" than the merged cell is not copied. Also, is therea more elegant to copy 3 columns at a time rather than do one at a time as my code shows:
Sub CopyICUCAPU()
'
' CopyICUCAPU Macro
'
Dim i As Integer
[Code].....
Trying to put together a macro that looks down active sheet for all cells that contain a value, sets a print area and then prints !
Is this possible?
FYG, I have a column that run from 3 - 2000, which contains a formula, which may produce a value depending on corresponding cells.
I used this code from a post on a similar topic, but excel is complaining code
in bold
Private Sub Print_Area_Click()
Dim lastCell As Range
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0)
Do Until Application.Count(lastCell.EntireRow) 0
Set lastCell = lastCell.Offset(-1, 0)
Loop
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address
End Sub
Conditional formatting. I want to change the color of a group of cells based on data in another group of cells. Example:
If cells G8 and G9 (which are merged) are between 80% and 94%, then I9,I10,I11 (which are merged) will turn Yellow. Also, under the same scenario, IF G8 and G9 is greater than 94%, then cells I9, I10, I11 will turn Red.
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
I am using Excel 2010 and I am trying to average the amount of days in a month to a daily average per person in my worksheet.
Total sales per person
A5 = 10 - This is the Grand total per person for column A
A6 =4
A7=6
Daily average per person
C5=2.6 - Average for all persons here
C6=2.0
C7=3.0
The formula I am using is:
=(SUMPRODUCT($A$6:$A$15,C6:C15))/$A5
Which gives me an answer of 2.6 in cell C5 as shown above which is what I am wanting.
Please note that my cell range for my staff goes from 6-15 for both Column A and C where the other cells are blank in both columns.
My question is, If I was to clear all the data in both Columns A6:A15 and C:6:C15, cell C5 would return to a #VALUE. How to I change the formula so that if the cells were Blank, cell C5 would also be blank until I enter data for each person again?
This is a project plan with tasks and dates. Column A is the activity number. (Example 1, 2, 3" etc). Column B is the task (Ex. "Complete Report"). Column C is number of days required to complete the task. Column D is the dependency column. (Ex. Cell D2 =1 in other words Task 2 is dependent on task 1). Column E is the date.
I would like to have a seperate start date cell and a go live date cell.
The objective is to enter a start date, and have each column E date increase based on the number of days entered in Column C. If a task is dependent on another and I change the number of days in Column C I need the dependent task to change the same amount of days.
Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g.
AAA blue
BBB orange
AAA round
CCC smelly
AAA elongated
Worksheet 2 I want to show:
A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g.
AAA blue round elongated
BBB orange
CCC smelly
When the sum of B7-D7 is less than cell (I7) I would like to change the shade of B7-D7 to orange to indicate that larger values need to be entered to equal the value of cell I7. And when the sum of these three cells does equal I7 their color should change to green. I recorded two macros to change the colors and I've run them to verify they work. But I've got something off in my simple macro below.
[Code] .....
I need a macro to highlight a cell based on other cells.
For example:-
1. I wants to highlight A3 in red if all the cells from I3 to Y3 are empty.
2. I wants to highlight A3 in yellow if I3 is filled and J3 is empty
3. I wants to highlight A3 in green if if I3 is filled and J3 is also filled.
Actually I am having a grid which shows all the components of watch like KIt, case, dial , strap,hands.
The A row is purchase order no and then I3 and J3 belongs to KIt means if a po is not then I3 and J3 are empty so A3 is high Lighted in REd and if a po is placed then we put supplier name in I3 and it changes the color to yellow and when a po is confirmed we put confirmation date in J3 so A3 turns to green.
So in this way we can see if any Po is not yet placed or not yet confirmed based on cell color.
I build a lot of spreadsheets at work that I email out and the person I send them too has to enter information in the cells highlighted in yellow. Since not many of the end users are too computer savvy I lock and protect all the cells that they shouldn't be changing.
This can take forever, is there any way to have all the cells in a worksheet become locked and protected based on the formatting they have? All cells that aren't yellow?
I have two columns, A and B (I technically have a lot more in my sheet but to make it simple just focus on these two columns). The two columns are Company Name and then Company Color. I want to write a macro that based on what color is in Company Code (B1:B500) to highlight Company Name (Column A) based on those values in column B.
Below is an example:
I would want GE to be Red for example.
Company Name
Company Color
Google
White
GE
[Code]...
Column DColumn EColumn FTarget %DifferenceCash4%5.12%Large-Cap8.5%1.73%Commodities4%-1%
I need to color code Col F based on the following criteria:
If F2/E2>20%, then shade pink else no color. I only care about the absolute values. The 20% number can change depending on the category. So for commodities, the limitation is 30%. Like that I have a list of about 20 asset classes, 14 of which have the 20% constraint, rest are either 25% or 30%. I would like the cell color to be updated each time I import a file. Also I have other conditional formatting on Col F (like red color if negative number) so I don't know if another conditional format will work on the same cells?
I want a code to Hide the row if "sum of numbers in columns three thru last column" = 0. Means, if the sum of all the cell (except first and second cells in that row) in a row is zero, then that row should be hidden.
View 9 Replies View Related