I need two formulas for my workbook. This is what the formula for Cell B6 needs to accomplish: I need Cell B6 to display what I have entered in Cell A6 but only if Cell A1 is greater than 1. This is what the formula for Cell C6 needs to accomplish: I need Cell C6 to display what I have entered in Cell A6 but only if Cell A1 is greater than 2.
View 2 Replies
I am an architect and recently I was required to do some extensive calculation relating to water requirements and toilet requirements for a building that i was designing. i created a excel file, with a basic vlookup function to put certain values in the cells that are pre-defined for a particular building typology (mostly created by governmental bodies as guidelines). for example, when i choose a particular type in column E, column F and G fills up automatically. now this table also gives me occupant load based on what is the area i enter in column D.
The problem is that I want to automate the calculation of toilet requirements. Now, each typology will have a different formula to calculate WC/Urinal/washbasin.
1. there will be 17 different formula - one for each typology (as shown in sheet 5)
2. Column j will calculate what is the number of WC required based on 2 criteria - first id what is the typology and then looking what is the number of occupant.
The problem here is for example, if in E9, selected typology as "offices", the formula of offices typology will be used in J9 and occupant value from H9 will be used but if i have E18 also selected as offices typology, the same formula will use occupant value mentioned in H18 instead of using H9.
Hence, excel will first have to look what is the typology selected. based on that, it will load formula - out of that 17 different formula - specific to that typology and finally it will refer to corresponding row for occupant load and will use it in the formula to get the result.
The worksheet perfectly performs as expected, big thanks to JBeaucaire again. Now, as am looking around of the file and doing some exploration on the array, as I was trying to make this applicable to real life application, I noticed the following mysteries...
1. When I tried to delete some rows, The Members Sheet output went wrong and displaying #N/A. I noticed it's because the formula range have change. But it would be hard for a new user to re-arrange it over and over again everytime when a row is deleted.
2. I've been trying to solve this problem to no avail. I found this link that gives me an idea how my worksheet should work, this was already solved but I can't apply it on my worksheet...
http://www.excelforum.com/excel-prog...nd-resort.html
3. I would like to add another column on A to put the 0 and 1 on, but I don't need to delete this column after the resort so that next time if I need to delete another row existing row i'll just replace the existing 0 with 1 on column A.
I am running excel 2003 and I am looking for a function that will display the filename or part of the file name in a cell. I know you can do this as part of a header or footer but is there anyway to have an individual cell display the file name of the document?
View 9 Replies View RelatedI'm new to using Macros and vba code in Excel and am getting problems in creating dynamic charts. Although I had seen all the posts of condional charts in search forum but no use.
I have an excel sheet with Date Received Column,Volume Unprocessed and the third column is to add 5 working days in the date received.I have to ask three questions.Please find enclosed the attachment.
1)In the attachement ,the excelsheet has 4 rows and chart for these values is displayed in chart1. But when new values are added up,it doesn't show those values in the chart automatically and I think I need macro for this.
2) I want macro to automatically calculate date by adding 5 working days in the date received for the rest of the rows.
3) If the current date is equal to date in the third column then the bar should be in orange colour. If the current date is greater than the date in 3rd column then the bar should be green colour and if its less then bar should be red.
I have to decide if the final answer will be yes or no based on a combination of 4-5 cell values from a single row.
I have attached the details in the excel sheet.
I am using Excel to tabulate votes for a contest. Judges have given a number to each entry, and but certain judges' opinions need to count twice as much as other judges' opinions based on their qualifications.
I've attached the file to help illustrate what I'm trying to do. Morris's votes need to count twice for all Photography or Web Design entries, and Clark's votes need to count twice for all Graphic Design or Web Design entries.
I know I can do this manually by simply copying the number into a blank cell in another column (like the blank column between Morris and Clark's names), but is there any way to make Excel do this for me?
Is there a way to apply conditional formatting to a range of cells that contains formulas?
View 6 Replies View RelatedI'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.
View 11 Replies View RelatedI am using =COUNTIF formulas to calculate different conditions. such as, calculating how much of one product do we have and how much is is received under 30 days. i want to combine those formulas in one cell to calculate two conditions in different columns.
1-So first i found out the date difference. I used =DATEDIF(Sheet1!C10,Sheet1!E10,"d")
2-Then I found out how many of one type do we carry, using the =COUNTIF.
=COUNTIF(Sheet1!J2:J3810,"DAZ")
3-That was easy, But now i have to sepereate them into 30 or less, 60 days or less, 90 days or less.
Then i used =COUNTIF(D2:D3810,"
I have a workbook with 2 worksheets. The first sheet provides a summary showing the last date a person attended a specific course.
The second worksheet is a list of all courses, attendees, dates, and status. I need a function that will match the attendee and course from the Master and display the date from the master in the corresponding cell on the summary sheet....
I'm having trouble with doing a similar thing with conditions/criteria based on arrays for different formulas (e.g. IF, COUNTIFS, SUMIFS).
Here's what i'm trying to condition and than use different formulas:
column A - Region
1. Europe
2. Europe
3. Asia
4. Asia
5. America
6. Asia
7. America
8. America
9. Europe
10. America
[Code] ....
QUESTIONS
1. (IF, AND ?)
For all cases listed in column A that have "university" in the column B, If there is at least one case having "Europe" write 1, if there isn't write 0.
The conditions are "Europe" in column A and "university" in column B.
If there are cases like that, I want formula to produce 1.
If there are no cases like that, I want formula to produce 0.
* In this example the formula would produce 1. for "Europe" and "university".
* If the formula was looking for "Oceania" in the column A with "university" in column B it would produce 0.
2. Using COUNTIF with multiple criteria.
Count all of the cases that have "Europe" in column A and "university" in the column B
The conditions are "Europe" in column A and "university" in column B.
If there are cases like that, I want formula to do count.
* In this example the formula would produce 2 for "Europe" and "university".
* If the formula was counting "Oceania" in the column A with "university" in column B it would produce 0.
3. Using COUNTIFS with multiple criteria.
Count all of the cases that have "Europe" in column A, "university" in the column B and "GER" in column C
The conditions are "Europe" in column A, "university" in column B and "GER" in column C.
* In this example the formula would produce 2 for the 3 conditions.
* If the formula was counting "Oceania" with "university" and "GER" it would produce 0.
I've spent a lot of time searching the Internet for this (e.g.how to use IF with an array, about different functions and formulas...) and found nothing that works.
For COUNTIF(S) they just didn't work, event with ctrl+shift+enter.
I want to display a message box to user upon opening a file. When the message box is displayed I want to display two buttons.
Button 1 = “Continue”
Button 2 = “Cancel”
If the user selects button 1 (“Continue”), I want the file to remain open and the message box will close.
If the user selects button 2 (“Cancel”), I want the file to close.
There is one condition that must be met for the “Continue” button. The message contains some disclaimer information. When the message box first appears on the screen, the “Continue” button is disabled. To make the “Continue” button active, I want to add a Check Box to the Message Box with a caption that reads “I agree to the Terms and Conditions”. Once the user places a checkmark in the Check Box, the “Continue” button will become active.
I am a little lost on this, and have tried to remedy my issue by searches to no avail. I have a cell formula that references another cell:
Cell A1 (=A3) where Cell A3 = 1
Cell A1 displays the text "=A3" and not the value of 1. Autocalc is on, and the worsheet and workbook is unprotected.
I am trying to display a formatted formula (combination of greek characters, superscripts, subscripts, bold, italics, etc...) based on a selected input. I have a selection cell with a drop down offering the choice of 4 difference scenarios. I want to display the resulting formatted formula depending on which scenario is selected (the formula is difference depending on the selection, as well as perform the calculation.
The calculation works fine, but I cannot figure out how to show the formatted formula. The selection works using IF statements, or VLOOKUP, but does not bring the formatting across. How can I do this?
I'm looking for an answer about how I can show the numbers underlying a
nested IF function. The formula works fine, but I need to be able to show
how I arrived at the numbers that I did. Here is an example of one of the formulas:
=IF(B9=1,(B4*Data!G5*Data!F5),IF(Calculations!B10=2,(Calculations!B5*Data!G5*Data!F5),IF(Calculations!B11=3,(Calculations!B6*Data!G5*Data!F5),0)))
Instead of the reference to the cell, I would like the formula that the cell represents. I know that I could go through individually and pull each piece apart with control F9, but since there are numerous cells and because the data changes every time I create a new budget, it would help if I could have some way to display it all with one action.
I'm working on a large set of data, so rather than read/writing lots of times to the sheet, I've followed the tip of reading a range to a variant array, processing the array, and then writing the whole thing back to the sheet (and it is indeed much much faster).
Dim vdata As Variant
vdata = Range("SummaryOutput")
'do a bunch of logic
Range("SummaryOutput") = vdata
To make things simpler (fewer ranges/variants to manage) I've defined a large range which includes non-data cells (blanks, labels, SUM formulas, etc..). The problem is when I read the range into variant array, it only copies the cells' values -- which means that once I write it back to the sheet, the formulas are lost.
So what I'm looking for is some way to read range into a variant whilst keeping the formulas (which I would see just being a string value in the variant array?). Does anyone know how this could be done?
My excel (2003) is stuck in array mode - is there any way to get it out of Array Mode and put it back in the letter:number display for formulas/functions? It's doing this in VBA too which is totally killing me.
View 2 Replies View RelatedI would like to highlight cells is two conditions are met:Cell = 0Offset(0,-1)>0I tried the conditional format wizard and entered a formula: =IF(AND($J2=0,$I2>0)) But I keep receiving formual errors, which I understand, because it appears to be incomplete formula. But I am not sure what else I need to add to the formula in the conditional format wizard
View 2 Replies View RelatedSo I have two sets of data, I've attached a worksheet which has them both. The datasets have different intervals, but I need to drag "Colour" from Data2 into Data1 and associate it with the correct interval. Sometimes the intervals match up and sometimes they don't - which could be a problem, but as long as its within a small spread it's fine. The "Colour" column in Data1 is filled with my desired result, the source being Data2.
ManyToMany_Example.xlsx
way to take 18 different workbooks, each containing the same 5 worksheets, and create a TOC that reference the workbook name only.
For Example, I would like to have one workbook with the first worksheet as an index or a table of contents that lists the name of 18 workbooks. When I hover my cursor over the name of a particular workbook in the index or TOC, it will open that workbook where I can now view 5 worksheets of that workbook.
Each workbook will contain the same 5 worksheets.
I am using excel 2002
I'm trying to make a spreadsheet for work so when im not there they can just enter the day for example sunday and what they have and then it will show them what they need to prep
View 4 Replies View RelatedI have a big list with names, money spent and date. I need somehow with a formula to do:
Take all the money spent on some specific day from a specific person an put it into a field.. and this for every date of the week and for everyname... Im gonna upload a photo.
Name
Money
Date
Daniel
5
01.03.2014
[Code] ........
I need the list of the left to produce me list of the right somehow, at least just the sum of everything spent everyday.
I need identifying correct title and corresponding company name. For this I need a UDF which looks for first occurrence of title having event or marketing or meeting word in data and if found the remove other titles and company names already separated with "/".
Sample Data
Producer, Target BTC@Periscope/Events Manager, Recognition Events@Minneapolis Park & Recreation Board/Event Producer@Events by JLS
Required Result
Events Manager, Recognition Events@Minneapolis Park & Recreation Board
Sample Data 2
Sales Associate@Teavana/Event Assistant@City of Saint Paul
Required Result 2
Event Assistant@City of Saint Paul
Sample Data 3
Sales and Catering Manager@Bunker Hills Golf Course at Town & Country Caterers/Marketing Coordinator@Town & Country Caterers
Required Result 3
Marketing Coordinator@Town & Country Caterers
I am trying to get a MsgBox to appear if the cell value within a given range exceeds a certain number value in my case "8". The only issue I am experiencing is that the code seems to take into account any input from any cell, not just the range specified, and additionally since there are 7 columns in the range the MSgBox pops up 7 times, and I need it only once and if the cell value exceeds "8" only when input.
ideally i would like to do a named range as there as there is a block of data where this will need to repeat.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
With ActiveSheet
For Each cell In Range("J40:P40")
If cell.Value [A35].Value Then
MsgBox "Red Cell cannot exceed 8 hours per shift. Please correct this."
End If
Else
End If
Next
End With
End Sub
See the screen shot I have attached: ramp rate.jpg
Basically I have to trawl through tonnes of data every day and I would like an automatic way to do it for me. I am calculating the ramp up rate on an Autoclave oven so these figures are temperatures.
I have various ranges for each section of a cure cycle so I will use this one example.....
I need to calcuate the ramp up rate between 115°C and 140°C
I need to find the min and max between 115°c and 140°c then count the cells within the min and max, minus the min from the max then divide by how many cells there are inbetween
(MAX-MIN)/CELL COUNT BETWEEN THE TWO
Is this possible?
When I click a command button an InputBox pops up asking me to enter a set of initials. When I enter the initials and click OK all of the rows with the initials in column 'H' are transfered to another worksheet. However if I was searching for say the initials 'AP' and I only entered 'A' and clicked OK, it will still transfer all the corresponding data with an 'A' in column 'H'.
In other words I want the data that is entered into the inputbox to be exact or nothing gets transfered.
I want to create a web address for the attached file and then post the link on my football forum so that members can view the file in their browsers. Is it possible to do this?
Attached File : Book1.xlsx‎
I have a macro on: Sheet101 (DAY 1) that adds additional sheets.
Example: When I click active button it will add Sheet102 (DAY 2) & can add up to Sheet130 (DAY 30)
I want a macro that will delete any of the sheets added from Sheet130 (DAY 30) to Sheet102 (DAY 2) But WON'T Delete Sheet101 (DAY 1)
Example:
If I have added 3 sheets I want it to delete those 3 Sheets:
Sheet104 (DAY 4) <--DELETE
Sheet103 (DAY 3) <--DELETE
Sheet102 (DAY 2) <--DELETE
Sheet101 (DAY 1) <--DON'T DELETE
& give a vbOkCancel MessageBox saying: Do you want to Delete Added sheets?
Okay the code below works, but one thing!
It deletes DAY 30 to DAY 20, then it skips DAY 19 to DAY 10, then Deletes DAY 9 to DAY 1.
So it's not deleting DAY 10 to DAY 19...
Do I need to add a wildcard?
Here is the Code: .....
Hi, I have this good working formula: ...
View 6 Replies View Related
