Hi,
I saw EXCELISFUN TRICK 369. I need to do something similar. I tried to follow his code and couldn't. Then I tried copying it and growing his records and still couldn't get it to work.
What I am trying to do is on the first list use an inventory list. Which could be about 2000 - 5000 records or maybe double that (not sure how big of a list I could use in Excel). But lets say it is the max number (if someone could tell me that number I would be most appreciative).
I then will load a second list, or would load a list into second column. I then want the difference (what is missing) from the second list to appear in the third list. If it can give me the row of where it is in the first list that would be great (not a problem if you can't). I just don't know why the code from that video is not working any help would be greatly aprreciated?
Thanks,
Peter Fraga
(fragapete@hotmail.com)
Hi All,
I was setting up a spreadsheet that was based on the following vid:
http://www.youtube.com/user/ExcelIsF...14/tqCEY5YMyqw
Dynamic sub tables based on a master sheet array formula
The formula in question is:
=IF(ROWS(A$7:A7)>$E$1,"",INDEX('2010Corn'!A$4:A$17,SMALL(IF(Table2[From]=$B$1,ROW(Table2[From])-ROW('2010Corn'!$H$4)+1),ROWS(A$7:A7))))
B1 is the customer I'm looking for, E1 is the count for the customer and the master page is 2010Corn. I have 20 sheets looking to this master page for data. It works great, except for an issue when adding a new line in the master table.
What is happening is when I get to the end of a row, I tab to enter a new line in the table. It takes up to a minute for the cell to change color and for me to regain control of the computer.
I have run a performance trace and while the computer is locked, one of the CPU core's is pegged for the whole time with a processor que of up to 10 items at a time.
My question is...does anyone have any hints how to optimize this formula?
Thanks
Tony
Hi, this is a bit hard to explain so please bear with me.
I have a sheet with several information regarding customers and house sales. Each customer makes a monthly payment towards the payment of the house. I was asked to produce a weekly report that shows how much is the total of all the customers debt at that particular week.
In this sheet I have a column called 'current debt'. Every time a payment is done in column 'payment', the current debt is automatically updated in another column.
There is another column that contains the date of payment from where I can take the week number by using the weeknum formula.
Ex
Client Name________Payment_____Payment date___Current debt___Week
Client 1____________ 50.000______01-01-2008______150.000______1
Client 1____________ 50.000______01-02-2008______100.000______5
Client 1____________ 50.000______01-03-2008______ 50.000______9
Client 1____________ 10.000______01-04-2008_______40.000______13
Client 1____________ 10.000______01-05-2008_______30.000______17
This created 2 problems as I want to creatr a pivot tabe to display all weeks of the year, consecutivelly,even if there are no payments in a specific week.
I already solved the problem of not having any payments in a specific week of the year by adding a dummy client that contains dummy data for all weeks of 2008.
My problem is the following:
Lets imagine that I create the Pivot table with just the information above and the dummy client up to week17 (all zero in payments and current debt for the dummy client)
I put the field 'Client name' in the 'row section' of the pivot, I have the field 'week' in the 'row section' of the pivot and have the field 'current debt' in the data section of the pivot with 'min' in the field option.
Here is my problem.
For week 1 I will get current debt for all customers as being 150.000, which is correct but in week 2, my current debt will be 0. Zero from the dummy customer and because there is oly a payment in week 5, zero from client1, same problem happens in week 3 and 4.
Any way I can get the pivot, possible formulas, macro I can use to fix this problem?
I basically want the pivot to display
week1 150.000
week2 150.000
week3 150.000
week4 150.000
week5 100.000
week6 100.000
and so on
Thanks in advance
Hello,
We are using a pivot table to run a weekly payroll report. It is organized by a roster code with all of the individuals that fall within that last code (by last name). The pivot table report needs to be in chronological date order for each person. Here is a link to an example of the report:
Ideally, for each person, we would add information on the total number of hours in each Category for the week, so for Baker, that would be 35.25 Regular and 4.75 Sick.
Is there any way to add this kind of weekly subtotal by Category for each person to the pivot table while still keeping a similar (tabular) format to what we have above? Ideally it would be as automated as possible because we have the payroll data refreshing automatically from the database so that we can update the pivot tables.
Thanks so much in advance for your help!!!!!!!
Does anyone know how to twist the pivot table view from XML/Cascading style to just a normal listing style like Access for Exel 2007? Example:
XML/Cascading style:
COLUMN A & COLUMN B
Product Phone
----------- TV
----------- DVD
Access/Normal View:
Product Phone
Product TV
Product DVD
I played with Pivot & field option but no luck. Hope someone can point me to the right direction. Thanks much!
Hello Everyone
I am trying to find the data source for a pivot table. And when I click on "change data source" tab, it directs me to a single cell. And I can make new pivot table by using that single cell ("a1") as data source.
Can anyone explain to me how this works?
PS: there is no Macro inside, no hidden worksheets
Thanks a million
Hi All,
I have been using excel2007 for few weeks now.
Today I came across something "unusual".
Before when creating a pivot table, I would get the following screenshot
I could see"Drop data items here" as in excel 2003
Now I get something like that
The funny thing is that with some documents I get the first picture and with others the second one :-S
What's REALLY annoying with the second one is that I cannot easily drag fields across the pivot table, I HAVE to use the right pane.
Is there a way of fixing that?
Many thanks
I have an excel workbook with several sheets in it listing personnel. I was wondering if a pivot table would be the way to go to to generate a report to identify only the individuals that don't meet a certain criteria.
Hi Guys,
I'm looking for some help. I am currently using a pivot table to record athletic race result during competitions. In the generated reports I am unable to sort the report by the required column. I am only being allowed to sort by the first column.
Basically I ned to be able to sort by different columns in the generated report. I really need this to work as detailed for displaying results in order of finish position. Any help would be hughly appreciated. Thanks BB
I've created a pivot table in excel with the following row labels:
LAST NAME
FIRST NAME
MIDDLE
SUFFIX
DOB
SOCIAL SECURITY #
If I assign the LAST NAME column as a report filter, it removes it from the row labels. I would ideally like the report filter to have the LAST name column as well as the FIRST NAME column, but I would like both columns to still appear as row labels. Can this be done using a pivot table?
