Data Table: Incorrect Results Inside Table
Feb 22, 2007
I did a data table yesterday and it worked. I tried again today and the results are incorrect. They are coming out as a constant (the same result as the original formula). Has anyone had this happen before and figured out how to fix? My spreadsheet is fairly complex. Does the data table formula need to refer to the "base" cells? For example, if the formula refers to cell F15, but in F15 the formula is +C15, does my data table formula need to use C15?
View 3 Replies
ADVERTISEMENT
Jul 24, 2008
I have made a pivot table and I dlike to identify with a macro the documents with net value over 1000. Then extract these values next to the respective sales documents in an are near the pivot table somewhere. The fields are called Document and Sum of Net value. Of course the pivot is very variable one time it has 3000 records and another 5000.
View 9 Replies
View Related
May 23, 2013
I have a workbook with two worksheets,
sheet 1 contains student data, name dob, actual age, raw score and an empty column standardized score
sheet 2 contains a conversion table, using the actual age from sheet one you locate the age in the top row of table in sheet 2, and using the raw score from sheet 1 you locate the matching raw score from the first column of the table in sheet 2, going down and acrosss until you meet this gives you a standardised score
i want the standardized column in sheet one to fill by using a formula which looks at the table in sheet two locates the two values and returns the result.
View 5 Replies
View Related
Nov 17, 2006
See the attached:
1/ Need to be able to populate a table with the following data (or as illustrated in ' Sheet 1'.
Company Name, Year, Manufacturing, Non-Manufacturing
This data will need to come from the lists for each of the variables as opposed to from the table, as this will be hidden from the user. Also, more company details will be added over time.
View 4 Replies
View Related
May 19, 2008
I have used a pivot table with help from members here for a rota.
Now i am wondering if i can add a column to the original data which is hidden to times the number of hours worked by the hourly rate which is in another sheet.
The pivot table will be in an admin sheet with protected access so employees cant see it.
View 9 Replies
View Related
Apr 8, 2009
I have a table of values that are computed using input values from the first column (y's) and first row (x's). Each value in the table (z's) has a unique set of x and y.
In order to plot z vs x and z vs y I need to create a list of unique x,z and another of y,z. My question is how most efficiently to thus turn this table "inside out." The table will be very large, or I would do it by hand.
View 9 Replies
View Related
Aug 26, 2009
I have a long list of delegates attending functions on different dates and need help with 2 problems:
A) List has filters by date/venue etc but I will want the table to be visible even when list is filtered.
B) I am using the following formula to count "=SUMPRODUCT(--($E$3:$E$728="Thursday 3rd September 2009"),SUBTOTAL(3,OFFSET($E$3,ROW($E$3:$E728)-MIN(ROW($E$3:$E$728)),,1)))" and this works but if I filter to another date then 3rd September shows 0.
View 6 Replies
View Related
Sep 16, 2012
I'm working on a spreadsheet to anticipate the corrosion rates on multiple equipments, based on 4 criterias. At the moment, we type them down one by one and we search for the corresponding value inside the table we have.
Temperature
CS (carbon steel)
%Sulfur
TAN
[< 50-100]
[>100 - 150]
[>150 - 200]
[>200 - 250]
[code]....
These values aren't the real ones, but it looks like that. So, if I have a piece of equipment made of CS, I look at the specific table for CS (There are about 7 different groups of materials). I consider the %Sulfur, TAN# and the specific temperature. You also have to consider that this table is repeated with different outputs for higher level of %sulfur (>0.6, up to about 3%)
EX - Material = CS, %Sulfur = 0.4, TAN# = 0.7, Temp = 152, my value will be 9 (the bold numbers are the outputs). There is no linear relation or any sort of mathematical way to obtain the output, you have to look at the table.
In my mind, this part of my spreadsheet should look just like that :
Material
%Sulfur
TAN#
Temperature
CORR RATE
W
X
Y
Z
= Output
The excel part now, I tried working with IF() to specify a table to use as the TRUE value, trying to group multiple IF statement into one, but searching values inside multiple ranges is very tedious and I cannot cover all the possibilities... Then I tried working with INDEX() and MATCH() but again, these are not exact values, they are ranges, making the work even harder. Building an output with multiple criterias that are simple and exact is easy, I just can't seem to work it out with ranges.
View 9 Replies
View Related
Aug 4, 2008
trying to find a way to insert a table into an active chart area.
unlike inserting charts as objects inside of a spreadsheet, i am trying to insert a table of the spreadsheet into the chart window.
the purpose is so that i may have a numerical reference to my data.
usually i post questions to which i have begun work on - but i have had no luck even brainstorming how this would be done!
the ONLY solution i have surmised is to insert a table into a chart area as an Object(picture)
but how would one write a macro for making dynamic range screen shotpicture files?
View 4 Replies
View Related
Nov 23, 2012
I have made a pivot table which I use each month by just updating the source data, however I was trying to have a cell in another workbook say "=A31" but it keeps going "=getpivotdata(...specific name".
My usual practice was to copy the top ten rows of my pivot table into another file but I am trying to erase this step.
View 1 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
Mar 13, 2013
Table 1
January-12
February-12
March-12
Table 2
Sr. No
Name
Dep
Lates
CL / SL
AL
Lates
CL / SL
AL
Lates
CL / SL
AL
[Code].....
View 1 Replies
View Related
Jun 2, 2013
I created a lookup table that works quite well. It even has if statements in the LookUp Formula. However, I have to update the table it pulls the information from each day. I wind up recreating the range each time because the table always has more rows each time. Is there a way I could just paste the table in each day and not have to change the range? The columns never change.
View 9 Replies
View Related
Jul 15, 2014
I have a large table that I want to reconstruct. For simplicity sake, let's just says it's 3 rows (excluding headers) by 3 columns.
Item Description
1/1/2014
1/2/2014
1/3/2014
Cheese Burgers
2
3
4
Hot Dogs
5
12
6
Beverages
2
5
3
I want to reconstruct it so that the column headers become values in the table. The table headers are dates, in this case, if that gives clearer picture. So the new table would have 9 rows, (3 rows of data, excluding the header times four columns).
Item Description
Date
Quantity
Cheese Burgers
1/1/2014
2
Hot Dogs
1/1/2014
5
[Code] ....
The above example is sorted by date but I would be indifferent if it's sorted by the Item Description.
Is there an easy way to do this? Pivot possibly? Again, my data table is large: 36 rows x 181 columns. Using the copy/paste/transpose feature is pretty impractical.
View 3 Replies
View Related
Jul 14, 2014
I was wondering if there is any way possible to link your pivot table filters to filter the data the same way in the table that it comes from? So if i had date as one of my headers and i filtered the date to a specfifc date, is there a way to also filter the date in the data source sheet?
View 2 Replies
View Related
May 13, 2013
I have a table below that looks like this:
LOC
# of days
DTX
3
RTC
3
PHP
12
IOP
12
The # of days column will be a manual input. Then I have a larger table that will take those values and convert them to look like this: (Obviously the dollar values are pulled from somewhere else)
1
DTX
$ 1,292.00
2
DTX
$ 1,292.00
3
DTX
$ 1,292.00
[code]....
I've been thinking of trying to use a macro but not sure that is the best way. Using a button or something doesn't seem very elegant either.
View 7 Replies
View Related
Dec 14, 2006
i have a pivot table an extract of each data contained in this table.
[img]Count of NAMdate
SERVICENAM12-oct10-déc11-décGrand Total
Commercial-lauralaura11
Commercial-laura Totalgh11
custody-jonathanjonathan112
k11
custody-jonathan Totalgh1113
settlement-ludovicludovic11
settlement-ludovic Totalgh11
SPQC-elodieelodie112
SPQC-elodie Totalgh112
Grand Total1337
View 9 Replies
View Related
Jun 5, 2014
If a player does not qualify, their name and score should not be included in the final results.
I currently have their Name missing, but the NUMBER is showing up.
I've explained more in the attachment.
INDEXARRAY.xlsx
View 3 Replies
View Related
Nov 10, 2011
I have a problem with VLOOKUP function and I am looking for a workaround.
SheetA I have a column of business names and another column of the category.
SheetB has a list of credit card transactions and a column of categories - The results obtained from incorrect results from SheetA...
View 7 Replies
View Related
Apr 8, 2014
I would like to be able to bring these results into an output, but without other members of the other team members results been seen by other memebrs of the team
View 7 Replies
View Related
Nov 3, 2009
If I type in an age in A1, in A2 I want an age group to appear
the groups are
20-24
25-29
30-34
35-39
40-44
45-49
50-54
55-59
60-65
so far, if I type in an age then it normally enters the group ABOVE what I want
i.e. if I enter “24” then it put the group “25-29”. if I enter “42” it puts “45-49” and so on.
=LOOKUP($A1,{0,16,20,25,30,35,40,45,50,55,60},{"<16","20-24","25-29","30-34","35-39","40-44","45-49","50-54","55-59","60+"})
View 3 Replies
View Related
Aug 4, 2006
My goal is to sum up the total sales and total forecasted sales of each cost center. I compiled a formula that is supposed to do this just as I want it to. Simple enough. However, for some odd reason the formula returns either the totals of ALL cost centers or just a zero instead of the correct result. The weird part is that when I check the formula by pressing the "insert formula" button it suggests that the formula actually should produce the desired result.
View 5 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
Nov 5, 2008
I have attached the dataset. I need the data table to show the total numbers per year with the % change year on year, A column in this table of a year end projection figure for 08/09 and the % change. And finally add a line graph to show each complete year and the 08/09 projection as a comparator.
View 5 Replies
View Related
Jan 24, 2010
i have a workbook with 30+ sheets and i want to create a new sheet containing a table from aprox 6 values from the others.
i have done this in the past by typing =(then select the cell from the sheet i want)
while this works it is very time consuming and filling down after i have done 1 row gives the wrong results
View 8 Replies
View Related
Mar 3, 2014
In the attached file I have a pivot table created from the data in A:D.
By default the pivot table sums the values for each product (e.g. 18.00 for the sales of Product A). Instead of adding the values for each product I would like to get the pivot table to compound the values for each product. In columns J and K I have shown the results that I would expect when compounding the values of sales and profit for each product. Data for more dates will be added in A:D.
how to achieve this using a pivot table.
View 2 Replies
View Related