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

D

Dylbus

New Member
Joined
Nov 6, 2014
Messages
8
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Please clarify what you mean by last used?

Last one where there is a value?
Latest edited?
etc

Kind Regards,
Chris
 
Upvote 0
Apologies, I should have explained further.

I am covering a 7 week period with these 7 cells. Each week, a value will be added. So, for example:

Week 1 - B9
Week 2 - G9
Week 3 - L9

etc etc

So it is the last one where a value has been entered that I need.
 
Last edited:
Upvote 0
What type of value, Number or Text?
What is in the 'between' cells (C9 D9 E9 F9 etc)?
 
Upvote 0
The values are numbers.

Each week is split into sections, so within each week there are a green, amber, red, white and other. I am trying to group the greens initially.
 
Upvote 0
So the 'Between' Cells contain TEXT strings, like green red amber etc..

Try

=LOOKUP(9.99999999999999E+307,B9:AF9)
 
Upvote 0
No, there is numeric data in the cells in between.

I have uploaded an example picture to illustrate what I mean.

4uz383.png
 
Upvote 0
This is just an example as I cannot upload the actual spreadsheet for data protection reasons.

Also, the title above should read ''Example - Weekly Tab', not 'Example - Monthyl Tab'. Just to confuse matters a little further!
 
Upvote 0
Perhaps this in C14 filled down/right per your example.

=LOOKUP(2,1/(($C$5:$M$5=C$13)*(ISNUMBER($C6:$M6))),$C6:$M6)
 
Upvote 0
Jonmo, I think this might be EXACTLY what I needed!

It works perfectly with the example. I will try and manipulate it to work in the actual spreadsheet.

Thanks for your time and effort, I really appreciate it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Error 91 when pasting into outlook from excel using vba
Replies
3
Views
511
Micron
M
A
  • Solved
Search a range of cells in another tab and return a value
Replies
6
Views
555
Peter_SSs
Peter_SSs
J
  • Question
VBA to save all indicated in range tabs as all separated PDFs with name of the tabs in the same place where the excel file is
Replies
5
Views
534
Jorgi
J
Kristylee0228
  • Question
VBA Code to loop through Tabs in Excel WorkBook and Paste data in different Book
2
Replies
15
Views
1K
kevin9999
K
S
  • Question
Help with Pop Up Message Box / Cell that States last time tab has data imported into it
Replies
0
Views
283
surkdidat
S

Forum statistics

Threads
1,214,979
Messages
6,122,550
Members
449,088
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top