copy certain cells corresponding to a date to another sheet 2

[Naminator]

Help Please.
I have a production sheet where the tanks get listed with the date they were produced and each tank gets a number. I want to copy the cells next to the latest date, ie the current stock to a different sheet. It must automatically look for the latest date, then copy the cells correspoding to that day into the other sheet. The cells that have to be copied vary according to the stock on hand. Please if someone can help. I managed to get the date over with the MAX formula which i use in the column for the date. Now i need to somehow copy the cells next to it and down and maybe summarize.

Thanks for any help.

 

[SkipVought]

Hi,

Have you tried using the PivotTable Wizard, which is a great Excel aggreagtion feature and requires no VBA code?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Naminator]

Hi Skip,

No, I didn't know about it but tried it out last night and i must say, what a powerful tool.

had to change the layout of my data a bit but then it worked really well.

Thanks again for the help.

Kinf regards,
Heiko

 

[Naminator]

Hi skip, now i need help with pivot tables:
I want to have my data in one workbook and the pivot table in another. Any ideas how that can work?

Thanks,

Heiko

 

[SkipVought]

When you start the PT, select EXTERNAL DATA in the first window. Then you can QUERY the other sheet and pivot the results.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Or create in the same workbook then move the sheet to a new book.

Gavin

 

[SkipVought]

All kinds of ways to skin a cat. Poor cat!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Naminator]

thanks for all the help guys,

got it to work.

kind regards,

Heiko

 

[Naminator]

Hi guys,
I'm stuck again. I have one workbook where the data gets entered as it becomes available. I have another workbook which is on my pc which has the pivot table in it which summarizes the data from workbook 1. It does not seem to update the pivot table when the other workbook is not closed. Can it be done that my secretary leaves workbook 1 open to enter data with time and i leave workbook 2 open and the table gets updated the whole time?

Thanks
Heiko

 

[Gavona]

For me: If you Refresh the pivot table it will update from the saved version. Even if that workbook is open on another machine.

However the source data must be a simple range or better a simple named range.
It cannot be a dynamic named range (specified with a formula, typically including the count function) as this cannot be evaluated unless the workbook is open.

Gavin

 

[Naminator]

Hi Gavona,
Thanks for the help. My data that is beeing entered is in a "table" format and the data is being entered with the "form" function. Does this make it a dynamic range? Otherwise there are no formulas in there.

I will do a trial with a sheet if the data isn't in a "table"format.

Heiko

 

[Gavona]

How does the pivot table know which data to analse? PivotTableWizard, Back should show how your source data is specified. Could be along the lines of:
'C:\...\Documents\[eraseme.xls]MTPSP-TF'!$A$1:$S$466
or
'C:\Users\Gavin\Documents\[eraseme.xls]!myData

If the latter then the source workbook contain s a named range "myData"

This could be specified within the workbook as a simple range: MTPSP-TF!$A$1:$S$466

or as a range that changes dynamically as rows columns are added and deleted faq68-1331.

This last type cannot be used as the source data for your pivot table unless the workbook is open

Gavin

 

[SkipVought]

data is being entered with the "form" function.

Do you meand the Data > Form... feature?

This requires that the table you are maintaining is a database or list with a valid range. Chances are that it is a named range that is 'dynamic' as Excel changes the range name reference as data is added or deleted.

The question is, does the name, reference the entire table, and does your pivot table use this named reference, as Gavin suggested in the previous post? Looks like...
[tt]
ProductionShipTravelers.xls!dbtest1
[/tt]
I made a change in this workbook's table, closed it, opened the workbook with the PT & updated the PT, which reflected the added data in the closed workbook.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Naminator]

Hi guys,

I am a bit confused now, sorry.
1. The way my excel looks i cannot find the pivot table wizard in any menu-bar. I only found it by googeling and finding the key combination alt D P. It will not let me do that on the old pivot table though, so i can't go back to see how the source data is specified.

2. Yes, it is the DATA > Form menu that i am talking about. I specified the range by selecting the columns which the data will be entered into. This way it takes the whole column all the way to the bottom. The same when i selected the data for the pivot table. I just click on the column "letters" and therefore select the whole columns. This way it does not matter how many rows i enter, they automatically get added to the pivot table when i refresh. Don't know if this helps?

3. I played around last night and opened two workbooks. the one was a table where i entered data through the "FORM" menu and the other was a pivot table linked to the data. I found that when the form was open, i could not refresh the pivot table.

Just to refresh. I have two workbooks. The one is beeing used to enter data and the other has a pivot table which summarizes the data. The one where the data gets entered stays open all day on a different pc. the one with the pivot table gets opened by me to check stock, orders, sales etc. Any other suggestions how i can do this maybe?
The problem that i am having is that i get all kind of connection or access errors when i want to refresh.

Thanks

Heiko

 

[Gavona]

When you want to refresh just open a copy of the source workbook first. Because it is already open then you will open it read only but that is no problem.
Maybe create a little macro that opens the source workbook, refreshes the pivot then closes the source workbook.

Gavin