I have many formulas on my (inherited) spreadsheet in row 4 through 10000 Then in row 2 there are sumproduct and subtotal formulas
When I hide and show rows only the formulas in row 2 need to be recalculated. Yet, it recalculates ALL formulas
Is there any way to control which formulas get recalculated or is it locked to recalculate all of them
It really slows down the process
The idea is to select an item i one cell, which in return will affect the following cells with the nutritional values. So far so good. I would then like, if a given amount is entered, it calculates the values.
A food item is typed in Cell A. The following cells will Index Match a table (on a different sheet), importing calculation formulas based on the food items nutritional value (Cells C - G). If an amount is typed in Cell B (i.e. 100 grams) the following cells will then calculate the correct nutritional values.
i learned to make an INDEX MATCH function, so that when i type "Gulerod" or something like that, the following cells import information from the Table tab... When the table contains fixed values, this works just fine. However - i tried to make the table (the cells) with calculation formulas (i.e. =SUM(4*B3))... this means the values in the tables are 0 untill a value is typed in B3 (in this example).
... however when i do the Index Match in my main tab, only the "0"'s are imported, and no matter what value i enter in B3 in the main tab, the following cells remains with the fixed value 0.
... so my question is - is it even possible to have the Index Match function import the calculation formula from the table and have it work in the main tab as well? Or is there any other way to make this work in Excel? Basically - to have the main sheet find the relevant data to use for calculation on a different tab/sheet, and then do the actually calculations on the main tab/sheet.
I am looking for a solution which will highlight a cell if it has changed as a result of another cell changing (i.e. formula recalculate).
i.e.
A B C
3 2 (a*b)
I want cell c1 to be highlighted if either a1 or b1 are changed manually.
Is it possible to use the marquee scrolling text affect in a cell in excel?
I want to try and make my sheet a bit slicker and this would look great.
I have a rather simple problem (tough for me!). I am setting up a food log. I would like to have one column with common food items that I have in a drop down list. The list would be about 20+ things. The other columns would have relevant nutritional info such as calories, fat, carbs, etc.
The problem is how do I make the values in the other columns change depending on which food item I pick from the drop down list?
For example, if I choose bread from the list, how would I get the columns for carbs and calories to change accordingly.
The IF function would be too hard because I couldn't nest 20+ IF functions in the formula for each cell. Also, the drop down list would change as my diet changes, possibly reaching 50+ things.
Also, I would like to manually enter a serving size that would alter the nutritional values in each column (for example 0.5*Calories or Carbs).
It seems so simple in my head, but I can't figure out how to get it to work.
How do I get a drop down (gender list) to affect the data in another cell? ie when male is selected this automatically affects another cell - data 13.5 and when female is selected as gender the cell data is 12.5.
View 3 Replies View RelatedI'm working on a financial reporting project that should be in Access but unfortunately it must be in Excel. Some of the formula are complex and I have a UDF to calculate these values. I added the line
Application.Volatile
to each UDF but when I change the current month in a dropdown box, the UDF's do not recalculate. The dropdown box sets a period number on one of the worksheets - this same value is passed to each UDF. I tried using this code in my dropdown box :
Sub DropDown4_Change()
Application.CalculateFull
End Sub
but the PC just hangs. I have hundreds (more likely thousands) of formula in the spreadsheet and the recalc is recalcing everything whereas I just want it to recalc the UDF's. I even changed all of the sumproduct formulae to array sum if formulae which sped things up - that is until I forced the full recalc on the drop down change event.
So my question is: is it possible to just recalc the UDF's on 3 worksheets when the user selects a different period in a dropdown box?
And a supplementary question : if {sum(if(...))} formula are faster than sumproduct formula, would a (well written) UDF perform faster than a {sum(if(...))} formula?
My spreadsheet is very large & takes sometimes 5 to 10 seconds to recalculate. The problem is that is was wanting to recalc every time I edited a cell, which I do constantly all day long. Due to this, I have turned off automatic recalculation. Is there is a way I can use VBA to force just one cell or just one row to recalculate? Keep in mind that currently no cells recalculate until I hit F9 or go in & manually hit Calculate Sheet in tools > options > calculation.
View 6 Replies View RelatedTwo questions: is there a way to not have the formulas in a range of cells, say from B2:AX20, evaluated during normal recalculations? I would like the formulas evaluated only if a form button is pressed.
How do I link the recalculation of the disabled cells to the form button?
The range of cells do not require updating unless certain other cells on a different worksheet are changed which happens rarely. However, other cells in the workbook do change so I would like to keep recalculation set to automatic. I searched the site as well as C. Pearson’s site and some others and have not found a solution. Any help would be appreciated because at present my workbook is extremely slow.
I want to create a macro that changes the calculation when opening excel to calculating without updating tables. When I first open excel, the file takes a while to update because it is updating tables, and I want to prevent this. Any ideas? Also, I already have the following code, but this only changes the workbook once its already opened:
Private Sub Workbook_Open()
Application.Calculation = xlSemiautomatic
End Sub
I have just written a function that sums all the values in the cells in a range that are not green. This works however if one of the non green cells is changed to green the function does not work. i have to re input it into the cell that i put it into.
Function SumNotGreen(SelectedCells As Range)
' Adds the values of the cells where the font colour is not green(35).
Dim Cell As Object
Dim x As Double
x = 0
For Each Cell In SelectedCells
If Cell.Interior.ColorIndex <> 35 Then
x = x + Cell.Value
End If
Next Cell
SumNotGreen = x
End Function
How can i make the function recalculate i.e. go back into the loop every time a change is made.
I'm building a yearly workbook with multiple worksheets (i.e. one for each month, quarterly reports and one final yearly report). My question is that although I can have the data transfer easily to my quarterly and year end report sheets, I can't seem to figure out how to filter my data and then calculate an average based on personnel averages in the various categories I have set up.
My example is that I want to be able filter my personnel shifts (i.e. 7am, 3pm, 11pm, etc) and then set up a formula which can for example calculate a column of data and average it only based on the employees working that particular shift. My formula should adjust for the shift time I pick.
I have this sheet full of random data and I want to recalculate extra fast so I keep my finger on F9 which causes the random data to randomize really fast of course. Now, in B1:AT1 I have numbers that change with every recalculation but here is the problem. I want the recalculation to stop when excel identifies a zero in that range which doesn't happen often.
View 1 Replies View RelatedI have written a custom function which is called in lots of cells. It had an error so I modified it. But the modification did not automatically trigger recalculation in the cells where it is used. Neither did F9 (manual recalculation). The only thing that did is hitting F2 for the cell then ENTER. But what a pain to do that for every cell it's used.
View 5 Replies View RelatedI have a workbook with a button to get updated data. The button simply opens the other workbook and then closes it. THe problem is it opens the book and then closes it before the opened book has a chance to finish it calculation resulting in lots of #value errors. The book that is being opened works fine but because it is shared it can't be left open and needs to be closed asap, though it should be allowed to finsih calcs before it gets closed again. If anyone know a way to make the following temporarily halt until the opened book finishes calculating before it closes it.
Workbooks.Open Filename:= _
"X:gas daily pricingGD pricing weather call active summer.xls", ReadOnly:=True
ThisWorkbook. Saved = True
ActiveWindow.Close False
I have just set up a conditional format to change the colour of my cells in column F based on a yes or no value in column E. I have a colour function formula working to sum the totals of the cells coloured the 2 different colours seperately. Since applying the conditional format. the formula for colourfunction will only total 0.
Is there a way i can apply the colourfunction formula to cells coloured by using conditional formatting
how to extract data like filtering but with a formula.
A
B
C
D
E
1
Cat1
Cat2
Cat3
Cat4 (Cum of Cat3)
Return Cell
[code]....
I want to return data in Column E such that if Column A is Apple and Column B is Red, I want Column E to insert there "9", which is the last row matching this criteria (just once for Apples, Bananas etc.).
Notes: A Apples wont appear again in the coming rows.Reds can appear again in the coming rows.Column C is constant.Column d is the Cumulative.Normally what would you do manually is filter Apples and Reds and record the last value for Column D.
I'm trying to make a schedule of deposits made for the month of March 2008. (Deposits are made on a daily basis.) With this, I wanted to know when the deposit would clear with the bank using the WORKDAY formula given that I have to count 6 banking days after the date of deposit (Saturdays and Sundays excluded).
I'm using Mac and I recently upgraded to Office 2008 (never tried WORKDAY formula in other versions). I am aware of the syntax used for this formula and it works just fine in other cases. But I noticed that with start_dates falling on a Thursday or Friday of the week, Excel would give me a result date that falls on a Sunday, which is odd given that it's supposed to ignore non-working days or weekends. (I haven't even gotten to inputting holidays yet.)
I am looking for a function or a formula that performs the same process in Excel 2010 as:
Data Tab -> Sort & Filter -> Advanced -> *radio button* Copy to another location -> List range: A1:A100 -> Copy to: B:B -> *radio button* Unique Records only.
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
I am having an issue filtering data in a spreadsheet:
Each set of information has 5 lines of information (title, information, manager, status, sponsor) and the first column of each data set is a merged 5 row box with the area of operation (this does not have to be merged if this is creating the issue). I need to be able to filter by area of operation and have all 5 rows showing. Right now, Excel only shows me one row for each set of data with the area of operation as one row.
I have a spreadsheet with Row 1 as the header and Row 2 as sub-headers. I want to filter automatically to 2nd row, but whenever I press on Ctrl+Shift+L, I have the first column filtered. I have another spreadsheet that whenever I press Ctrl+Shift+L, it automatically filters the 2nd row instead. I'm not pretty sure what's the difference. I have my spreadsheet frozen on cell B3 (not sure if it has something to do with freezing cells. I know I can select 2nd row before pressing on Ctrl+Shift+L, but is there some other way to have this automated?
View 2 Replies View RelatedIf I give excel the advanced filter criteria <=2/15/2008 it seems to list all the dates prior to and on that date. Are there any pitfalls to this filtering technique (i.e. would any dates fall through the cracks)?
View 2 Replies View RelatedI would like to know whether it's possible to evaluate the percentage of a text value ie 'Approved', after filters on other columns have been done.
AREA YR DECISION
HN 09 APPROVED
NYM 08 REFUSED
RC 09 APPROVED
HN 09
RC 08 REFUSED
? %
So if I filter by AREA and/or YR, I would like the % of how many were 'Approved' in that filtered range (ignoring any blank cells in the DECISION column).
So far I have a basic
=TEXT(COUNTIF(C2:C6,"*Approved*")/ROWS(C2:C6),"0% ""Approval Rate""")
but it doesn't work once I filter by AREA or YR, and doesn't ignore blanks.
I'm quite newbie for VBA for excel, my friend asked me a favor to filter the data on his excel, so when he enter some keyword, the table would sorted (only show the row that have that keyword), and if possible that he enter it few keyword.
keyword example VB, oracle
table example ....
I have an excel with the following information
A1 - Is a dropdown which will have names of the systems (EAI,Mediation,CDF
..,All)
Row2 will be header which will have following data
B2 - Project Id
C2 - System ID
D2 - Project name
E2 - Project Phase
F2 - Status
from g2 to GN2 there are about 60 resources names entered
From C3 to F3 the data wil be entered and appropriately a chk mark will be
made to the corresponding resource wroking on that project
Say i have about 10 systems provided as dropdown for entering data in A1
(Ex: EAI, MEdiation, .....)
There are resources working for each system
when i apply filtering on a system name i shoud be able
to show only those resources working for that system and all others should be
hidden
Also under the "system" dropdown i will have an option "All", when "All"
option is selected i should be able to see all the data for all the systems.
EX: if i take system "EAI" and if the resources working for eai are there in
AO2 to AX2 then i want the resources from AX2 onwards and before AO2
not to be shown.
If i take a system "Mediation" and if its resources are from Z2 to AI2
then i want resources before Z2 and after AI2 notto be shown.
If "All" option is chosen then i should be able to see the whole sheet
with all the data for all the systems and all resources.
I have a filter that works in Excel 2007, and I'm trying to figureout how to make it work in Excel 2003. OrderNums is an array of multiple criteria. The below code works perfectly in Excel 2007.
View 3 Replies View RelatedI've filtered my data - such as selecting a date range - and then run the pivot, but the pivot ignores the filter and gives me data for all the dates. my workaround is to copy and paste the filtered data to a new sheet and run the pivot off that sheet. This works fine, but maybe I can avoid this step?
View 1 Replies View RelatedI have a worksheet with data in named tables on several sheets. Each of these tables has a column called "filter" (this is not always the same column number between sheets). The values in this column are either 0 or 1, depending on if that particular row is relevant under the book's current selections.
I'm trying to figure out the code that will take a table and filter it to show only the rows which have a 1 under the "filter" column.
I have added below code in module to get list of parts which are not delievered.
I am trying get code which will end the macro as soon as its do not find mentioned crieteria i.e.
("Not Delievered")
Selection.AutoFilter
ActiveSheet.Range("$B$1:$B$5").AutoFilter Field:=1, Criteria1:="Not Delievered"