Just replace the static text with cell references Creating the getpivotdata formula as I suggested does put "static" text in the formula - eg =GETPIVOTDATA("Sum of Utilisation",$A$3," ","Work","Team","Wksp")
Some of these are the field headings in the pivot table, but the ones for selecting data (in this case "Wksp"), can be replaced with a cell reference (ie D12), which you can populate with the item you want to look up.
This technique can be used for all the parts of this formula if you wish
This makes it easy to copy the formula & reuse accordingly.
Getpivotdata formula is easier in Excel 2003 If you gather information often from pivot tables, an improvement in Excel in the latest version makes it incredibly easy.
All you have to do is start a formula in the cell you want to put the data into, by typing a "=" & then just click the cell you require in the pivot table.
Excel will then write the formula for you!
You can then improve the formula by replacing the text criteria with rangenames, thus giving a reusable formula to get virtually any information needed.
I know this is no help if you do not have the latest version, but this may well be reason enough to upgrade!
My answers
We have used https://whosoff.com for a few years now.
Recommended.
Just replace the static text with cell references
Creating the getpivotdata formula as I suggested does put "static" text in the formula - eg
=GETPIVOTDATA("Sum of Utilisation",$A$3," ","Work","Team","Wksp")
Some of these are the field headings in the pivot table, but the ones for selecting data (in this case "Wksp"), can be replaced with a cell reference (ie D12), which you can populate with the item you want to look up.
This technique can be used for all the parts of this formula if you wish
This makes it easy to copy the formula & reuse accordingly.
Tony
Getpivotdata formula is easier in Excel 2003
If you gather information often from pivot tables, an improvement in Excel in the latest version makes it incredibly easy.
All you have to do is start a formula in the cell you want to put the data into, by typing a "=" & then just click the cell you require in the pivot table.
Excel will then write the formula for you!
You can then improve the formula by replacing the text criteria with rangenames, thus giving a reusable formula to get virtually any information needed.
I know this is no help if you do not have the latest version, but this may well be reason enough to upgrade!
Many thanks
I also managed to find an answer at:
http://www.cpearson.com/excel/DateTimeWS.htm
This uses the NETWORKDAYS function to eliminate weekends as well.
I would recommend this site be checked out, especially for data/time problems.
Once again many thanks
Brilliant
This worked a treat.
I have also managed to suppress the nasty #N/A output in the data table, by using a "formula is" statement within a conditional format.
many thanks
Seems like a plan.....
Thanks for your help on this lads.
I'll try the pivot table solution.
Not quite.....
A pivot table might work, but with the level of Excel ability of the two users this may be an issue.
The users need full access to the information on the input sheet & will need to save(needing write access)
The second one "presents" a subset of the columns, by use of formulas, which I have copied down for about 100 lines.
I was trying to use the AutoFilter to collapse the list, making it suitable for e-mailing to individual suppliers.
I therefore wanted to try to stop the two users doing anything at all to the output sheet apeart from selecting which supplier to report on.