output data from another reference sheet

WinaHath

I need help in generating a formula for this scenario, please.

Our purchase requisitions have to be approved by multiple persons and since we have over 662 department codes it will be awesome if Smartsheet can match the approvers with the department codes and the approval sequence.

I need help in generating a formula for this scenario, please.

when the Department code in the Requisition Sheet changes to Dept Code xxxx , then find and reference sheet Approval Tracks to output the approver name for approver sequence 1 to the Approver 1 column.

Requisition Sheet:

Approval tracks sheet

Paul Newcome

You would use an IFERROR statement.

=IFERROR(original_formula, "")

parulmishra

If my understanding is correct you can do something like this for Approver 1 column

=INDEX(COLLECT({Approved by}, {Department}, [Department code]@row, {Approval Sequence}, 1, 0))

for Approver 2 column

=INDEX(COLLECT({Approved by}, {Department}, [Department code]@row, {Approval Sequence}, 2, 0)) and so on

WinaHath

#incorrect argument

=INDEX(COLLECT({Approved By}, {Department Code}, [Department Code]@row, {Approval Sequence}, 2, 0))

when I Reference Another Sheet, do I just select the column that contains the data or the whole reference sheet?

Erin Kim

I select the column containing the data I am pulling/referencing

WinaHath

I am still getting #incorrect argument

WinaHath

will this formula work even if Department Code is a formula as well?

WinaHath

@parulmishra- I get an error message #incorrect argument

Please help

Paul Newcome

It looks like some syntax errors. Try this:

=INDEX(COLLECT({Approved By}, {Department Code}, @cell = [Department Code]@row, {Approval Sequence}, @cell = 2), 1)

You would ONLY change the 2 to 1 or 3 or whichever sequence number. The 1 at the end should stay as 1.

WinaHath

thank you so much@Paul Newcome! what do I add to this formula so that if there is no Dept Code in the column instead of getting and #invalid value error message it will just be blank?

Paul Newcome

You would use an IFERROR statement.

=IFERROR(original_formula, "")

WinaHath

THANK YOU again!!

WinaHath

=IFERROR(INDEX(COLLECT({Approved By}, {Department Code}, @cell = [Department Code]@row, {Approval Sequence}, @cell = 3), 1) "") ???

parulmishra

@WinaHathgood to see the problem got resolved :) for iferror

=IFERROR(INDEX(COLLECT({Approved By}, {Department Code}, @cell = [Department Code]@row, {Approval Sequence}, @cell = 2), 1),"")

Paul Newcome

@WinaHathYour last formula looks right to pull in the third in sequence and leave blank if there is none.