I have a file with numerous data tables that slows down the file significantly due to it's large size. I would like to set the calculation method to Manual, but also be able to manually turn the tables on and off so that they don't slow the file down when I hit F9 to calc. Is there a formula or method to accomplish this? BTW, I can't use VBA.
View 5 RepliesI have to use data tables to calculate Net Present Value (NPV) by altering specific variables.
How to use the data tables function under the what-if analysis.
My sheet and calculations are all set up
But for the life of me i cant figure out how im going to tell excel that im changing for example wage costs and not fixed assets when trying to asses the NPV.
Ive done it manually by altering the figures on the Inputs and Data Sheet.
My NPV calculation is on the Workings and calculations sheet.
The data tables i have done manually are on the Inputs and Data sheet.
why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?
View 1 Replies View RelatedI 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 saw a previous post regarding using "with (nolock)" in the FROM statement with each table to prevent SQL from locking the tables. I'm not sure exactly what's going on, but when I edited a query to add an extra criteria, the query is grinding the server practically to a halt (such power!!). Our IT guy tells me that my query is taxing the server. Maybe I'm not using the NOLOCK correctly?
Here is the query as I'm running it that runs slow. (The additional WHERE statement that I added that created the slow down is this one: ((cicmpy.AccountTypeCode='TSH') AND (oehdrhst_sql.ord_dt>={ts '2013-01-01 00:00:00'}))
Prior to adding this statement and the NOLOCK, the query runs in a flash.
Full query:
SELECT
oehdrhst_sql.ord_type,
oehdrhst_sql.bill_to_no,
cicmpy.AccountTypeCode,
[Code]....
I am needing to disable 5 worksheets from being calculated under the 'AutoCalculate' default option of excel. Currently, I set all 5 sheets' .enablecalculation property to false (see code example below), then save the workbook. This should prevent these 5 sheets from being calculated when the workbook is opened again - however these sheets are still being calculated with excels default 'Auto Calculate' setting once the workbook is reopened.
My issue:
While having 'Auto Calculate' enabled, how is a sheet disabled from being calculated upon opening?
Current method of disabling worksheet - does not work tho.
worksheets("Mysheet1").EnableCalculation = False
I'm trying to figure out whether or not something is possible in power pivot. I'd like to load two tables into powerpivot and then set up formulas that create calculated values across the two tables.
Here's a simplified description of what the tables look like. [URL] .........
The pivot table should join the two tables on Month and Category and allow me to create calculated values across the cost category, like this. Note it's filtered to a single cost category right now: [URL] .......
In this view it's filtered to all cost categories but i'd be looking to still maintains the right count of members (i.e. it doesn't inflate the numbers from duplicate records as you might get if you were to join the two tables in a sql database): [URL] ....
The actual tables I would use are quite a bit larger (too large for excel without pp), and have quite a few more of each type of category.
Would something like this be possible to do in powerpivot without having to do some data prep work in another app to join the two tables together?
i am trying to make a spreadsheet up which will calculate and sort out teams in a league table for my local football team. basically, i have around 6 teams in the league and they need to be sorted via: points, then goal difference, then goals scored and then by the match between the two teams if still equal. I have managed to work out how to get excel to calculate all of the numbers of wins and losses and goals scored, etc but cannot find how to rank the teams in order regarding how i just explained.
View 9 Replies View RelatedUsing Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
I want to use a Pivot table to filter data to show just the studies that contain patients from the 'South' area?
As per example below I want to be able to see all the patients in all areas but only for studies that have patients from the south. I put together an array formula that works well for small tables but is too much with one one my sheets that contains 200,000 rows.
Before filtering:
Study ID
Study Short Title
Study Patient ID
Area
1346
LLP
90126
Northwest
[code]....
When users paste data into cell , I need to protect data validation/ conditional formatting etc. How can one do this?
View 14 Replies View RelatedI am trying to secure a worksheet using data validation. I want to prevent users pasting over data validation fields, however I want to allow users to be able to still use the copy/paste feature as long as the data is valid.
Attached is a sample that I found on the forums. The current code does not allow any paste functionality.
Paste Restriction on Validation Cells.xlsm
I work in a correctional centre. Some of the correctional officers have
little training to use Excel or other programs, but are required to complete
spreadsheets on line. Although we have heavily protected the worksheets and
locked cells, we find that in some cases the staff can still mess up the
worksheets by copying and pasting data inappropriately.
Is there a way to turn off the ability to paste data into the worksheet, and
to force each field to be completed manually? This would eliminate the
problem.
I wish to prevent the use of the " ' " apostrophe in an entire column and I was wondering if this can be done with conditional formatting including a message of some type.
View 4 Replies View RelatedI have a worksheet that you can enter in
Forename, Surname, Area, D.O.B
This has a macro assigned that copies the data and pastes it into another worksheet at the next available row.
Now this works fine, but I want to be able to identify if the data already exists in the other worksheet then prevent it from being pasted and give an error message that the person already exists in the database.
Is this possible?
[code]...
Above is what I hav already, I was thinking it would help if each card had an additional field added that told you if the contents were valid. This could also say empty if all fields were blank. This field could for example use an if statement to check that you have entered a VS, a start date a project name. It could also check that if you have entered a completion date you also enter review loops. This could be in large red text so it is obvious to someone completing a card that they have not yet filled in all necessary data.
i have data in a cell that i must view in the sheet and in the print area
but do not want it to print
I currently have a monitoring spreadsheet which my staff enter data into each month. There have been some problems with staff entering data wrong so I have decided to build a userform to prevent errors. I have 2 combo boxes one for selecting a month of the year and another for a particular call the user must enter data for. I want the userform to enter the data from the userform into specific cells in a specific area. e.g. If a member of staff entered data into the userform for February, Call 2 then it would appear in the relevant boxes.
View 5 Replies View RelatedI am adding up the value of a range of cells in one cell. What I want is If the value of the cell which is adding up the values of the range of cells exceeds a particular number it should throw an error.
View 6 Replies View Relatedi want to import data from sheet2 a1 from sheet1 a1 continously. now problem is when iam importing i shouldnt loose my previous data. eg: at time 11 when i import data to sheet2 a1 from sheet1 a1 with value 9. and at time 12 when i import data to sheet2 a1 from sheet1 a1 with value 10. value 10 will be over wrting value 9. here i shouldnt loose value 9. i want values comming afterwards should come to next row.
View 3 Replies View RelatedUsing 2003 - Is it possible to prevent cell data entry automatically creating a hyperlink for email/web url's. Using
View 4 Replies View RelatedShould be simple but I can't get this to work,
I have used =value(a1) that prevents any formula, but it also prevents a zero being entered.
I have amended trying and/or statements in the validation but then it allows zeros and formulas!
I have about 10 pivot tables that only have one Pivot Field Item Selected (i.e., Jane Smith) with dates as column fields and a sum for data items. Everytime I refresh the pivot tables and there is a new person in the data tab (which is the data the pivot table is referenced to), the new name populates in the pivot table. I am sure I could include all of the names in one pivot table, but there is a long explanation why I only keep one name per pivot table in this particular workbook.
Attached is an example. As you can see, when you refresh the pivot table, the new name (Jason Smith) which I added to the data tab, will be included in the pivot table.
I am trying to figure out if there is way either with VBA or without VBA to prevent all of the pivot tables in the workbook to not select any new Pivot Field Items when I refresh the pivot tables.
On the uploaded workbook, Daily Nutrition Worksheet. How do I clear all the data to start fresh without deleting all the formula?
View 2 Replies View RelatedUsing a macro I want to paste from the clip board.
I can manually do this by opening the clipboard, selecting the data, which pastes to my specified location.
The macro copies to the clipboard okay, but loses the data that I want to paste because just before pasting I have to clear data which was previously been filtered - I have to do this at this point. The macro bogs down. If I end the macro there, I can manually open the clip board and choose the data I copied from there and paste it that way. I'd like the macro to do this for me.
I'm looking for a formula which will enable typing (in cell A1) positive numbers between 0-1000000 but will prevent the user from leaving that cell in an empty state. Empty cell means: cell with space(s) or deleting the legitimate value that is, already, in it).
I must emphasize that I know how to handle this by VBA code and/or by using a "named" pre-defined list of values + un-checking the "ignore empty cell" option - but I would like to know, once and for all, the solution WITHOUT using Macro and/or 'List' (if any..)
I tried that, without success:
=AND(A1>=0,A1<=100000,NOT(ISBLANK(A1))
Let's assume, starting at A1, that i have table (8c x 30r)
and lets assume that in each of the columns i have 5 cells filled in with data at random.
What is the best way for me to produce a list of the data in each of the columns without having to go in and delete out all the blanks myself? I.e. i want to reproduce the table but without any of the cells which do not have any data in.
I have been trying to find a way to consolidate data in two tables, but can't seem to locate a good response. I have been toying with EXACT, VLOOKUP, HLOOKUP, MATCH, and SUMPRODUCT, but none seem to work for my issue.
Two Files - Call them acrendo.xlsx and office.xlsx
Both have a single sheet and contain first name, last name, and date columns. Office also has other information that I'm trying to merge into the corresponding row of acrendo.
Basically, I am trying to take the combined first name, last name, and date column from a row in acrendo as a single record, look for an exact match in office, and if it finds one, return the corresponding values in Coumns D, E, F back to acrendo. Someone suggested using the following sumproduct formula:
=SUMPRODUCT(--(A2=[ofc.ally.xlsx]ofc.ally!$A$2:$A$9329),--(B2=[ofc.ally.xlsx]ofc.ally!$B$2:$B$9329),--(C2=[ofc.ally.xlsx]ofc.ally!$C$2:$C$9329),E:E)
This always returns a value of 0, even if there is data in the matching row to be copied over.
One small complicating matter is that there may be duplicate entries in office (i.e. one row in acrendo may match to four rows in office). I can cleanse that if necessary, or preferably, if multiple matches are found, it would simply insert the additional rows into the acrendo file.
Also, is there a way to go through the acrendo file and check for duplicate records and delete them (e.g. if there is the same last name, first name, and date, delete the duplicates).
Can't do this manually as I have acrendo has almost 18,000 rows and office has almost 10,000 rows.
I am trying to merge data from 2 tables into a master sheet. Column goes like this 1 = customer ID, 2 = allocated number, 3 = age
The first table has around 2000 entries and 2nd table has about 1000, and they both contain some overlapping entries. I wonder if there is a simple way to go around this rather than manually looking at each individual entries as I will be more tables to fit in later on.
I have recently discovered the "data table" tool on Excel and have found it to be extremely useful for sensitivity analysis. The Excel help describes how one can create 1 and 2 variable data tables. Now I would like to create a data table using three variables, however. (I would like to vary three different variables and see what effect they have on one formula.) Is this possible with Excel? Can pivot tables be used this way?
View 2 Replies View Related