Using lookup with multiple non-sequential rows

[Spl1t1]

I am trying to have a cell lookup the last value entered in a row, but I also want it to look at the last entry of multiple rows. Meaning, on sheet 1 cell C8, I want it to display the last entry made from sheet 2 cells B7:F7, B20:F20, B33:F33, etc.... I will have 12 rows total that I ned the last entry to populate on sheet 1 cell C8. Any help would be greatly appreciated.

 

[Peter_SSs]

If the entries are numeric and you enter them in sequence (without blanks) then could you use this non-array formula?

Excel Workbook
	C	D	E	F	G
7	6	65	6	6	5
8	3	4	0	23
9
10
11					23
Last entry

 

[T. Valko]

That is so awesome. I forgot to add the worksheet name, I re-wote the entire formula. Thank you so much!!!!! This rocks!

You're welcome. Thanks for the feedback!

 

[Spl1t1]

Now, what if I want the Quarter Average on SSA worksheet which are cells J9, J12, J15, J18 and I want the most recent entry on Scorecard wooksheet V25?

 

[T. Valko]

Now, what if I want the Quarter Average on SSA worksheet which are cells J9, J12, J15, J18 and I want the most recent entry on Scorecard wooksheet V25?

For the average:

=AVERAGE(J9,J12,J15,J18)

How do you know which is the most recent entry? Is it the last cell that has an entry?

If that's the case then something like this:

=IF(J18<>"",J18,IF(J15<>"",J15,IF(J12<>"",J12,IF(J9<>"",J9,""))))

 

[Spl1t1]

Well what I did now was was move the Quarterly Average cells so they are in a column. I have the formula =IF(COUNT(C7:G9),AVERAGE(C7:G9),0) in the cell. So here is the problem. I have a master workbook that has a master worksheet, plus 16 other worksheets (which this formula is on) then transfers onto the master worksheet. On the worksheet that contains this formula I also have serveral cells that update data from another workbook. If there is nothing in the cell on the other workbook, it produces a 0, but this is a problem cause the formula on the master workbook master worksheet (the formula you helped me create) it updates those 0's. Now if I put in a 0 into the cell on the 2nd worksheet and the 2nd workbook I do need it to update then on the master workbook master worksheet.

 

[T. Valko]

Well what I did now was was move the Quarterly Average cells so they are in a column. I have the formula =IF(COUNT(C7:G9),AVERAGE(C7:G9),0) in the cell. So here is the problem. I have a master workbook that has a master worksheet, plus 16 other worksheets (which this formula is on) then transfers onto the master worksheet. On the worksheet that contains this formula I also have serveral cells that update data from another workbook. If there is nothing in the cell on the other workbook, it produces a 0, but this is a problem cause the formula on the master workbook master worksheet (the formula you helped me create) it updates those 0's. Now if I put in a 0 into the cell on the 2nd worksheet and the 2nd workbook I do need it to update then on the master workbook master worksheet.

Try something like this...

=IF(A1="","",A1)

If A1 is an empty cell the formula will return a blank instead of 0.