Search code examples
excelfunctionexcel-formularow

Excel - how to achieve the equivalent result of using "Ctrl+End" by using Functions

I have a table in a single sheet excel where the fields are date and consecutive Columbus to enter numbers for currency of different denominations and a total column at the end.

The table gets appended by a row every day.

I want to multiply the column headers (denominations e.g. 2000, 500, 200 etc.) with the denomination number for the latest day. Means the header row × the last row of the table.

I can't achieve this using last blank row, since I have different other data below the Table.

I'm also looking for a solution that doesn't involve VBA.

So, can anyone please help me.

Solution

  • First Method: Without Table (Control+T) Range Formula in B1 is

    =SUMPRODUCT(B2:D2,INDEX(B3:D19,MATCH(LOOKUP(1,1/(A2:A19<>""),A2:A19),A3:A19,0),))

    enter image description here

    There are 3 methods to get the last row/value

    First - Cell F1 formula =MATCH(9.9999E+307,Sheet2!A1:A19) returns last nonblank cell index in the given column range.

    Second - Cell F2 Formula =LOOKUP(2,1/(A1:A19<>""),A1:A19) gives last nonblank cell value in the given column range

    Third - Cell F3 Formula which is useful in this case as there is data below the referred table. =LOOKUP(1,1/(A1:A19<>""),A1:A19) returns the nonblank cell value before the first blank cell

    Second Method: With Table (Control+T) Range Formula in B1 is

    =SUMPRODUCT(VALUE(Table1[[#Headers],[2000]:[200]]),INDEX(Table1[[2000]:[200]],MATCH(MAX(Table1[Date]),Table1[Date],0),))

    In this method, the table range get automatically updated in when a new record is added to the table.

    enter image description here

    So in both the above cases, if a record is added below (adjacent to) the last used cell, the sumproduct formula will refer to that record.