How would I write a formula if I was trying to get the cell to count all expired certifications (based on a range of cells and their expiration dates) as well as all blank cells? This is what I have so far and all it's doing is displaying "FALSE"... =COUNTIF(D2:D95,"
View 5 RepliesVBA in Excel 2007. Essentially, what I am trying to do is this:
I have to two columns (A and B) that have a drop down list in each cell containing text options. I created the drop down lists using simple data validation. Let's say that A has the following options (East, West, North, South) and B has (Up, Down, Left, Right). For each combination of these, e.g., A1 = East and B1 = Down, I want C1 to pull a unique piece of data (a $ amount) from a separate worksheet. I can get this to work in its most basic form but I want to be able to be able to do this for cells in rows 1 - 300 without having to write separate lines of code for each.
Additionally, I am having trouble getting C1 to update automatically once A1 or B1 are changed.
I have a problem in applying Data Validation on cells containing Formula. Eg: cell A1 has a value 10 and cell B1 has a value 15. i have a formula "=A1+B1" in cell C1. If i apply a Data validation to the cell C1 so as to accept values only in the range of 0 to 10, the cell C1 is still accepting values grater than 10 when i change the values in the cells A1 and B1.
View 2 Replies View RelatedI want to take the value in I and apply a 10% discount to it and place the new value in column M. Would it be possible to have this new value rounded up or down to the nearest 9.
Example:
Column I=7.99
Column M=7.19
Column I=7.59
Column M=6.83 (however I would need it to be rounded to the nearest 9. The price I would need would be 6.79)
Column I=2.19
Column M=1.97 (however I would need it to be rounded to the nearest 9. The price I would need would be 1.99)
I'm trying to apply the code for accumulating value in a single cell to a range of cells.
I want to use the code found in this thread [URL] ..... but, it only lets me edit "B1" to another single cell. I need my cells G4 to G469 to operate the same way.
How to edit the code so I can include a range or show me a version of the code where I can enter my desired range?
The above formula is not working as in A9, I have a formula although there is no physical text in the cell as the criteria applied is false. However, B9 still appears.
=if(istext(a9),b9,"")
Is there a way to use multiple formulas for just one cell without damaging the others?
View 9 Replies View RelatedI have compiled a spreadsheet and it has columns for the days of the week, Mon through Fri. I have written a formula in R9 which will add a range of comments in the Recommended Actions column (R) depending on what is entered in I9:M9. So far so good
Unfortunately when I enter anything in I9, then enter something else in J9 the "Recommended Actions" just adds another comment after the previous comment in the same cell as the formula in there references I9 & J9 & K9 & L9 & M9.
Can you think of a way to set it up so that when I enter something in I9 then enter something else in J9 it changes the "Recommended Actions" from the old comment to the new comment or deletes the old comment before adding the new comment?
Formula:
=IF($I9="OK","Working ok",IF($I9="No Modulation","Check Profile and modulation",
IF($I9="FLD","Check pulse unit and meter operation",
IF($I9="No Data","Check Comms",IF($I9="Low Pressure","Check pressure on outlet",
IF($I9="High pressure","Check outlet Pressure",
[code].....
I have a list of numbers that I want to identify the highest and second highest numbers and create a mathematic result.
For example.
I lets say I have 15 stocks "B19:P19" I have X amount of money to invest "Q21" I want to invest 85% of Q21 in the highest of B19:P19 and 15% in the second highest. If there are 2 highest that are tied I want to split the 85% of Q21. SO far I have been able to identify the Highest and show it at 85% of Q21 with this =IF(B19=(MAX(B19:P19)),(Q21*0.85)) but I am not sure how to get the rest of it to do what I want.
I have a requirement to enter 25 formulas into 25 different cells. The formula is this:
[Code]......
The only thing that changes is the cell reference at the end which goes up in increments of 20,000, i.e. R20000, R40000, R60000, etc.
I have this set up and it works fine. My question is whether there is another quicker way rather than have the above code repeated 25 times?
I am creating a holiday chart for absence, I have put the following formula in one cell
=COUNTIF(F4:NF4,"H")+COUNTIF(F4:AJ4,"HD")
Now when these letters are typed in to the range they calculate "H" and "HD" to be 1 - is it possible to have "H"=1 and "HD"=0.5 to then give a total in the desired cell?
Basically "H" is 1 day holiday and "HD" is (0.5) half day holiday, I would like the total from a range to appear in one cell
I am working on a massive Pivot Table but in order to compile it I have to copy data from many worksheets into one. My problem is that each worksheet contains about 11,500 formulas with references to other workbooks and cells. However, none of these is locked for cell reference (meaning none has the $ sign for row or column) so copying and pasting obviously screws it up since the reference is changing but I need to have the links active in the pivot table.
It would obviously take me a year to add 440,000 individual $ signs. Is there any way, a trick of sorts, where I can just highlight the entire worksheet and add the $ sign to each and every cell reference automatically?
I would like to put the following two formulas in one cell, so if one condition is met, it returns 1:
Code:
=IF(IF((AND(MATRIX!C4=0.795),1,0)
Code:
=IF(IF((AND(MATRIX!C4>=-0.094,MATRIX!C4=0.795),1,0)
The formulas work fine as single formulas in one cell, but I can't figure out how to do both in one cell. I tried this but I get errors.
Code:
=IF(((AND(MATRIX!C4>=-0.094,MATRIX!C4=0.795),1,0,IF((AND(MATRIX!C4=0.795),1,0)
I've also tried using the OR function, get errors. I'm not sure which to use, IF or OR, and I can't get the syntax right on either one.
I actually have six formulas for each cell, but how to do two, then I'll know how to do all six.
I currently am using Data Validation drop-downs (which are identically referenced) in a number of cells (From J10 to J19). Each entry refers to a Crew departure &/or arrival time (based on an Aircraft schedule) and crew Subsistence & allowance ($17 per day). Referenced from “K10:K19” is USD currency:
DAYS:CURRENCY:
“J10:J19” “K10:K19”
“FULL-WEEK”(References 7 Days) = “USD 120.00”
“SATURDAY (DEPART)”(References 2 Days) = “USD 35.00”
“SATURDAY (ARRIVE)”(References 6 Days) = “USD 100.00”
“TUESDAY (DEPART)”(References 5 Days) = “USD 85.00”
“TUESDAY (ARRIVE)”(References 3 Days) = “USD 50.00”
I am trying to establish a way to specifically; select a particular day in the Data Validation drop-down menu (J10:J19) and a formula automatically converting the result to “USD Currency” for each of the 5 alternatives?
So in other words, each data validation cell will have 5 matching formulas pertinent to each specific orientation (Day)?
E.G.Select “FULL WEEK” from the drop-down option and “USD 120.00” is revealed / converted?
Select “SATURDAY (DEPART)” from the drop-down and “USD 35.00” is revealed?
... “SATURDAY (ARRIVE)” = “USD 100.00”
... “TUESDAY (DEPART)” = “USD 85.00”
... “TUESDAY (ARRIVE)” = “USD 50.00”
The closest I have managed (with no real success) is as per the following formula:
IF(T26="FULL_WEEK",X26,IF(T26="TUESDAY_(ARR)",X27,IF(T26="SATURDAY_(ARR)",X28,IF(T26="TUESDAY_(DEP)" ,X29,IF(T26="SATURDAY_(DEP)",X30)))))
I would be so incredibly grateful if somebody could help me (in laymen’s terms)?
I'm looking for a way for Excel to combine formulas together into a more compact form for me.
Attached is a very simple version of what I want, my actual formulas are very complex but I'd still like a way to have them automatically combined into a single cell.
Alternatively, what is a good way to break down a very complicated one-cell formula and still keep things organized?
Here is the one-cell version of a formula I work with.
[Code] .......
That formula is difficult to work with in one cell, so on another sheet I have it broken down into parts so that if one part isn't doing what I want I can change it. This takes up a LOT of space because of the other steps I do (the above formula is just one step) and I'd like to combine it together.
Attached File : Excel Formula Combination Question.xlsx‎
I am trying to make a button that will re-initialize the workbook: clear (delete) all unlocked cell's values. I have the following
View 5 Replies View RelatedIs there a way to unfix multiple cell references from formulas in a range of cells at once?
View 2 Replies View RelatedProblem-1) i want to round off the digit in the same cell i,e. Formula in F12 should be included in D12.
Problem-2) i have entered a sample account.in this i want to post the data entered in day book into their respective sheets of jaya and supervision automatically.
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
I cannot acheive to have a {Sum(if(...)} formula applied to whole columns. See below.
The following formula return a numeric value. So it works.
{=SUM(IF(Query!$L1:$L65535=TEXT($A37,"@"),Query!$AM1:$AM65535,))}
(confirmed with Ctrl+Shift+Enter)
The following formula returns #NUM. So it fails.
{=SUM(IF(Query!L:L=TEXT($A37,"@"),Query!AM:AM,))}
(confirmed with Ctrl+Shift+Enter)
how to use {Sum(if(... )} to whole columns?
I have a very simple workbook in Excel 2000 with one page containing all my data which I update on a daily basis. Some of the data is linked to another workbook so that it updates each time I open it. Then I have 12 tabs, one worksheet for each month of the year with 10 simple graphs on each worksheet.
As you can see, it is a very simple worksheet. Each month, I copy and paste the graphs from the previous month into the current month and simply alter the Source Data in the graph to gather the current month's data. At the start of this month, it allowed me to copy and paste, but when I tried to alter the Source Data to the current month, I got a window on screen with a yellow triangle containing an exclamation mark and the words "No new fonts may be applied to this worksheet". If I click the OK button or the cross, it responds but the whole programme is locked. The only way I can get out is to give the 3 fingered salute and close Excel down.
I have a workbook with 33 worksheets and more than 150 charts. Now, when I right-click on a chart to access "Source Data" for example, I get a message saying "No more new fonts may be applied in this workbook". Then I have to click "OK" about a dozen times before I get access to the dialog box. Curiously, it happens for some charts and not for some others.
View 7 Replies View RelatedIf filtering column C, just showing 'A's, by pressing the add row button, I want to copy the last cell in column C and pasting it in row 73, since it's the next unused cell when the worksheet is unfiltered.
View 2 Replies View RelatedI have a auto filtered applied to 1000 rows of data and the actual quantity of rows I currently see is 55 rows. When I do a filldown command to apply this formula, "=CONCATENATE(X$1,I51)" the result I get is wrong becuase where the "I51" value is at the formula is picking up cell values that are hidden due to the applied autofilter.
What do I need to add to this formula so that it only picks up cell values that are not suppressed by the autofilter? I want it to disregard the hidden rows when I do a filldown and only apply the formula to the rows that are not hidden due to the applied autofilter.
in this example below i need to work out what the divsor would be for values inbetween the data?
View 9 Replies View RelatedI want to have the macro delete the color applied once a new "Row" is selected!
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim RR As Range
Set RR = Target
Cells(RR.Row, 2).Interior.ColorIndex = 36
Cells(RR.Row, 3).Interior.ColorIndex = 36
Cells(RR.Row, 4).Interior.ColorIndex = 36
End Sub
I have a spreadsheet that I want to sum the data similar to SUMIF or SUBTOTAL after a filter is applied. So I have data in column "F" that I appliy a filter and based on that filter I want to sum only the filtered totals in column H. Is there an easy way or is creating a new function the only way to go?
View 9 Replies View RelatedI have the following code (below) which calls a function to return the sum of values in a specific part of the array.
I haven't set the function up correctly as it is causing an object not defined error(424), but I can't see where and have been going round the houses changing it. I'm wondering if it's my confusion over when to use By Val?
Sub getDataIntoArray()
Dim arrDataTot()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
ReDim arrDataTot(1 To 1)
I have a worksheet that I'm trying to rank program selling. The range has subtotals that I need to exclude, so I'm trying to rank over multiple ranges. I've thought about LARGE in an array function, but can't seem to make that work. Is SUMPRODUCT a possibility? I've attached the worksheet, trying to get the top programs (again excluding the subtotals in bold).
View 4 Replies View RelatedI can select an individual cell or range, select Format->Cells->" date" format, hit okay, and nothing actually changes unless I click on a cell so that it brings up the edit cursor. I can then hit enter (not changing the content of the cell in any way) and then Excel recognizes it as a date. The cell values are all in the standard "3/14/2001" format, with the exception that single digits are 0-padded, and there are no spaces preceding or following the date string. The issue here is that without the cells being recognized as actual dates, they cannot be properly sorted, eg: the original range:
01/04/2005
01/01/2005
01/02/2004
01/03/2005
is sorted, incorrectly, to:
01/01/2005
01/02/2004
01/03/2005
01/04/2005
...obviously for ascending order, the single 2004 date should be placed at top. Any ideas other than a custom date-text parsing macro? I could do that, but the size of each sheet, the number of sheets per batch, and the frequency of individual batches makes that idea completely impractical. ...Technically, my macro couldn't sort the above ranges correctly, but on further investigation, I noticed I couldn't do it manually either.