Connecting a cell to the last cell of entered data, HELP!

BrendanO

New Member
Joined
Aug 14, 2014
Messages
7
Hi everyone,

So I'm creating a tracker for a project at work. I'm tracking communication with a group of companies (sales). Anyway, I'm using the first worksheet (i.e. the one on the far left) to keep a summary of the last communication I had with the company. Each subsequent worksheet contains all communication with an individual company from the list contained in the first worksheet.

What I'm trying to do is connect a cell from the main summary worksheet to the last cell with data in a column on one of the subsequent worksheets. The reason I want the last cell of data is because I want to be able to look at the main worksheet and see the last communication I had with any one of the companies that I'm communicating with. If this doesn't make sense, please let me know and I'll clarify! Thanks so much!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
You could use a VBA macro to determine the last cell with data in it then copy the data over

Rich (BB code):
'set the final rows
finalrow1 = sheets("Sheet 1").cells(rows.count, 1).end (xlup).row
' change 1 to column you want to copy and sheet 1 to the name of the sheet with data on it

'copy data and move to summary sheet
finalrow.Copy destination:= sheets("Summary").Range("**your range here**")

don't know if this is what you're looking for
 
Upvote 0
Could you use a MAX formula to look up the last date, e.g.,

Code:
=MAX(Sheet2!$C$1:$C$100)
... and then use lookup formulas from there, e.g.,

Code:
=VLOOKUP(A1,Sheet2!$C$1:$D$100,2,FALSE)
Chris.
 
Upvote 0
Hi Chris/Jsting,

Thanks for the help. Chris, the MAX code you sent works for me, however the values that I'm drawing from the subsequent worksheets and inputting to the main sheet aren't numerical, it's text. Basically the C column contains last know communication, i.e. "sent an email, waiting for reply." I'm trying to take that last communication at the bottom of column C in the sub worksheet and putting it into the main worksheet next to the respective company's name. Thank you again for the help! Again, great code, I just need it to work for non-numerical data.
 
Upvote 0
Still coming up with zero in the main document. Here, I've attached a link to a screenshot telling you exactly what I'm trying to do. As you can probably tell from this thread.... I'm not exactly an Excel genius.

View image: screenshot
 
Upvote 0
Rich (BB code):
=MAX(Advantage!$A$3:$A$100)

In G2:

Rich (BB code):
=VLOOKUP(F2,
Rich (BB code):
Advantage!$A$3:$B$100,2,FALSE)

In H2:

Rich (BB code):
=VLOOKUP(F2,
Rich (BB code):
Advantage!$A$3:$C$100,3,FALSE)

(Please excuse the multiple boxes, I have no idea why that's happening.) :laugh:

How are those?

Chris.

 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,924
Members
452,366
Latest member
TePunaBloke

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