Currently the cell C3 in the attached worksheet has a SumIF Formula to calculate all the targets from the Forecast Columns (E, H, K,.....).
I'd like to have a formula that would take only those Forecast numbers when the Actual columns (F, I, L, O....) are populated.
I have a cell with two Options - Applicable and Not Applicable. with the help of experts here in this forum I was able to hide the subsequent cells if the user chooses "Not Applicable".
However Now what I looking to do is - If the user chooses "Not Applicable" I want my subsequent cells to automatically change to NO.
Example : My C56 will have two Options - Applicable and Not Applicable
My C57 to C 70 will have a bunch of questions with two options - YES and NO.
If the user chooses "Not Applicable" in C56, I want the cells C57 to C70 automatically change to NO.
In the attached file I'd like to have a formula on cell B3 that would pick the number from the latest entry on "Actual" columns (column K, column M....). This should update the cell each month data is entered in respective month column.
View 4 Replies View RelatedI have an Excel file with multiple worksheets. 4 of these worksheets have the same format and have concatenate formulas in cells D14 through D28. What I need is an automatic pop-up message to tell me when I open the file if on any of these 4 worksheets text is generated from the concatenate formula that matches either "air cooling unit", "air heating unit", or "ceiling".
And, if so, I need for the pop-up to state the name of the Worksheet/tab and which of the above text strings match. For example, if one tab is named "6-5-09 C" and within the range of D14:D28 the text "ceiling" is listed, the pop-up should read something like, "Maintenance Needed for ceiling on 6-5-09 C!" If there are multiples on multiple worksheets, then I'd want all of them listed on the same pop-up. Then I would want to be able to click OK or CLOSE to close the pop-up. If I can even color the pop-up a color different than the regular gray, that'd be cool too.
I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
=IF(YEAR($A$3)<2014,"0",(IF(YEAR($A$3)>2014,H114,(IF(MONTH($A$3)<>2,H114,AA118)))))
A3 contains TODAY()
H114 is the cell being calculated
AA118 is the cell containing the calculated value for the current month.
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 Replies View RelatedI'm having trouble with the two formulas working in conjuction with each other.
Condition 1:
=(MOD(ROW(),2)=0)*(COUNTA(3:3))
Condition 2:
=TODAY()>=A1
"Condition 1" seems to override "Condition 2"? Ever see that?
So I have one sheet that needs to pull data through to another sheet (which is a stats summary)
I have a drop down list containing 4 options all of which have to be counted separately on the stats summary sheet. However I only need them counted when a value is input in another cell in that row.
For example: I select option 1 from down down menu, but I only want this to be counted on the stats page when I enter a date in the "date" cell.
Here's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
I need cell (O4) to display days overdue or days remaining on an assigned task based on subtracting due date (M4) from todays date, but only perform days subtraction function if (M4) is not blank and only if task complete cell (N4) is less than 100.
Have the conditional blank cell figured out, but nesting another condition for the less than 100 complete cell. So in other words, I don't want cell (O4) to subtract days and display any error, irrelevant data if there is not any due date entered or the task is entered as complete...
I have a table which holds scores as well as other data for players. Is it possible to automatically sort a table based on their points score to see who is top of the league?
View 9 Replies View RelatedI’m aware of the AutoRecovery, but I’m looking for an Autoback (Auto save) itself. Is there one in Excel? ( I'm using 2003)
View 2 Replies View RelatedI’m not even sure I can do this- but- I want to be able to have a few columns, 3 of them I want to be Item #, Item, Cost.
Is there a way to put the Item # (Number) in, then have the other 2 automatically come up?
I am inputing each day the total amount of sales. I am manually doing the averages for past 12 weeks on Monday sheet. I would like when I enter the amount for that week that the cells B84, C84, E84, F84 are automatically updated for the past 12 weeks. I have tried a few different approaches but I keep getting #ref or circular reference
View 10 Replies View RelatedI'm trying to create a bug reporting tool (with a bunch of text boxes and drop down lists) and have the following problems...
1. I would like to get a unique number inserted automatically in a textbox (it's supposed to be the bugs id (1001). How do I do this? And when I click OK after inserting all info I want this number to become +1 so the next defect can be added immediately.
2. Why are my drop down lists empty as default and their values only appear if I enter a value. Why aren't the lists displayed when i just click on them?
3. I have a multipel row text box. How do I get the text to jump to the next row automatically instead of using crtl + enter?
supposing i have column A which is vehicle number
and column b is fuel quantity
how to make in c automatic sum by vehicle number as i may have many entried in column A
example
vehno1 20
vehno1 21
vehno2 25.5
vehno2 40
vehno3 50
A1 contains "=2+3+4" which shows 8 as a result. I would like to add a word "Lbs" with it so it would display "8 Lbs" on the same cell. If I entered =1+2+3 on cell A6000, it should give me "6 Lbs".
View 7 Replies View RelatedI have a list of names in A:A how can I hide the rows that will start with the name (John)
A B
1 Tina 12345
2 Tina 5468
3 John 5566
4 Peter 444885
5 Tina 44653
so that way all I see is
A
1 Tina
2 Tina
5 Tina
formula for input values.I need to put in table_1 every to each fourth row each first row from table_2.
For example:
in table_1 A1 input A1 from table_2
in table_1 A5 input A2 from table_2
in table_1 A9 input A3 from table_2
in table_1 A13 input A4 from table_2
And so on.
I have data in table-1, in current shape
Table-1
B C D E F
Adda Zakeera 1239987801310037
Ahmed Pur East 5559998803310042
Ahmed Pur East-2 8888874805510041
Arifwala 3545555805510045
Bahawalnagar 3336666802610046
Bahawalnagar-2 1257777806610038
Bahawalpur 2206666804410044
Bhakkar 2223333805610042
I want to arrange my data like Table-2. I have tried vlookup() formula, it worked but every time I puldown the formula I have to manually increase cell # (e.g) In the row of Adda Zakeera, I have 4 values. When I use vlookup() and drag it downward the values should be shown as in Table-2 under Adda Zakeera.
=VLOOKUP($D$2, Sheet1!$B$4:$AF$90,2,0)
Table-2
D E F G H I J
Ahmed Pur EastAhmed Pur East-2 Adda ZakeeraBahawalnagarBahawalnagar-2BahawalpurBahawalpur-2
555 888 123 333 12548 2201#N/A
555 888 9987 333 77777 66666#N/A
I'm working on Excel sheet which cover period of time, I specify the period on the top, (cell E4= FROM : cell G4= TO) then I have to fill column (B) with the days name, and fill column (C) with the starting date from beginning till the end date.
i.e.:
E4= 24/06/2014 G4= 23/07/2014
B9= Tuesday C9= 24/06/2014
B10= Wednesday C10= 25/06/2014
I'm getting this work by a VBA code, and if it's possible to have the days names in Arabic language, and set the print area from (A1) till the (next) row after the last day in the column i.e cell (G39)
how to print automatically from Excel to a PDF file. After a couple of days of searching and tweaking, I've got what I think is a fairly good solution for automating your printing of worksheets to PDF files.
This solution requires the use of PDF995. This is a free utility available at www.PDF995.com. This is a print driver that allows you to print to a PDF file. The file is compatible with Adobe and can be read with the Adobe reader like any other PDF. The free version does pop-up some advertising with each print, but the automation works with around the pop-ups. I believe the full license version is about $10 and doesn't produce the pop-ups.
The challenge in automating a PDF process is that the PDF driver will prompt the user for a filename. This is ok if you are printing just one sheet, but if you need to automate the production of several PDFs, you need to be able to specify the name of the file in the code. The subroutine SheetToPDF presented below allows you to specify a single worksheet and the full filename for the PDF. I've included all the code and external declarations needed. The two subs at the bottom give examples of how to call SheetToPDF with the passed parameters.
'Needed to Read INI file settings
Declare Function GetPrivateProfileString Lib "kernel32" Alias _
"GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpDefault As String, _
ByVal lpReturnedString As String, ByVal nSize As Long, _
ByVal lpFileName As String) As Long
'Needed to Write INI file settings
Declare Function WritePrivateProfileString Lib "kernel32" Alias _
"WritePrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpString As Any, _...........................
Is it possible to set a formula or something up so that if a row contains certain information, then the row will turn a certain color? For instance, if cell B2 contain the number "3" and cell B5 contains the number "5", then row B will turn yellow. Then if cell C2 contains the number "4" and cell C5 contains the number "7", row C will turn blue. This is just an example. In this example, I'd like a formula all the way down column's 2 and 5, but only the rows would change color (not columns) according to their information.
View 9 Replies View Relatedregarding this article:
[url]
Is there a way to name a tab with some combination of 2 or more cells?
A1="first"
B1="last"
so, the tab name would be "firstlast".
also, can a space be inserted?...i.e. A1&" "&B2
I have a column with random figures numbering upto 2000 meaning 2000 cells starting from 1 - 2000 and this varies every time I update my sheet....
What is require is that in C5 i should be able to set a max limit of "reference cells", the purpose of this limit is that it would automatically go like a stop watch from 1 until the limit... So for example if I put in 500 in C5 it would reference going up from 1 until 500, I need the highest random figure within those range of cells referenced.
These max results are referenced in a mathematical alogrithm of which the calculated result must match the base level or get as close to it as possible...
I would like to create a pop up message that would automatically come up when someone opens one of my Excel workbooks. Essentially, I want to have some text regarding the confidentiality of the document, then require the user to click an "OK" button to continue.
View 9 Replies View RelatedIs it possible to have Column E automatically enter a value based on a 4 digit number inserted into Column C?
I have about 200 different 4 digit numbers that can be entered into Column C and 8 different corresponding values that need to appear in Col E... Would this be possible / difficult?
I am trying to create a web query that will automatically collect information from a dynamic web page. There are five tables on the web page that can appear. However, sometimes there are only 3 or 4 tables that appear.
The web page automatically updates every 60 seconds so I would like to have a web query check the site every 55 seconds.
I can do a query and get the information but I have to do it manually.
I am trying to create a program that when a cell changes to a specific text from a pulldown menu of two items the active sheet's tab changes color depending on what is selected. Is this possible using the pull down menu, and how would you go about doing this?
View 9 Replies View RelatedIs it possible to get a row highlighted just by placing your cursor on top of any cell along that particular row?
View 9 Replies View Related