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

user_4024307 0
Viewed 63

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.

row excel-formula excel function
2 Answers

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.

user_98080630

What I actually ended up using is the Concatenation function to refer to the last row of the Table, wich I got by using TableName inside the ROW & ROWS functions.

  1. TableName[[#Headers],[2000]]*INDIRECT("C"&ROW(TableName)-1+ROWS(TableName))
  2. TableName[[#Headers],[500]]*INDIRECT("C"&ROW(TableName)-1+ROWS(TableName)) and so on.

This did not have any issues with all the data I have below my Table.

Thanks for all the help.

user_40243070
Related