expand a formula

buz

Board Regular
Joined
May 30, 2002
Messages
230
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,976
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Board Regular
Joined
May 30, 2002
Messages
230
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

Board Regular
Joined
May 22, 2002
Messages
209

ADVERTISEMENT

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

Well-known Member
Joined
Feb 15, 2002
Messages
4,976
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
May 30, 2002
Messages
230

ADVERTISEMENT

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

Board Regular
Joined
May 30, 2002
Messages
230
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

Threads
1,144,739
Messages
5,726,006
Members
422,652
Latest member
Elnene1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top