Pivot table issue, I'm not able to capture new data, when table is updated

[johnnyq113]

Iv'e also included what I'm trying to capture.

thank you guys for the help.

(update 8/19 New workbook attached)

[ATTACH]414573[/ATTACH

[FDibbins]

Hi, welcome to the forum

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

[MarvinP]

Hi johnnyq and welcome to the forum,

I think you need to learn about Dynamic Named Ranges. They grow or shrink based on how many rows or columns of data you have. Then you can use this Name as the range in your Pivot Table.

See these links:

http://www.ozgrid.com/Excel/DynamicRanges.htm
http://www.bettersolutions.com/excel...G820716330.htm

[johnnyq113]

Thank you MarvinP.. I will look into it.

[humdingaling]

This is the method i prefer most of the time

http://www.contextures.com/xlPivot01.html#opt1

[johnnyq113]

Actuals Pivot table.xlsx


I'm not sure if this is allowed in the forum, but I'm willing to compensate, if I can actually get this form to work.


I apologize for being such a noob. I've spent the past few days searching and experimenting different formulas but ended up nowhere.
I've tried the v/h lookup function but it's still not doing what I want it to.

So with the references you guys implied with the dynamic name range.
to my understanding the Pivot table I uses already populates new data as I refresh the worksheet.

I'm trying to capture how much "production, and units" were done for each shift.


On the "SR-Print" worksheet,

Under "SR-SRPRNT" its suppose to capture data from the pivot table.
and from there base on the time that each shift starts, it suppose to take a "snapshot" from start/end of shift of how much production,units
were done at that specific time.

I hope this all make sense.

[johnnyq113]

bump?

I did a little More research and I'm going to try the index+Match function and see if that takes me anywhere

[humdingaling]

im not quite sure what you want as the output with the data you have provided?

when i open the sheet it tries to get data from a external source...maybe this isnt updating properly which means cannot see what you are
essentially you want to pull information from a column but dont really know how it can be split into the sub sections?

[johnnyq113]

Actuals Pivot table.xlsxThank you for taking a look at it.

Danggit I think your right its trying to grab data from the database. But I think i' m making progress which the time capturing portion.

It seem that I had to convert the time function =Now(), into a decimal value.
then compare < or > to the time I wanted to capture at the end of shift.

im going to attach my current workbook.

[humdingaling]

it seems you are on the right track now?

i do suggest you change SP-PRNT sheet cell A4 to read SR-SRPRT to match your pivot table?
that way you can index/match it....in case the number of columns increase/decrease?

i assume you also can then use a match/index to find the mate based on now() so you get the right row of data as well

[johnnyq113]

thanks,

im stilling having trouble, capturing the "snapshot" of the data.
On the Sr-prnt worksheet. in cell F3, it captures the data when the statement is true.
But i think i need a false statement, to tell it to keep the current data and do nothing when the statement becomes false. Instead it overwrites it. any ideas?