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

[BrendanO]

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!

 

[jsting]

You could use a VBA macro to determine the last cell with data in it then copy the data over

'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

 

[Chris Mack]

Could you use a MAX formula to look up the last date, e.g.,

=MAX(Sheet2!$C$1:$C$100)

... and then use lookup formulas from there, e.g.,

=VLOOKUP(A1,Sheet2!$C$1:$D$100,2,FALSE)

Chris.

 

[BrendanO]

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.

 

[Chris Mack]

Does this work:

=INDIRECT("Sheet2!C"&MAX(IF(Sheet2!$C$1:$C$100<>"",ROW($C$1:$C$100))))

Chris.

 

[BrendanO]

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

 

[Chris Mack]

=MAX(Advantage!$A$3:$A$100)

In G2:

=VLOOKUP(F2,

Advantage!$A$3:$B$100,2,FALSE)

In H2:

=VLOOKUP(F2,

Advantage!$A$3:$C$100,3,FALSE)

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

How are those?

Chris.