loop through each last row

[Prabhudharun]

Hi,

I have workbook. In sheet 1 I have below

Customer Name	Sales Amount
Customer A
Customer B

<tbody>
</tbody>

it continuous n number of customers.

And sheet 2 name is Customer A, sheet 3 name is Customer B.. sheet 4..sheet 5 Customer N number..

Each customer sheet has sales value which is last row of column G.

I need to fetch customer sales on sheet 1 against each customer.

Any help would be much appreciated.

 

[Joe4]

If the Customer Names in column A EXCATLY match the Sheet Names, then here is a formula you can put in cell B2 on Sheet1 and copy down for all rows and it should do what you want:

=LOOKUP(2,1/(INDIRECT("'"&A2&"'!G:G")<>""),INDIRECT("'"&A2&"'!G:G"))

It basically combines the two things mentioned here:
https://exceljet.net/formula/get-value-of-last-non-empty-cell
https://exceljet.net/formula/indirect-named-range-different-sheet