Generating Data Table On The Fly
Aug 1, 2008
I would like to know whether it is possible to use some VBA/Macro code to complete the following task: when the macro starts to run, it first create a data table, do all the calculations necessary and then paste all the end values on the data table itself and remove the data table it created. the reason why i asked that was because sometimes it takes forever for one of the excel-based programs that i am working on right now due to the fact that it takes a long time to compute the data table. i understand u can always set the calculations tab in excel to be automatic except for tables, but somehow this setting does not stay with the file and hence when i give the spreadsheet to the client, i will have to show them how to set the option, which is a hassle, also, i just want to give them the final values of the calculations and not let them see how those things are done.
View 3 Replies
ADVERTISEMENT
May 22, 2013
Is it possible to create a checklist (Yes, No, Maybe) with a pivot table? I need to manage a huge document with multiple subcategories, so using filters to display certain subcategories would be ideal.
Every time I generate a pivot table from my table, there is always a sub total, which is not needed (There are no absolute raw data values). Is there a way to remove this?
View 2 Replies
View Related
Aug 12, 2009
I'm knocking up a spreadsheet that deals with expense claims for a small company. Under the Reason column you pick an expense type from a drop down list. What I'd like to be able to do is to set up a graph or table that can identify how much is spent on the different things. As there is a drop down menu system I'd like the worksheet to be able to differentiate the expense types and generate a total balance for each. Enclosed is an example of the spreadsheet.
View 2 Replies
View Related
Oct 13, 2013
I was planning to use some combination of INDEX,MATCH and/or the SUMPRODUCT function to complete this task.
In the past I've used the SUMPRODUCT function creatively { ex. =SUMPRODUCT(--(repname=$C$2),--(question=C5),--(test=$E$3)) }
However, since the criteria I need to consider are not organized in individual records, the SUMPRODUCT function did not work.
See the attached work book for details on the test grade data.
Test Data Workbook.xlsx
The workbook also includes a mock-up of the summary and detail I'm trying to create.
My specific questions are on the "Questions" tab.
View 1 Replies
View Related
Jun 1, 2009
I have a table of data which represents data in different categories by week.
My ultimate goal is to have another table representing the data for each month - for instance - for each of the categories the data for :
06/04/2009
13/04/2009
20/04/2009
27/04/2009
is summed to make the data for the month of April.
The way I'm doing this at the moment is very long-winded
I'm using a whole new table - the size of the original weekly one - for each month. A calculation decides whether to effectively leave a cell blank or insert the appropriate data based on a date being within a particular range.
So in each "month table" there is the same list of week values:
06/04/2009
13/04/2009
20/04/2009
27/04/2009
04/05/2009
11/05/2009
etc.
but for each "month table" only the cells adjacent to the dates within the relevant month will return actual numerical values within them
This is an example of the forumula I am using in these tables:
=IF(AND(($AD7>=MIN($AE$5,$AE$6)),$AD7<=MAX($AE$5,$AE$6)),$C7,"NA()")
Then a master table sums the totals for each month.
I want to be able to keep this table but get rid of the ones for every month as the sheet is getting unwieldy!
I have tried several times to attach the sheet for clarity but each time upon trying to "Submit New Thread " I am getting page not found errors - the sheet is only 133KB and I have tried zipping and sending also - I can't make it any smaller.
View 14 Replies
View Related
Jan 6, 2009
use a vlookup with an IF function to display those unmatched record found in sheet1.
In sheet2, there are some new and old firms. Thus I need to separate them to process.
View 9 Replies
View Related
Jun 27, 2009
For Ex:- If there are three Items.. A B C and forming a group of 2
then the the total no of combinations would be permut(3,1) = 6...
I need this to be shown as follows depending on the no of itmes and no of group no=2 in this case...
Permuations & Combinations are as follows:
AB
AC
BC
CA
CB
BA
View 10 Replies
View Related
Jun 11, 2013
I am trying to create a pdf from a selection of cells. I tried using the macro recorder, and it only got some of what I needed.
select range b11:l44
save as pdf using above range
file location will be located in A1
file name will be located in d1
open pdf
View 5 Replies
View Related
Nov 21, 2006
im given an area and im asked to generate 10000 random numbers. how can i?
View 2 Replies
View Related
Feb 5, 2007
I have ten numbers (i.e. 1,2,3,4,5,6,7,8,9,10) and I'm trying to figure out how to make Excel generate a list of all the combinations possible for these 10 numbers. I'm trying to avoid using a VBA script. Is it possible to accomplish this task only using a function formula? This way I will have the ability to tweak the formula and test different qauntities of numbers (i.e. 1,2,3,4,5,6,7 or 44,56,77,33). Is getting a list of all possible combinations to output on a single column with commas between numbers possible with a formula?
View 5 Replies
View Related
Dec 12, 2013
So I created a formula to genterate from one program to the other. I am use to being able to click at drag the formula and have it create a new one such as 1,2,3,4,5. This formula is a little more complex and does not do that.
Here is the formula.
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]120913'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]121013'!$N$6
When I highlight and drag them I want it to create 121113,121213, and so on. It just keeps creating
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]120913'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]121013'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]120913'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]121013'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]120913'!$N$6
='C:Documents and Settingsjmor08jDesktop[DAILYLOGSHEETS.xlsx]121013'!$N$6
View 4 Replies
View Related
Oct 20, 2009
I have a spreadsheet of 1000+ companies and I want to take a random sampling of 100 of them. I would assume this could be accomplished by auto-filling a numerical value to each line of data (which I did), then scrambling only the column with the numbers (which I can't figure out), and then selecting the entire text and resorting it to put the numbers back in order and then just work with the first 100 rows (which I can do, if I knew how to make the numbers random).
View 10 Replies
View Related
Dec 5, 2012
If i had a template in excell would it be possible when it was opened it could generate a new number for the sheet in a cell for Ref ID.
View 3 Replies
View Related
Dec 5, 2007
I have made a daily planner for each month. The dates are on separate sheets which are titled 1st Dec 07, 2nd Dec 07 and so on.
This is also included on the actual sheet as well in the form of a heading. I want to know if it's possible to automatically rename the text on the sheet to what the sheet is called?
Or if the first sheet has a title of '1st Dec 07' how can I get the other sheets to automatically be titled in sequence so I don't have to do it manually?
View 14 Replies
View Related
May 15, 2012
I have a spreadsheet that has a command button which generates an email in outlook express, which until today was 100% fine no problems at all.
However I now need it to generate an email (with the email address and subhject line completed) but not to send it - I've tried everythign I can think of but with no joy
Code below:
Private Sub CommandButton6_Click()
If Range("I88") = 1 Then
MsgBox "You have not completed all the details.", vbCritical
Else
[Code]....
View 1 Replies
View Related
Jul 8, 2013
Let me explain, I am trying to auto generate a list. So I have one cell A1 with value "ABC"In another sheet,
I have a list of products in cells A:1 to A:100 and "ABC" is one of them, but is there 4 times. I have value in B:1 to B: 100
What I need is to create a list of all the values in Colum B that are related to "ABC"
Colum A Colum B
ABC 10
ABC 20
ABC Test
ABC TBD
Based on "ABC", I need a list to generate a list:
10
20
Test
TBD
I try Arrays and VLookup and Match and index but they are all single data return.
View 4 Replies
View Related
Jul 26, 2013
How to generate semi-random numbers? I have a small sample (26 points) that I would like to extrapolate out and plot on a skewed bell curve. The numbers represent a ratio which can never be less than one. The average of my sample is 1.0149 and the standard deviation is 0.01234. When I use the random number generator tool in the data analysis pack, it generates mostly good numbers, but it also produces numbers like 0.98 and such that are never possible in my data. I've made a plot with the random numbers, including the sub-one ones, and then just adjusted the x-axis so that they wouldn't show on the graph, but I know that that's not the correct way to do it. My graph isn't skewed as much as it should be.
View 1 Replies
View Related
Jul 10, 2007
with a code to generate new sheet name and running number.
For example the name can be INF001 then follow by INF002, INF003 .... each time I run the code.
View 9 Replies
View Related
Nov 10, 2008
I wanted to generate 11 random numbers between 1 and 10, which added up to 100 every time I pressed F9...
View 9 Replies
View Related
Jun 12, 2013
I have created a table in Excel 2010 (pls see attached table named post.xlsx).
Then copied the above table into PowerPoint 2010, using "paste link" (I tried to attach the PowerPoint file but the system says "invalid file type" and I cannot attach it).
Question:
I have received income data for another month - the new month is 13 and the corresponding new income is 100.
I typed 13 and 100 into the Excel table post.xlsx and thus extended the table by another column.
Then I went back to PowerPoint slide, then right clicked on the table there, then clicked "update link".
Specific Question:
The newly-typed column in Excel table is not get updated in PowerPoint table.
View 2 Replies
View Related
Feb 1, 2010
I have enable Refresh on Open for my excel pivot table, but user need to click "Enable Automatic Refresh" , only solution i came across is to change the registry setting. Which i dont have access to edit registry(admin disable the access).
Alternate solution i try to use Access macro to automate the process and use Outputto save it as a excel file A. Then use excel file B to update pivot table from excel file A.(as excel A data is always latest)
The problem is i will get "....A file name already exist...do you want to overwrite.." prompt.
Which defeat the automate process.
Any other solution to enable the automatic refresh on open the excel workbook?
Or Access can overwrite the exist file or save it as another file name with timestamp ?
View 14 Replies
View Related
Mar 14, 2014
In the attachment you will see an example of what I am trying to accomplish. What I am trying to do is find VBA code that is either specific to this worksheet or in a macro. When the sheet is opened I enter a date in B2. I then enter data into A7, B7, and C7. What I would like to happen is when the data is entered into A7, B7, and C7 the sheet goes and finds the same date that I entered in B2 and copies that data from A7, B7, and C7 into F7, G7, and H7.
View 2 Replies
View Related
Apr 14, 2009
I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields:
Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.
1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).
2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.
3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.
4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment
5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.
View 3 Replies
View Related
Sep 11, 2009
Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.
Table 1
Team 1Team 2Team 3Team 4Team 4Team 5Team 5ABABCity 12531642City 231173705City 367891125City 436251348
Table 2
City 4City 2City 1City 3Team 4BTeam 2Team 5ATeam 4ATeam 1Team 3Team 5B
View 2 Replies
View Related
Feb 10, 2009
Can anyone help, I'm trying to create some test data, eg 1000 rows in excel. In each cell a formula (?) returns some text such as Dog, Cat or Rabbit based on the probability Dog=0.5, Cat=0.3, Rabbit=0.2
I've come up with a messy solution of generating a random number between 1-100 and then using a lookup table where 1-50 = dog etc... it works but is long winded and difficult for other people to follow.
View 6 Replies
View Related
Jun 3, 2013
I am new at excel and have very limited experience. I have a employee training database, with employees (in column A2:A54) and the certification names (in row A1:AS1) that I must generate a monthly report on employees that are expiring within the current month. The cells are already conditionally formatted to change colors for one month prior to expiration date, current month of expiration date, and past the expiration date.
I am manually gathering the data then adding it to another sheet. In the mist of trying to gather data, I am missing and/or overlooking information. I wanted to know if there is a way to automatically generate a report based on the expiration dates that would pull the employees name and which certification is expiring or expired, preferable both. I have tried reading threads and applying advanced filters but due to the number of certifications it is not functional to do it this way.
I have attached a sample of the spreadsheet : ETD.xlsx
View 3 Replies
View Related
Aug 5, 2010
I am looking for code that can generate the report that a user selects.
For example the user clicks reports on the userform. The report userform opens up. The user checks the monthly report button and enters the last day of the month he wishes the report to be for. i.e 7/31/2010 and then clicks generate report. when executed excel creates a new workbook and creates two sheets. the first looks exactly like the master data sheet from the original worksheet but of course with the specified data. the second sheet will populate a summary of the data. I will address that after i figure out how to get the first sheet compiled.
View 1 Replies
View Related
Jul 2, 2008
I am using Excel to produce work orders and I need each one to have its own unique number. Much like an invoice or contract number, each time I open the file I would like Excel to generate a new number for that spreadsheet with a value of 1 more than the previous spreadsheet.
View 14 Replies
View Related
Jan 27, 2009
I have a spreadsheet with multiple departments on it set out in no particular order, (just as they are added) and this sheet needs to stay like that as a record of when added.
The problem I am having is that I need to generate a bordered blank Weekly, Monthly or 52 week planner for what ever department needs it on a different sheet so it may be printed. This will contain that departments items but leave the days/weeks blank for them to fill out.
As an example I use the code below to loop through the original sheet and generate a 52 Week Planner if the criteia matches.
View 3 Replies
View Related
May 14, 2013
Is there a way to generate a normal curve from just the statistical values (mean, std dev, etc)? I used the NORMDIST function but have to manually do the x values and it generates what I would descirbe as a "sample" curve. I would like to make one based on user input.
View 5 Replies
View Related
