expand a formula

[buz]

I just realized this formula only works for one time customers. If I have a repeating customer then the formula is 'all' inclusive. Is there a way to include a date range with this formula?

=GETPIVOTDATA([JobCosting.xls]Cust_PT!$A$4,"grand total tabaka")

tfyh

Buz

 

[Dave Patton]

On 2002-10-02 23:10, buz wrote:
I just realized this formula only works for one time customers. If I have a repeating customer then the formula is 'all' inclusive. Is there a way to include a date range with this formula?

=GETPIVOTDATA([JobCosting.xls]Cust_PT!$A$4,"grand total tabaka")

tfyh

Buz

Hello Buz

I cannot provide an informed comment with the above information.

With one of your first Pivot Tables, I illustrated how you could access a particular part of the Pivot Table.

The formula was something like

=GETPIVOTDATA(Hr_Summary!A3,""&B15&" "&C15)

This accessed B15 (Brian) for C15 pay period 7.

 

[DonkeyOte]

GETPIVOTDATA method will vary depending on the version of Excel you have... 2002/XP has far greater flexibility for drilling into tables with multiple paramaters compared to 2000. Tell us which version you're on.

 

[buz]

I am using XL2001.

The referenced formula is for customer name 'tabaka' and would return all employee hours spent on their job times a labor rate and markup.

Problem is that 'tabaka' job ended and has been invoiced and now they want more work done. I'm stuck on how to 'not' include previous job hours, materials and subs on new invoice. My thinking in the original post is that if the formula could include a date range then only the hours, materials, subs returned by > and< dates could solve my problem.

Maybe I need to rethink the whole design??

_________________
Dave - I was just taking a look at the formula you provided for employee pay period hours and had a glimmer of light. The way you set me up with SUMPRODUCT for employee pay period hours, I could also use that for 'total customer hours', just pull it straight from the DB instead of from a PT. Yes? No? Just use a beginning and ending date on the invoice which would be referenced for totals.
This message was edited by buz on 2002-10-03 09:47

 

[Tom@CPC]

buz,

you may have answered your own question! You say each customer has a job number/invoice number, so include this piece of information in your pivot table as well. You can look up through multiple levels with the getpivotdata function.

So if tabaka has jobs 1234 and 2345, you could use ...'tabaka 2345'... as what you want to look up.

Regards
HTH, Tom

 

[Dave Patton]

On 2002-10-03 09:38, buz wrote:
I am using XL2001.

The referenced formula is for customer name 'tabaka' and would return all employee hours spent on their job times a labor rate and markup.

Problem is that 'tabaka' job ended and has been invoiced and now they want more work done. I'm stuck on how to 'not' include previous job hours, materials and subs on new invoice. My thinking in the original post is that if the formula could include a date range then only the hours, materials, subs returned by > and< dates could solve my problem.

Maybe I need to rethink the whole design??

_________________
Dave - I was just taking a look at the formula you provided for employee pay period hours and had a glimmer of light. The way you set me up with SUMPRODUCT for employee pay period hours, I could also use that for 'total customer hours', just pull it straight from the DB instead of from a PT. Yes? No? Just use a beginning and ending date on the invoice which would be referenced for totals.
This message was edited by buz on 2002-10-03 09:47

Yes With Sumproduct, you can define the exact date range that you want to use.

If all quantities are in distinct periods for billing purposes, you may also be able to extract the information from your PT.

 

[buz]

Tom - Thank you - thats an excellent idea. I may have to implement that.

Dave - I have been playing with SUMPRODUCT (referencing the explanation you gave me on how it works) and have been able to alter the formula to get the specific data for a customer/date range.

Little by little I am understanding, but still don't think I could create one of these formulas from scratch.

Thanks a million for allllllllll your help!

 

[buz]

Ok - so I'm not getting there.

Can SUMPRODUCT be used like GETPIVOTDATA from another workbook, getting data from the database to a different wkbk?

This is the formula I have been using:
=SUMPRODUCT((INDEX(Drange,0,3)=A16)
*(INDEX(Drange,0,1)>=A14)*(INDEX(Drange,0,1)<=B14)
*(INDEX(Drange,0,4)))

and I'm trying to get a sheet name involved like this:

=SUMPRODUCT([JobCosting.xls](INDEX(Drange,0,3)=A16)
*(INDEX(Drange,0,1)>=A14)*(INDEX(Drange,0,1)<=B14)
*(INDEX(Drange,0,4)))

still a novice here.

 

[Aladin Akyurek]

On 2002-10-03 18:06, buz wrote:
Ok - so I'm not getting there.

Can SUMPRODUCT be used like GETPIVOTDATA from another workbook, getting data from the database to a different wkbk?

This is the formula I have been using:
=SUMPRODUCT((INDEX(Drange,0,3)=A16)
*(INDEX(Drange,0,1)>=A14)*(INDEX(Drange,0,1)<=B14)
*(INDEX(Drange,0,4)))

and I'm trying to get a sheet name involved like this:

=SUMPRODUCT([JobCosting.xls](INDEX(Drange,0,3)=A16)
*(INDEX(Drange,0,1)>=A14)*(INDEX(Drange,0,1)<=B14)
*(INDEX(Drange,0,4)))

still a novice here.

Try:

=SUMPRODUCT((INDEX('JobCosting.xls'!Drange,0,3)=A16)
*(INDEX('JobCosting.xls'!Drange,0,1)>=A14)*(INDEX('JobCosting.xls'!Drange,0,1)<=B14)
*(INDEX('JobCosting.xls'!Drange,0,4)))

where I assume Drange to be already defined in JobCosting.xls.

 

[buz]

Aladin - worked like a charm - thank you!