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).
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 this formula:
=DATEDIF(C3,I3,"MD")
However i need that formula to be blank if columns C and I are not filled out yet
I have a spreadsheet with 6 hidden columns (B to G). Rows 1 to 14 are frozen and have data that comes from another work book and these rows will be protected. The user will enter data from H14 to U14 and then H15 to U15 for the next row and so on down the sheet. The number of rows of data over a week is variable, a minimum of 21 rows (3 per day) but no maximum.
When the user starts entering new data in the column range H to U I would like to automatically populate the 6 hidden cells on that row with information from particular cells in protected rows 1 to 14. For example B14 would populate with the value from I4, C14 would populate with the value from I5, D14 from I6, E14 from I7 and so on whenever someone enters a value anywhere from H14 through to U14.
I can understand that a worksheet change event would be useful to do this but I guess then it wouldn't want to run every time each cell is populated so I think if it had to activate on a single cell change then the cell in column L would be best. Also, as I understand it, there can be only one block of code per sheet that operates on a worksheet change event, have I got that right? If that is the case then I assume all the code to populate the hidden cells on each row will need to be in this block of code.
I have data entered in rows 2:10. The amount of data is variable for each row. For example, in row 2, columns A:F are filled out, and the rest (G:P) are blank. In row 3, columns A:J are filled out and K:P are blank. The data is always entered from left to right, and to a maximum up to column P. Hope this makes sense.
In column Q, I want to write a formula that will tell me the last filled column of that row (A=1, B=2, C=3, etc.). So in Q2 would display 6. Row 3 would display 10. And so on.
I've attached a simple spread which gets across the main problem. i need the totals row (currently row 7) to drop down a cell each time the data base above has an extra row added to it its pretty much a cosmetic issue but it can be useful on hugely long spreads.
View 13 Replies View RelatedI want to create a room reservation sheet, based on the following:
December
Room 1
Room 2
08:00 am
09:00 am
10:00 am
Where the blank cells should be filled with a color (red, blue, anything), based on another sheet that tells me i.e that the room 1 is occupied from 09:00 to 10:00 and room 2 from 08:00 to 09:00 am.
How could i change this formula so
If the previous cell "filled with a number" in column D is equal to C17 Then C18 otherwise c17
This is the formula
This formula is in D38
=IF(DAY(A38)=DAY($C$13),$C$17,)
This formula is in D65
I also need to apply the same to this formula
=IF(MOD(ROW(A38)-MATCH($C$13,A:A),7)=1,$C$17,)
I'm an intermediate Excel user that can navigate around pretty well, but now my boss wants me manipulate Excel in such a way that I cannot figure out how to do.
He wants the worksheet to be "locked" so that I cannot select another or "the next" cell unless the active cell I'm in has something entered into it. The purpose of this is so that when a manager is buying off (proofing and approving) my work, he can't put his initials in a cell next to the purchased item unless the cell that decribes the purchaed item is filled in. I'm not exactly sure what terminology to use when describing this, so forgive me if I'm being vague.
Can I restrict a user from "moving on" to another cell unless the active cell is filled in? And if so, how do I do this?
Is there any formula or macro that can be used to detect the value of the last filled in cell in a column or row? For example, I fill in column B each day with a new numerical variable, so on day one B1 = 100, on day 2 B2 = 200, on day 3 B3 = 150 and so on. I would like the last variable entered in column B to appear in, for example, cell A1. Is there any way to do that?
View 9 Replies View RelatedI have written several pieces of VBA code which produce a sequence of tables on a single worksheet (with the rather original title "Tables"). The code often adds tables to the end of the current set of tables, and to do this, I need to know where the next available space is.
I have a solution which I have been using for ages now, which checks each cell in an appropriate column until a sequence of 3 blank cells has been found as I can guarentee that the tables are at most 2 cells apart. It then sets i=i-3 to give me the location of the first empty cell.
Blankcount = 0
i = 3
While Blankcount < 3
If Cells(i, 3) = "" Then
Blankcount = Blankcount + 1
Else
Blankcount = 0
End If
i = i + 1
Wend
i = i - 3
My is updated very frequently and i just wonder if it is possible to display the last filled cell of a certain column in another cell.
Imagine column B (B5:B1253) is filled with average sales values by using formula "B$5:B5" and i want to display the most uptodate average value in cell B1.
I need a formula or macro to fill a cell with the last entry in a given column.
Anyone have any advice?
Is it possible to display text based on the contents of another cell?
I am creating a report that includes all our data for the year per month - this builds dynamically every time i create the next months report.
So far i have data for,
Column A-- Column B
Jan - have data
Feb - have data
March - have data
April - no data yet...
In this example i would want the following text to appear in another cell,
Statistics: January - March 2007
It would dynamically update to January - April 2007... once April cell had data.
It's probably very easy but after several attempts i can't get it working as such.
I want a macro to be able to select & highlight (ready for copy/paste) all cells in the column range "A2" to the last populated cell in column A.
View 3 Replies View RelatedI am making a spreadsheet that will be used to track employee time usage. I would like to be able to create a graph of the time throughout the day that shows only whether or not the employee has entered an activity during each particular time slot.
EX: The graph shows the time from 8-10am in 30 minute increments. The employee has entered activities for the time slots from 8-9 and 9:30-10. When the graph is created, I would like to see times 8-9 and 9:30-10 filled (or, charted, or, however I should phrase it) and time 9-9:30 left blank (to represent that no work was done).
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.
Is it possible to have a cell, say B1 - which will return the Time and Date Value of when something is entered into Cell A1?
Example, I type "Hello" in Cell A1.
Now B1 will show me the Time and Date which i typed in Hello into A1.
I have a spreadsheet that has a bunch of empty cells that I need to fill in. Someone else needs to know which ones I have filled in after I am done. Is there a way that I can have Excel automatically highlight the cell after I put something in?
I know I could highlight the cell manually after I put something in it but if Excel can do this automatically for me, that would be the best as this is a very large spreadsheet and there will be many blanks to fill in. I am using Excel 2003.
how to autogenerate a date/time when another cell is filled in Excel?
View 2 Replies View RelatedI have a spreadsheet which has a sales report for 100 stores. When a store opens the spreadsheet they login using a username and password on a userform. This then filters the report and brings up there respective line. The stores have to fill in certain cells on there line. I want to display an alert if they forget to fill in a cell when they login.
View 2 Replies View RelatedI 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.
I'd like a code to automatically delete the value in column AK and its corespondent in AL, if the value in AK is filled in column C
View 7 Replies View RelatedI keep record of my blood sugar in excel 2003 that I send to my doctor weekly.
What I would like to do is, instead of typing the readings and mmol/L at the end in every cell, is to just type in the reading (example 5.5) then tab of the cell and have excel populate the cell with mmol/L automatically after the reading.
Can it be done?
So I have a table where column B contains a text entry, and column C returns a certain other text entry based on if certain keywords appear in the B entry. Say, if B8 contains "Fresh Apple Pie", column C8 could return "Tastes Delicious" by searching for "Apple" in B8. I've got it work just fine using a ridiculously long If(Isnumber(Search())) function, but the problem with this is that if I want to adjust the output, or add a new input rule I have to go into the formula and that just gets messy. Instead, I'd like to start an extra sheet, or a table to the side, where the user can write in column D what he's looking for, and then in E what the formula should return. Ie., if I wanted to change "Tastes Delicious" to "Getting Sick of It", I'd just have to change the E8 entry to that text, but leave D8 as "Apple". I've been playing with array formula's, but I can't get the formula to search past the first row of criteria.
Basically, I want something like this, where columns D and E are open ended for any user to add new search criteria or change the existing (for example, I could put "Gummy" in C4 and "Candy" in D4, and B3 would then return "Candy"):
Item
Output
Search For
Classify as
Candy Apple
Fruit
[Code] .....
I've been using this formula:
{=IF(ISNUMBER(SEARCH(C3:C4,B12)),D3:D4,"")}
(the cell references are different, but you can see the formula)
And it half works. If C3 is found in B12, it'll return D3, but if C3 isn't found I need it to look for C4, which it doesn't do. Naturally, the end formula will be a much larger range than two cells though.
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.
how 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 RelatedIs there anyway this can be done using a button in the spreadsheet?
For Example.
I send numerous almost identical forms to a colleague and to eliminate subject line typo's (Reference numbers) i would like the Subject line to match a cell in the document itself.
I have experience in working with Excel, but none on using code/macros
