How to get the current filter value applied to a table in Google Docs Spreadsheets?

Question:

( by leniel )

Currently I have a table in a sheet called Timesheet. I use a Filter in the header of this table to filter by Project name. I have a SUBTOTAL formula that gives me a great figure about the total hours  for the current selected project.

Now what I'd like to do is: get the value of the current selected filter (only 1 item/project name in my case) and use it to match with the name of the project in another sheet called Projects where I have the project name in one column and hour value in another column. My desired output would be the total value (Subtotal) of Hours I have in sheet Timesheet multiplied by the Hour value present in sheet Projects.

Hope it makes sense. Here's a sample spreadsheet:

Sheet "Projects":

Sheet "Timesheet":

Is there any script I can attach to my spreadsheet or anything that can let me achieve what I want or is it even impossible right now?

I managed to get it to work in the sheet Projects but that's a static thing. I'd like to have the Total Earned present in the sheet Timesheet and see the value change dynamically according to the filter I have selected in column Project in the Timesheet.

Thanks for your attention,

Leniel

Solution:

Have a look at the following screenshot:

I have change the formula in Cell E2 to:

=INT(SUBTOTAL(109,D4:D) )*24+HOUR( SUBTOTAL(109,D4:D) )&":"&TEXT(MINUTE( SUBTOTAL(109,D4:D) ),"00")

And I have the following formula in Cell F4:

=arrayformula(iferror( round(((hour(D4:D)*3600)+(minute(D4:D)*60)+second(D4:D))/3600;2) * (if(E4:E="";"";vlookup(E4:E;Projects!E3:F;{2}*sign(row(E4:E));false)))))

And I have the following formula in Cell H2:

=INT( SUBTOTAL(109,F4:F) )

And now when you filter the range with "Project 1", you will get the following results:

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive