I am wanting to have certain cells of data from one worksheet appear on another worksheet once the cells on the first sheet have data entered in them.
I find that I can do a simple formula to copy the cell but I want the cell to remain blank until data is added in the first sheet (it will be a mix of text and numerical entries).
I have a spreadsheet in which I want to track the date and time someone made an entry in an adjacent cell (so if someone enters something in B2, I want the date/time of that cell edit to appear in C2).
When the date/time has appeared, I want it to remain the same and not update when the worksheet is opened the next time. So I can't use NOW to pull it in, because that's going to change every time the file is opened.
I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this: =INT((D10-10)/2)
The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.
If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.
I think it has to do with using an IF statement followed with ""? Am I on the right track?
Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?
I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.
I have 2 similar question.
I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this:
=INT((D10-10)/2)
The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.
If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.
I think it has to do with using an IF statement followed with ""? Am I on the right track?
Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?
I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.
A1 has a date of 03/10/07
B2 has formula =A1 + 7
I want to copy that formula down the column B2 but if
B3 is empty I get 01/07/1900
How Do I make so that if there's no date entered in
A column I get blank cells but still retain formulas,
I've been running into this issue with other formulas but I couldn't find any solution searching this forum,
I'm trying to create a worksheet to automatically make a football league table update once results are entered. I have entered a formula using the "IF" function which compares scores and then awards points. However before a game is played and the cells are left blank, the formula detects this as a "draw" and awards a point. how to prevent the formula operating until a result is entered?
View 7 Replies View RelatedI am trying to work out some code that will allow me to search worksheet2 to find the last filled cell in column A.
I would then like to copy the contents of the cell.
Then past this data into the first empty cell in a column in worksheet2
I am looking for an IF statement that would leave a balance cell blank if both the revenue and expense cells are blank, otherwise a formula would be calculated.
View 8 Replies View Relatedhow I might 'trigger' a macro to run when a certain cell was filled with any value at all. I have the macro working fine (it's a simple copy and paste special) but I want it to run automatically when the cell A20 has a value in it.
View 9 Replies View RelatedI have a 'Total' Cell (I23) that has the following Formula: =IF(SUM(D23:H23)>0,SUM(D23:H23),"")
But I would only like the formula to 'work' if another "Total" cell (I57) further down the sheet is Blank.Therefore if Cell I57 has a value, the Cell I23 is left Blank.
I'm want this formula, =IF($C30="Y",$A29+1,""), to leave cell A29 blank if cell C30 doesn't have a "Y" in it. If cell C30 does have a "Y" in it then I want to add one day to cell A29. Cell A30 should return 28-Feb, but in stead it returns 0-Jan.
************************************************************************>Microsoft Excel - 2007log.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA26B26A27B27A28B28A29B29A30B30C30A31B31C31A32B32C32A33B33C33=
ABCD2624-FebSat**2725-FebSun**2826-FebMon**2927-FebTue**300-JanWedY*310-JanThuN*320-JanFriN*330-JanSatN*Feb*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I am counting days until a part is filled, and would like the unused lines to stay blank until a date is entered. below is a pic of my sheet.
I would like the 40034 colum to be blank (colum k) to be blank until there is a date input into colum b (date req)
My formula for colum k is =IF(ISBLANK(A4),TODAY()-B4,A4-B4).
Probably a simple method that i have not tried. I have a table where some cells are populated with #N/A. I am trying to work out a formula in another cell saying if the cell that contains #N/A then leave this cell blank.
View 4 Replies View RelatedEvery time I think I've got this thing beat, they throw another curve at me!
If cell Q19 is blank, leave target cell blank
If cell Q19 is not blank, return the value of cell E$4
Excel Version: 2000
Workbook has the possiblities of 366 tabs at the bottom, but for a normal calendar year will only have 26.
The tab name is the ending date of the pay period without the year. So for Feb 25, 2007 the tab would be named 0225
On a yearly calculations page I am simply trying to say IF TAB 0225 exists then cell equals '0225'!A2 otherwise the cell should be blank.
I tried this, but gets a reference failure instead of putting nothing...
=IF('0102'!A2=0,"",'0102'!A2)
In this example the possible tab name would be 0102. If the tab exists, everything works fine, but since in this case it doesn't, I get a reference failure. #REF!
I NEED the result to be empty if the tab doesn't exist.
Any idea would be helpful. I'd rather not have 366 tabs when only 26 are needed for any calendar year.
I have a spreadsheet called "temp.xls" that will change file name often.
This "temp.xls" has text that needs to be copyed to a new spreadsheet named "punchlist.xls" that does not change names. I know how to acheive this using record macro but I need the text to find the next row in the punchlist.xls file. Do all the macros run on the 1st file or do both files need macros?
cell A1 has the time (09:00), cell A2 has the minutes (60), cell A3 is the sum of A1+A2.
Im using this formula =A1+TIME(0,A2,0) - which is fine, except A1 is sometimes blank, so therefore I would like A3 to be blank.
I thought I could use this: =IF(A1,"","",(A1+TIME(0,A2,0)) But it doesn't work.
I am creating a worksheet for my buisness and I have input date of birth and have done a formula to work out ages but I dont know how to hide the formula and leave the cell blank so that when I create new information it automatically puts the age in.
View 1 Replies View RelatedI need to sum cells that contain this formula: =if($c$5=0,"",c5*b5). I am getting a wrong data type value error.
View 7 Replies View Related=IF(OR(A1>0,A2>0,A3>0),*2,"")
if A1,A2orA3 is greater than 0 then multpy by 2 or leave the cell blank
I have made a table which contains 2 date columns, Both formatted as date (dd-mmm-yy), the second column is calculated as the first column, + 30, The dates work out fine when adding to a date in the first columns, but from a blank cell it displays 30-Jan-00.
e.g
Started
Finishes
12-Nov-13
12-Dec-13
30-Jan-00
09-Nov-13
09-Dec-13
11-Nov-13
11-Dec-13
Rather than delete the rows with the blanks in the Started column, is there a formula that I can use so that the cells in the "Finishes" column is left blank when the "Started" cells are blank?
The current formula for cells in the "Finished" column is:
=[@Started]+30
I'm trying to set up my formula so that if there is no criteria match, rather than returning 0 it leaves the cell blank.
(I recommend skipping the below text and just opening the attached spreadsheet, instructions/what I'm looking for and trying to achieve and well laid out requirements and examples are in the spreadsheet and much more thoroughly explained then I can do below)
I'm setting up a table that grabs data from columns in a raw data spreadsheet.
For the purpose of the table I'm making, the formula needs to match up both the House, and the year/month from the raw data sheet, then if the two match and are side by side, return the value to the right either in the Plan Field or the actuals field depending on which field in the table the formula is in. If there is no entry or matching criteria for both the house and Month then it should leave the cell blank, not show a 0
So I have a SUMIFS formula that I'm trying to set up so that if there is no criteria match it leaves the cell blank.
So =SUMIFS(sum range, criteria& range, criteria&range), if no matching criteria can be found, leave cell blank, else if matching criteria found then return value from the sumrange.
This is the exact formula I am using:
=SUMIFS(INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B9, 'Raw Data'!$1:$1, 0)), INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$A$8, 'Raw Data'!$1:$1, 0)), Table!$A$9, INDEX('Raw Data'!$1:$1048576, 0, MATCH(Table!$B$8, 'Raw Data'!$1:$1, 0)), TEXT(Table!C$8, "yyyymm"))
Yeah kind of hard to follow, so attached is a very detailed and well laid out spreadsheet showing the example of what the formula is, how it's working/what it's returning and an example of what i want it to return/look like, so please check out the spreadsheet, it explains it a million times better.
Essentially though i want the formula set up so that if there is no criteria match, rather than returning 0 it leaves the cell blank.
I have 6 columns and would like to combine them. If the cell is blank the result should not leave a space.
- Prefix
- First Name
- Last Name
- Middle Initial
- Last Name
- Suffix
Example: Mr. Henry J. Weeks, III
Example: Henry Weeks
Example: Mr. Weeks
Example: Henry J. Weeks, III
how to write a formula that will leave a cell blank if nothing is entered. I do not want it to show a 0 unless the cell entered is a 0.
In cell G16 - I am adding U46 and U58 together.
I do not have a problem when it is 1 cell - my formula works fine. When I have 2 cells added together, the formula does not work.
I have another query that I believe can only be solved using VBA.
In a row of input data, some cells are duplicated from previous cells in the same row but in some instances, the user needs to be able to input different information.
e.g. M4=F4 if left blank and the same would apply to columns N=G, O=H, P=I & Q=J.
I have done some searching but nothing quite matches my requirements and recording a macro only produces the following result.
I am trying to create an conditional statement using this code
View 14 Replies View RelatedI would like the users to have the option of filling in the cells with "RED" by any means.
Here are the conditions or pseudo
If (any cell in Column X is RED)
Then (copy the row to Copy Rows Filled With Red)
If ( the cell in Column X is not longer RED)
Then (remove the row from Copy Rows Filled With Red)
In plain English, if the cell is filled with RED, it is copied over to sheet Copy Rows Filled With Red. Or if the cell is no longer filled with RED, it is removed from Copy Rows Filled With Red.
Please see attachment for explanation.
I did find information from other posts but with my mininal knowledge of VBA, I am not able to apply the macros for my need. I have an order form. Row 1 to 10 contain personal information. Column A is the "quantity" and Column E is the " total" with a formula. If cell of column E is filled (contain the $ amount), I want the whole row copied to another sheet. I also want row 1 to 10 to copy to the other sheet as well.
View 3 Replies View RelatedI am using the copy range command to copy cells from one worksheet to one that is generated on commandbutton click. Is there any way to 'paste' the cells in row 3 instead of row A?
The code i am using looks like this -
Worksheets("Products").Range("A" & i + 2).Resize(1, 6).Copy Range("C" & Rows.Count).End(xlUp).Offset(1)
This code works fine, but i want the data to start in row 5, not at the top.
Ive tried to use many of the post on this topic but I suppose I just dont understand how to stitch this into my actual code. Im extremely noob at VBA and the only way I can make macros is with the record tool.
My exact problem is I have a row of data in one workbook which is generated by a calculator built into it.
I then need the data from the output result table to be copied to another workbook in a table. I would then like the user to be able to run a different calculation on the calculator and have the results copy to the next row down as not to disturb the results of their first calculation. (I would like to assign this to a button next to the original result table)
Heres the code I recorded that just overwrites to the same spot. I was hoping I could just edit the paste location to make it skip down to the next available cell starting below the first (C25:F25).
I dont know how you guys make your code look all nice and indented either, so I apologize if its confusing I copied what the recording outputed....