I am trying to trigger a formula if a cell (B2) is populated (with anything) but if it is blank for the destination cell to remain blank.
The formula I am trying to trigger is =IF(G18=0,100,100-(100/(1+H18))).
If it can not locate the correct responce I want it the formula cell to be blank. How do I adjust my formula to do this?
This is the forumal I am currently using
=VLOOKUP(A26,Subs!A:F,5,0)
I have also tried this one as well
=IF(ISNA(VLOOKUP(A6,Subs!A:E,5,0))+(VLOOKUP(A6,Subs!A:E,5,0)=""),"",VLOOKUP(A6,Subs!A:E,5,0))
I have the code below that is two separate activities and I want to change the second activity from a cell trigger (Set KeyCells = Range("K42:AD42")) to a button trigger. I need to first to remain unchanged.
I'd be ok if this was just one macro that I could assign to a button but because its two and I need to write the second's to clicking a button I'm over my head.
Its occured to me while writing this that because it'll be a range of buttons I'll probably need to make each one an individual code? Is this the case? If so I may have to just keep this as it is.
I'm currently using the following formula to return either a "PAST DUE" or "DUE" value in a particular cell based on the date entered in the cell in the previous column.
=IF(AND(TODAY()>=BG43+5,TODAY()=BG43+10,"PAST DUE",""))
However, if the reference cell is blank the formula automatically triggers to put in "PAST DUE" as the value. I would like to modify this formula so if the reference cell is blank that this cell will also remain blank. I've tried a few different options, but I'm getting an "too many arguments" error on what I've tried.
I'm linking one sheet in a workbook to another. The formula is simple,
='Link 1'!E1
If there is data in the cell, it replicates it which is what I'm after. However if there is no data in the cell it returns a "0", not what I want at all.
I have a current formula using SUM formulas obviously in the answer cell the answer is returning 0 due to no data inputed, how do i make the cell show empty.
eg. =SUM(A1*A3) returns answer 0 need cell to be blank.
Referenced cells returning zeros: can these be made blank or string data?
But why I still get the value 0 instead of blank cell when I press ctrl+shift+enter?
My formula = IF(U15="Bank", PROPER(E15),"")
and E15 = VLOOKUP(B15,'Customer Data'!$D$6:$K$100,3,FALSE)
I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
View 8 Replies View RelatedI have one column that contains an If statement formula and would like the next column to then work off of the first column (i.e. if that 1st column returns a value then then adjacent column uses that result).
What is happening now is that it is returning #value (because I guess technically the cell isn't blank?)
All the answers I can find for this address either the auto-calculation setting or formatting the cell as a number. I am using this same formula in a bunch of cells to calculate totals: =SUM(E12:E17). It works find all over the sheet, except in one box. There is no difference in formatting or data type or anything.
Not working: notworkingexcel.png
Working: workexcel.png
The numbers being summed from the cells above the one in question are referenced from another sheet if that is relevant.
I am wondering if there is a way to return a cell reference by having excel
look at a table with certain parameters. For instance...
If there were a table with repeated dates such as:
Feb 12 1500 1530
Feb 12 1745 1750
Feb 13 1215 1245
Feb 13 1610 1700
Feb 13 1850 1900
Feb 14 1050 1140
Is there a way to return the cell number (ie A6) for the first instance of
Feb 14?
I would like to have the answer to a formula, say in cell G6, to show up in say G5. I notice in my college coarses, doing our homework through excel, the solutions are always in a cell around the entry cell.
View 9 Replies View RelatedThe formula should take the info from validation lists in R13 & S13, and match them up in F3:O3 (T1Q2) & C6:C15 (T2Q2) and return the data in the intersecting cell into R9 (Where the formula is kept). In this instance, it should find 1 at I3 and the 4 at C15 and return NN to box R9.
Is there a formula or combination of formulas that will return the word "Order" if the values in a column go above a certain threshold then start to decrease?
For example we have a list of data in a column that is imported each hour from an external database. Each new value is copied into the next cell down in column B. If the latest value was to go above, say, 100 is there a way of returning "Order" as soon as one of the subsequent values is lower than the previous one?
Below formula matches two criteria
=SUMPRODUCT(('[Value.xlsx]test!$B$5:$B$50=$B5)*('[Value.xlsx]test'!$C$5:$C$50=$C5)*('[Value,xlsx]test'!D$5:D$50)).
What it does is:
Matching B5 value in range B5:B10 of tab "test" in worksheet "Value"
Matching C5 value in range C5:C10 of tab "test" in worksheet "Value"
AND capturing the matching value in range D5:D50.
The problem is that i am getting 0 if the matching value is a blank or 0.
I want the formula to return nothing (i.e. blank) when the matching value is blank and 0 only when the matching value is actually 0.
I am trying to find a formula for a vlookup that will check one cell for any value but if that cell has no entry then it will check a different cell to return the value how do i do this?
TEAMCSS Wetherill ParkCT NAME
above is where i need it to look CSS wetherill Park is cell C1 the cell that has no entry is H1, both of these cells have validation lists in them,
OS windows XP excel 2007
My workbook is for financial planning but I'm attempting to streamline an input page (name, birthdate, etc) that will be referenced throughout the entire workbook to trigger automatic calculations (present value, education calculations, etc).
The cell in the input page is a birthdate - which when populated will trigger a cell on a different worksheet to calculate the respective age using this formula:
Code:
=IF(MONTH(TODAY())>MONTH('Input Page'!B30),YEAR(TODAY())-YEAR('Input Page'!B30), IF(AND(MONTH(TODAY())=MONTH('Input Page'!B30),DAY(TODAY())>=DAY('Input Page'!B30)),
YEAR(TODAY())-YEAR('Input Page'!B30),(YEAR(TODAY())-YEAR('Input Page'!B30))-1))
The problem is if there is nothing written in the birthdate cell then the age cell will automatically calculate 114 (reference photo below). Ideally I'd like that cell to be blank if nothing is in the birthdate cell on the input worksheet. I'm assuming since I'm using an IF formula to calculate the age already then I'll need to use a macro to to an "ignore".
[URL] ...........
I am using the following formula to return an "F" in the cell. If there is no "F" it just returns "#N/A". Is it possible to have the cell blank instead of the #N/A? If so how do I do it?
=INDEX(E1:E99,MATCH(1,(A1:A99=Selections!J5)*(E1:E99="F"),0))
and then ctrl + shift + enter
My formula is returning a 0 when true instead of being blank as specified. If I designate a value other than "blank" (i.e. "A Word") it still returns a 0. What am I doing wrong?
=IF(AND(CBAuditType="PHYSICAL",TxtInsuredName=""),"",TxtInsuredName)
I want to be able to return the value of the last non-blank cell in the horizontal range L3:BF3. This would need to account for any blank cells that might occur within the range. Those 'blank' cells would contain formulae and the non-blank cells would contain GCSE grades from A* down to U or National Curriculum levels in the format nx (e.g. 3a). I would need to be able to copy the formula down to other rows. The reason I'm not keen on an array formula is because they can slow things down, as I understand it.
I'm afraid I don't have a sample worksheet as this is all, for now at least, an emerging plan in my head. I should probably add that the grades will not necessarily increase in value in the range, so we're not looking for the largest.
I am trying to get data in excel sheet using web query from the following web link:
But I the data returned is a 'hypen' (i.e. a dash symbol). I am getting the row/column headers but not the figures or the numbers under them.
I have a range that is filled with formulae and the the number of rows in the range with data changes as the formulae reevaluate the inputs. So the rows with visible data changes between one row and 200 rows. By looking at the output in the first column of the range, Col M and finding the last row where the formulae returns data, I need a way of selecting across the columns of the range (M1 through to Qi) where i is the number of rows that have data in.
View 8 Replies View RelatedI have a set of data in a given range with one column indicating "priority" of 0, 1, 2, or 3 for each row. I would like to be able to allow users of the file to filter the data simply by changing the text of a given cell, or by choosing among filter options from a drop down. For example, I would label the control cell "show items up to priority:" and then let them enter 1, 2, or 3.
The "auto filter" option won't work because if they use the "sort" options in the drop down it will mess everything up. I would only want to use the auto-filter drop down if I could suppress the sorting options and the other filter options. (I'm after custom options of <4, <3, <2, <1).
Also, the advanced filter method doesn't seem to work well because users would apparently have to re-run the filter each time they want to change the criteria range value.
I've heard of macros that can be triggered based on changes to data in a given cell, but how is this done in this situation?
I guess programming a custom drop down box for selecting 0, 1, 2 or 3 would work just as well as having them type the number in. But I'm not sure how this could be done either.
I have a summary worksheet consisting of columns that are referenced to other sheets within the same workbook.
If the cell in the 'referring' (original) sheet is blank then the forumula returns a zero instead of a blank in my summary worksheet, which is messing up some other calculations.
Is there a way to make these cells truly blank, or possibly into string variables instead, so that they do not interfere with my calculations?
Drop down list containing 5 stages of a typical sales cycle, i.e. client engaged, negotiation, prospect, Short-listed, Win/Close.
These are locate in column B and each cell in column B has a drop down list with these 5 options.
Now, based on which option is selected the corresponding cells in column C and D need to change. This change is defined as follows -
Cells in column C, need to change colour ( i.e. blue, orange or red)
Cells in column D, need to change to a percentage value (i.e. at intervals of 10%, 10%,20%, 30% ...etc to 100%)
Column C (corresponding cell i.e. if B3, implies change to C3 respectively). In this case cell colour needs to change to one of three colours listed above.
i.e. if cell in B2 has client engaged, C2 must show blue, Similarly if B2 has short-listed, C2 must show red.
Column D, Equally, depending on colour in Column C, the percentage must automatically populate.
0-30% = Blue
31-50% = Orange
51-100% = Red
I have used conditional formatting options on a trial and error for testing the above, but not getting much progress. I suspect due to the multiple changes and different simultaneous changes i.e. colour and text, a Macro is most likely needed? (I have limited knowledge of VBA).
In closing there are about 200 rows that need to have this capability. I.e. Contents of column B trigger respective cells in Column C and D.
I have a single workbook with five spreadsheets. My goal is to move data along a path (or work flow) from one sheet to the next by using a "trigger" pulldown menu choice.
Sheet1 = Prospects
Sheet2 = New Sale
Sheet3 = Upgrade Sale
Sheet4 = Won
Sheet5 = Lost
Sheets 1, 2, and 3 use the same data layout for column A - K.
Sheets 4 and 5 have the same A - K as above and also have columns L - R in common.
The last column in sheet 1, 2 and 3 is a pulldown menu (New, Upgrade, Won, Lost).
Data rows on each sheet start at row 7.
The goal is to use the pulldown choice to remove the data from the current sheet (ex: Prospect) and add it to the next open row in the required sheet (ex: New or Upgrade). I also need to be able to make a similar move from New/Upgrade to Won or Lost.
I have completed an automated time sheet but have realised that there are some days where overtime entries make it go wrong.
There is one column where you enter an overtime shift when the cell has an entry I need all the other calculations to be zero
A1 START TIME
B1 END TIME
C1 HOURS WORKED ARE CALCULATED using a formula
D1 OVERTIME COLUMN ( IF AN ENTRY IS PUT IN HERE MANUALLY I WANT C1 ALWAYS BE 0
I tried various ways with isblank but just get true or false.
I have an application that updates the text string in A1 every x minutes (time may vary). I'm trying to develop a toutine that will trigger a Macro/call a function/subroutine once only on each change of data (and then not trigger again until the next change...and must only trigger on change of data in this specific cell). So the timeline looks like this
start A1 = "Apples", Fire macro once
start + 10mins A1 = "Oranges", Fire Macro Once again
start + 25mins A1 = "Pears", Fire Macro Once again
etc.
Triggering the macro is no problem but constraining to one triggering is proving a problem (for me anyway) and is really holding up my application progress.
Any way to trigger a macro in one cell whenever there's an input in another cell.
Attached is the example excel of what I want to achieve.
macro trigger.xlsx
Ok, basically C3 is a dollar amount. The default total for C3 is $0.00. If C3 is $0.00 then this formula cell will display a blank cell. If there is any other amount in C3 then the formula C3-C2 will run.
I tried this but it's not working:
=IF(C3="0","",C3-C2)