HELP! Getting the last cell in a range from a different tab

[Dylbus]

Hi all,

I hope you can help. I have read these forums for a long time but just signed up as this is the first time I haven’t been able to find an answer on here!

I am looking for a formulae to get the last cell in a range from a different tab.

The range is B9, G9 L9 Q9, V9, AA9, AF9 on the worksheet 'New Monitoring'. I need to show the last used cell from this range in a cell on a different tab/worksheet.

I have tried to name the range as 'TestRange' and have tried this formulae - INDEX(TestRange,MATCH(9.99999999999999E+307,TestRange)) But it doesn’t return the value.

I am scratching my head and have hit a brick wall. Any help would be hugely appreciated.

 

[Dylbus]

I have edited the formulae to read like this:

=LOOKUP(2,1/(($B$5:$AF$5=D$4)*(ISNUMBER($B9:$AF9))),$B9:$AF9)

D4 is on tha main tab/sheet and I have inputted this formulae in that cell.

All the other data is on a tab called 'New Monitoring'. How would I amend this formulae to show that B9 to AF9 are on the other tab? I know I need to put something like ='New Monitoring'! but I cant work out where!

I hope that makes sense.

 

[Jonmo1]

D4 is on tha main tab/sheet and I have inputted this formulae in that cell.

You can't put the formula in D4, because it refers to D4, that would be a circular reference.
D4 is supposed to be a cell containing the color you're looking for (green red amber etc..)

Other than that
You have to add the sheet reference to all 3 ranges that exist on the other sheet.

=LOOKUP(2,1/(('New Monitoring'!$B$5:$AF$5=D$4)*(ISNUMBER('New Monitoring'!$B9:$AF9))),'New Monitoring'!$B9:$AF9)

 

[Dylbus]

Jonmo, I cannot thank you enough for this.

I now have that formulae in maybe 50 cells, all relating to different sheets and it is working perfectly.

I was completely stuck and you have saved the day. Thanks very much.

 

[Jonmo1]

You're welcome

 

[jim may]

I spend a considerable amount of time reviewing the content of this site. It has, more than any other source, brought me somewhat along in my better understanding of Excel.

As I review this thread I'm a bit confused when I compare the final formula solution (cell references, etc. ) (offered by JonMo1 - Tks !!) with the 2 worksheets displayed earlier.
Could someone re-post the 2 worksheets showing the layout that is in agreement with the final formula?

Thanks, Jim