loop through each last row

[Prabhudharun]

Hi,

I have workbook. In sheet 1 I have below

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Customer Name[/TD]
[TD]Sales Amount[/TD]
[/TR]
[TR]
[TD]Customer A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer B[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]






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