Creating dynamic cell references

[Curious Dude]

Hi everyone. I have a simple range, say, A1:G1. I want to have another cell, H1, reference whatever rightmost cell in the range contains any data. All the cells in the range will be using text.

So if B1 contains data, I want H1 to show what's in B1, but if data is inputted into F1, I now want H1 to show what's in F1.

Can someone explain how to do this?

[JBeaucaire]

Use this to show the last cell with any value in a range.

=LOOKUP(2, 1/($A1:$G1<>""), $A1:$G1)


If you want to see HOW that works, click on H1 after entering the formula, then use the Formulas > Evaluate Formula feature to watch it unfold.

[Curious Dude]

Thank you! It works perfectly.

There's just one more thing I realized: I need to bring over the date too. So let's say F2 is the rightmost cell containing data, in the cell directly above it (pretend my range was A2:G2 instead ** A1:G1) will be the date which I would like brought over to the cell directly above H2.

So if F2 is the rightmost cell with data, F1 above it containing the date gets copied to H1, and F2 gets copied to H2.

How can this be accomplished?

[JBeaucaire]

Same formula, change the second reference range which is the value to return to the cell.

=LOOKUP(2, 1/($A2:$G2<>""), $A$1:$G$1)

[Curious Dude]

The only problem is in the date range every cell is already filled so it doesn't work the same in this row. Thank you for trying to help me. I really appreciate it.

I know best how to explain the setup now if you'll hear me out.

Column A will be a master list of my clients, starting at A2 going down. Going across the top row, B1:G1 will be the dates March 1-March 6.

I want to have it so that when I populate a cell below it, that date is brought along with it, with the date going into H1, and what was below it going into H2.

So basically the same idea as above, with the rightmost cell being sent to the H cell, only also making the cell (containing the date) above it appear in the H cell above that; so the date of the most recent task gets put together with the details.

As an example, I work on client X on March 2nd, so on the sheet in the cell below March 2nd (in this case, C2) I enter in the work details. Since that is the most recent date (rightmost cell populated), that gets brought to H2. The date of March 2nd (from C1) gets brought above it to H1. So basically whatever cell is the most recent one automatically grabs the date (in the cell above it) along with it. If I enter work for March 4th into cell E2, I want it to grab the date of March 4th from E1 and bring that to H1, etc.

[AliGW]

Can you upload a sample workbook to better illustrate what you're after and what you've already tried?

[Curious Dude]

I'm going to mark this thread as solved because my issue was resolved enough to my satisfaction. I've realized an easier way to get the rest of what I'm looking to get done done. Thanks!~