In an excel spread sheet I need to look at say cell D4 which is a time, I need cell F4 to be able to tell if the time is before or after 07:00:00 and display 'A' for <07:00:00 and 'B 'if >07:00:00.
I have been trying to do this for ages with out look, thought I may be able to do with conditional formating but keep getting nothing.
Example:....
i m wanting Cell B1 to display true if any of the values in column C match otherwise I want to display false.
A 1
B 0
C 0
D 0
But I can't determine how to get this done.
I have attached the spreadsheet for reference. All other sheets pull information from the WHITE sheet.
I want the columns for unit price, discount and total to be set to two decimal places but I also don't want it to show anything if the field has a 0 value (so if i only have a few items I don't get lots of o's).
To get the field to not display a 0 if it is empty on the WHITE sheet I have been using "0;-0;;@" which I found on the net. If I use this then it negates the two decimal places.
This also includes the WHITE sheet.
I'm currently using an IFERROR, VLOOKUP formula to display an availability date for a product.
Atm, it reads some like this
I have 2 columns E and F that I am using, in Column E, I enter a time say 22:43:00, column F currently ads 15 minutes to what ever is in there with '=E3+TIME(0,15,0)' which is fine except if column E has no time in it when it automatically puts in 00:15:00, hov can I get the cell not to display anything in there is no value in column E.
View 2 Replies View RelatedI'm trying to create a completion statement on where I will add up the money my client has sent to me and also add up the money I need to pay out in order for them to complete their transaction. The end result I want is I would like to display whether my client has given me to much money and I need to repay them. Or if they haven't given me enough then I need to invoice them. I need excel to display for me after all the figures have been added and subtracted if the end figure is negative then display in D19 (balanced owed to you). If the end figure is positive (Balance required from you) display in D20.
View 12 Replies View Relatedhow to get excel to display 0 instead of this? It pops up everywhere through my spreadsheet but cant get it to display 0.
View 14 Replies View RelatedI have a whole number (207) in B5. This should be seen as a percentage (207%).
I want to add this percentage to the value in M15, which is not a percent (2705.8).
I would like to display this sum in M17.
The number that is displaying now, after simply putting 207% (I don't want the % symbol in there) in B5, and the formula =sum(M17,B5) in M17 is 270787%.
This is obviously not correct.
I've got results in row 3 - 16 386 and sometimes these results are exactly the same in some rows. What I now would need is row AB to display only one of each result and row AH to display how many percentages of the time this particular result appears.
View 14 Replies View RelatedI have a spreadsheet that lists users plus 4 columns of data horizontally and other data vertically. See image forum_sheet.png
My problem is the sheet is getting wider and wider as users are added. What I would like to be able to do is have a dropdown or something that allows me to select a users name which would then display their data set [ four columns ] on its own. I have tried validating a dropdown list but this only works on a single column.
See image forum_sheet_2.png
when I use the =sum command of a certain cell and that cell contains no data I am getting a $0.00. I understand why, but can I display just a blank cell if no data is found in the cell?
View 3 Replies View RelatedI don't know exactly what the function that I am after is, but, what I am trying to do is this:
- Search columns E2:A11 and sort from highest to lowest.
- Place results of the TEAM NAME (Columns A2:A11) representing the highest to lowest in column B16:B25.
Formula to display only if. i have this formula
View 5 Replies View RelatedI've seen a few finance excel sheets lately (don't have an example right now... ) that have a total number for something and then in parentheses next to it a percentage of the total is automatically calculated. So I have 5 pens and that's 10% of what I have in my pencil cup --> 5 (10%) is how it is displayed.
View 3 Replies View RelatedI have a set of numbers:
Example:
2
3
5
6
7
8
9
1
Is there a formula that can display the top 3 numbers in that data set?
Expected results:
9
8
7
Basically I have a =VLOOKUP(E2,Sheet3!$A$1:$C$7442,2,FALSE) And sometimes it doesnt find the number its looking up, so theres no data to display, so it just shows #N/A is there anyway I can make the =VLOOKUP(E2,Sheet3!$A$1:$C$7442,2,FALSE) display "not found" if it doesnt find anything?
also sometimes if it does find the number its looking up, and the cell next to it is blank, it just displays 0. I could filter it.. and then just replace all the 0 and #N/A with not found, but im just wondering if i could do it in the code too.
I am designing a template, where I will enter people’s birthdays.
What formula will return their age(s), last birthday:
•On 1st July each year.
•Continue displaying it, for the next 12 months.
•Repeat this, cyclically.
Is there a way to have a spreadsheet NOT display a #N/A, #NUM, #VALUE, #NAME error just because of an empty associated cell in the formula? I know I can do it with qualifiers (eg. if(true, then, else). But is there way to have the sheet just not display the error. I'm just trying to make the sheet look better without writing the qualifying formulas.
View 2 Replies View RelatedHow do i get a cell to display a zero instead of #DIV/0!.
View 7 Replies View RelatedI have a word document which displays the users name who is using the document int he form Surname, First name.
I was wondering if this is possible too do in excel? I can get usernames too be produced but not actual names.
I have the names of sales agents in C15:C17 (only 3 names) and their sales figures in J15:J17. In a separate cell I want to show the name of the salesperson with the highest amount of sales.
View 4 Replies View Relatedhow can i check the range value if i have the merge cells in the range? i want display it in the msgbox.
View 10 Replies View RelatedI'm trying to writ a small user form with a text box, buttom and image container. The idea is when I type a sign number and click the button the form add the sign number to the address path of the folder containing the images and set the image containers picture to the corisponding file .. this is what I've done so far but I cant get it to work -
View 2 Replies View RelatedI need to know how to display the value of a cell in another cell. This cell I need to display can change and I need the other cell to do so also.
View 2 Replies View RelatedColumn Q is a vlookup which looks up dates contained in column P within a range called "Query_From_Price_Database" and returns data from column 3 as indicated below:
=VLOOKUP($P4,Query_from_Price_Database,3)
What I'd like to achieve for each time a value is returned as being "0", is for the cell to display the most recent data.
E.g.The vlookup results might be:
27/04/10 15.29
26/04/10 15.55
25/04/10 14.98
24/04/10 0
23/04/10 15.13
22/04/10 0
21/04/10 0
20/04/10 0
19/04/10 15.24
I want it to be displayed thus:
27/04/10 15.29
26/04/10 15.55
25/04/10 14.98
24/04/10 15.13
23/04/10 15.13
22/04/10 15.24
21/04/10 15.24
20/04/10 15.24
19/04/10 15.24
Where there would be zeros, data is displayed from the most recent date actually containing non-zero data. Is this possible with a "not too complicated" formula?
I've tried a few methods, but they only seem to result in "formula too long" errors.
In a sheet I am showing a calendar where a user can choose Day, Month, Year and if he/she presses enter the data is submitted in Sheet 3. This is working fine for me.
In this sheet ( Sheet3) I have the column headings as Day, Month, Year, Date and Submitted By.
Now in the user form I have a command button as Show data (User form Name CmdShow) and Text Box (User Form Name TxtDateLeave) . which I want is that when someone will click on Show data text box will show the data last row value of column D from sheet. Column D of sheet 3 may be Date or Text format.
I have written code for that
Private Sub CmdShow_Click()
With Thisworkbook.Worksheets("Sheet3")
Me.TxtDateLeave.text = .Cells(.UsedRange.Rows.Count, 4).Value
End with
end sub
But this is not working. is there any other way?
Branch
Security Region
Average Score
101
1 London
4
102
5 Home Counties
5
[Code] .......
I have setup a dropdown list of different regions that cover our company and what I want to do is that when one of those regions is display only the rows from that region are displayed in the list either on the same or a new worksheet.
I know this can be done by adding a filter to the column in question but I am dealing with very basic PC users so to make their lives easier it would be great to use the drop down box option.
I'm using this custom format for my values:
0"%";-0;;@
The problem I'm running into is that although I want it to apply only to values
Each tab in my worksheet is represented by a customer's name. I would like to have inside this worksheet their name displayed.
I have a userform that has 6 textboxes that a user inputs quanities and then in the 7th textbox totals it up. See attached code works great.
What Im trying to do now is have the dollar amount display next to each textbox quanity, textbox quanity X cell value(B5) to give the user the dollar value of each textbox quanity. Then have a grand total at the bottom. Its just to show the user the total dollar value for the item.
Private Sub Accept_Click()
Call AllGood
End Sub
Private Sub cancel_Click()
Unload Me
End Sub
Private Sub TextBox1_Change()
Call totalTextBoxes
End Sub
Private Sub TextBox2_Change()
Call totalTextBoxes
End Sub...............