Formula to display number from the last (rightmost) non-blank cell in a specific row in another sheet...

Question

Level 1

44 points

Formula to display number from the last (rightmost) non-blank cell in a specific row in another sheet...

The title mostly covers it. I have a cell in Sheet 1 in which I want to display a number from a cell in Sheet 2. But, when a new value is added in the next column I want to see the newly added value.

Say I have this in Sheet 2:

ColA ColB ColC ColD ColE, etc.

Row1 28 36 15

Row2 54 19 30

Row3 16 22 41

I want the cell in Sheet 1 to display the 41 from C3, but if anything is ever entered in D3—55 for example—then I want to display the 55. If something is entered in E3, I want to see that, etc.

In case it matters, there are no blank cells in row 3 prior to column C, and nothing but blanks in that row from D and beyond.

Thanks!

Posted on Apr 29, 2022 7:29 AM

Reply

Answer 1

SGIII

Level 8

37,532 points

Apr 29, 2022 9:16 AM in response to David Dixon

One way is to do something like this:

=XLOOKUP(REGEX("."),Table 2::3:3,Table 2::3:3,"",2,-1)

Or, if you are in a region that uses , as the decimal separator:

=XLOOKUP(REGEX(".");Table 2::3:3;Table 2::3:3;"";2;-1)

This tells Numbers to search from last to first in the range for the first cell with a character in it.

SG

Reply

Answer 2

David Dixon Author

Level 1

44 points

Apr 29, 2022 9:36 AM in response to SGIII

Thanks! I don't have time at the moment to try it, but.... what is meant by "in a region that uses , as the decimal separator"

And I assume I would replace "Table 2" with the name of the source Table, and replace the "3"s with the Row I'm using?

Thanks for the help!

Reply

Answer 3

Barry

Level 9

60,023 points

Apr 29, 2022 10:31 AM in response to David Dixon

"what is meant by "in a region that uses , as the decimal separator"

It means if you write " $10.25" you are in a region in which the decimal separator is a period ( . )

if you write " $10,25" you are in a region in which the decimal separator is a comma ( , )

Reply

Answer 4

David Dixon Author

Level 1

44 points

Apr 29, 2022 11:21 AM in response to Barry

Ah, got it sorted. In case anyone else ever sees this, yes, I needed to use the commas. In the area of beginner issues, I also had a bit of fiddling to do to get the parentheses right. Lastly I just had to select the entire row at the far left, I was selecting the cell, not the row. Sheesh.

Thanks so much for the timely help!

Reply

Formula to display number from the last (rightmost) non-blank cell in a specific row in another sheet...

Similar questions