Does anybody know how to reference an entire row from one sheet in another, based on one cell?

anehn

Hi,

I'm trying to observe the number of forms submitted to a sheet per month and their status. So far I've managed to count how many sheets per month have been submitted (this is done in a second sheet from the one where the forms are submitted to). I believe that the issue lies in the fact that so far I've only referenced single cells in the sheet where the forms are submitted and can't check other cells in that row for their content.

Does anybody know how to reference an entire row from one sheet in another, based on one cell, so that I may check the content of a different cell?

All the best

Paul Newcome

Give something like this a try:

=COUNTIFS({Date Range}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2025), {Status Range}, @cell = "Specific Status")

So it looks like you were on the right track already. Were you getting an error message or an unexpected count?

Paul Newcome

If you are trying to gather the count of rows from another sheet based on specific criteria in more than one column, you would use a COUNTIFS.

https://help.smartsheet.com/function/countifs

.

anehn

@Paul Newcome Thanks, but I've already tried that to no avail.

Currently this is my formula if I try with countif:

=COUNTIFS({Vorschlag 2 Range 2}, IFERROR(MONTH(@cell ), 0) = Monat@row, {Vorschlag 2 Range 2}, "Wartet auf Zusage")

"Vorschlag 2 Range 2" refers to the two columns in the other sheet that I'd like to use.

=COUNT(MATCH(Monat@row = MONTH({Vorschlag 2 Range 1}), {Vorschlag 2 Range 1}, 0) = MATCH("Wartet auf Zusage" = {Vorschlag 2 Range 3}, {Vorschlag 2 Range 3}, 0))

This is one of the many other ways I've tried. If it helps my thoughtprocess was to check whether the month statement and "Wartet auf Zusage" stement were true in the same row and then count the number of rows where that was the case.

kowal

hi @anehn,

why would not you use VLOOKUP or INDEX/Match formula for that?

Paul Newcome

@kowal How would a VLOOKUP or INDEX/MATCH help with getting a count of rows?

@anehn Are you able to provide screenshots for context?

kowal

hi @Paul Newcome ,

"Does anybody know how to reference an entire row from one sheet in another, based on one cell, so that I may check the content of a different cell?" - it looks like a typical Index/Match usage for me.

Paul Newcome

@kowal Right, but if you read the actual post (very first sentence) and the comments, you will see they are looking for a count.

anehn

@Paul Newcome These are the two columns. The goal is to count the Number of rows in a certain status at any given month in a separate sheet. I've figured out how to count the months, but not the second part. If you need any translations please let me know.

@kowal I've already tried several different itterations of a mtach/index setup, but those didn't work either.

Paul Newcome

Give something like this a try:

=COUNTIFS({Date Range}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2025), {Status Range}, @cell = "Specific Status")

So it looks like you were on the right track already. Were you getting an error message or an unexpected count?

anehn

From the previous one I got an unexpected count, from this one I'm getting #INVALID REF. But it's fine, I apparently won't need it. If I have some time over I'll keep playing auround and let you know if I figure it out.

Edit: Thanks a lot. Earlier I made a mistake, your solution works

Paul Newcome

Happy to help. 👍️