Could I use a dynamic function to match values to another sheet in Google Sheets? [closed]

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

Want to improve this question? As written, this question is lacking some of the information it needs to be answered. If the author adds details in comments, consider editing them into the question. Once there's sufficient detail to answer, vote to reopen the question.

Closed 3 months ago.

Improve this question

I'm making a quiz-type thing with Google Sheets- one as the user-end (where the quiztaker would input answers) and one as the answersheet. What I want to do is have a function in conditional formatting to see if the value in Cell B2, for instance, is equal to the value in Cell B2 of the answersheet. Quiz on user-end Quiz answer sheet Unlike the example shown in these screenshots, though, the actual quiz will be very large and I wouldn't want to go around pasting functions for EVERY SINGLE CELL. The end formula should see if Quiz(Current Cell No.) = Answersheet(Current Cell No.)

At first, I tried a series of functions to find the current cell's letter-number coordinates, which worked completely fine. =LEFT(ADDRESS(1,COLUMN(),4)) & ROW() and =SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "") & ROW() both worked perfectly in finding letter-number coordinates of the current cell. But I still needed a way to reference that cell, but in another sheet. '=Answersheet'!B2 could nicely find me the value of B2 (in the answersheet), but '=Answersheet'!(LEFT(ADDRESS(1,COLUMN(),4)) & ROW()) returned a formula parse error. I couldn't get on to trying to find how to compare the values because I'm still stuck at this step. Meanwhile, I'll just dawdle around and finish that project due tomorrow.

Welcome to StackOverflow! Please provide a minimal reproducible example in the form of markdown tables containing your sample input data and desired output. You may create one with the help of this link. — Jats PPG
– Jats PPG, Commented Sep 17 at 15:08
Have you considered creating a form instead of sharing a spreadsheet for collecting answers? — doubleunary
– doubleunary, Commented Sep 18 at 17:24

Harun24hr · Accepted Answer · 2025-09-18 02:10:48Z

2

You just need INDIRECT() function. Try-

=INDIRECT("'Answersheet'!"&LEFT(ADDRESS(1,COLUMN(),4)) & ROW())

But, why not you use XLOOKUP() function to get answer from answersheet against particular question?

answered Sep 18 at 2:10

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Could I use a dynamic function to match values to another sheet in Google Sheets? [closed]

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related