Vlookup and Aggregate Formulas

[wheath]

I have a rather large spreadsheet and in one worksheet I put hours associated with certain labor tasks. I would like the hours to translate to another spreadsheet in another format without having to re-type them. I have a formula that was working, but does not seem to be picking up all the items.

I am putting in the link for the spreadsheet. It is the sheet titled "Job Cost" that I input the information. The place I want the information to populate is in "Field Timecard." If you look in Columns Q & R in "Field Timecards" you will see the formulas for pulling the information, however, you will also notice that it isn't pulling to the second page of the timesheet properly. Any suggestions?https://drive.google.com/file/d/0BxlPk4r6K0G9Mi1qUXpZZE5RU05teVNnQ0VfWFN6QnNKSlVv/view?usp=sharing

 

[Jeffrey Mahoney]

Wheath,

Please try this: In cells Q51:Q73, the formula should be:
=IF(ROWS($Q$51:Q51)+COUNT($Q$14:$Q$35)>$T$6,"",INDEX('Job Cost'!$A$7:$A$138,AGGREGATE(15,6,ROW('Job Cost'!$A$7:$A$138)-ROW('Job Cost'!$A$7)+1/('Job Cost'!$C$7:$C$138>0),ROWS($Q$51:Q51)+COUNT($Q$14:$Q$35))))

Jeff

 

[Jeffrey Mahoney]

Weath,

I've never used the Aggregate function before. I gotta say, it took me awhile to figure out how that worked. I pulled that apart by its guts and inspected every value. What got me confused was the Array function using ROW('Job Cost'!$A$7:$A$138), subtracting the beginning row from each of the array values ROW('Job Cost'!$A$7), and then adding 1 / each of the hour values to get a Divide by zero error which Aggregate ignores. It's even hard to explain.

I wish someone would explain how those array functions work. I thought there should be a way for Sumifs and Countifs to do the same job, but I wasn't able to duplicate this solution.

Jeff

 

[wheath]

Weath,

I've never used the Aggregate function before. I gotta say, it took me awhile to figure out how that worked. I pulled that apart by its guts and inspected every value. What got me confused was the Array function using ROW('Job Cost'!$A$7:$A$138), subtracting the beginning row from each of the array values ROW('Job Cost'!$A$7), and then adding 1 / each of the hour values to get a Divide by zero error which Aggregate ignores. It's even hard to explain.

I wish someone would explain how those array functions work. I thought there should be a way for Sumifs and Countifs to do the same job, but I wasn't able to duplicate this solution.

Jeff

I had completely forgotten how I even developed the logic to write that formula, so I was at a complete loss. Your formula worked perfectly! I don't quite understand some of the array and aggregate functions either.

 

[Jeffrey Mahoney]

I tried to replay to your message. The system tells me you have exceeded your message allotment.

If you want to send me a link to your latest version without my TOC, I will get that in there for you..

Jeff