I do believe, that there must be a very simple solution to my problem - but I am just too stupid to know it or to find it.

I am in sheet 2 of google sheets table and I do want to use a simple reference in a cell, like this:

='sheet 1'!A1

to display the value of cell A1 in sheet 2. But as the sheet 1 is dynamically appended by a google form, I do always like to use the value of the last row of that form as soon as I do open the the sheet 2.

So I like to build "A1" from 2 values - the string "A" and the dynamic "row number". I can get this build with

=CONCAT("B"; K1)

whereby "K1" is build from this formula for fetching the last row number from a sheet


But I have no clue, how I can combine these values in a cell reference statement.

Can someone please help me?

Thank you & Best Regards, Markus.

New contributor
user286412 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • Welcome to Web Applications. Try using INDIRECT (we already have a lot of questions related to this function). If you need further help, please add a brief description of your search efforts as is suggested in How to Ask.
    – Rubén
    Nov 23 at 22:40
  • Your goal is not clear. In Sheet2, are you wanting to return only the last entry from Sheet1? Or do you want all of the entries from Sheet1, but in reverse order (i.e., with the newest entries at the top instead of at the bottom)? In addition to answering that question, consider sharing a link to your spreadsheet (or to a copy of it), so that we can see what you are trying to do and where.
    – Erik Tyler
    Nov 24 at 1:32
  • Hmm... you are asking "how to use a dynamic value from a cell as row number for a cell reference" but it appears that you really want "how to get the last value in a column". Is this an XY problem? If so, please edit the question title and the problem description. Nov 24 at 14:25

To get the last non-empty value in column Sheet1!A1:A, use sortn(), like this:

=sortn(Sheet1!A1:A, 1, 0, Sheet1!A1:A <> "", false, row(Sheet1!A1:A), false)

...or index(), like this:

=index(Sheet1!A1:A, max((Sheet1!A1:A <> "") * row(Sheet1!A1:A)))

To get more than one column, replace the first instance of Sheet1!A1:A with Sheet1!A1:F in either formula.

Your Answer

user286412 is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.