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

@WinaHath good 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

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

WinaHath

please help me figure out this formula (I did try but I obviously don't know what I'm doing!)

I want to COLLECT the approver email from another sheet with the following to conditions:

{Approver Email}, {Department Code} = [Dept Code] or [Project Code] and the Approver's {authorized limit} is greater than or equal to 3000 or greater than or equal to 5000

the reference sheet contains the dept code and project code in one column but they are separate columns in the output sheet since the approval tracks differ by departments😟

Thank you!

Paul Newcome

@WinaHath I just noticed that your formula from 7 June is missing a comma there towards the end.

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

I am not sure I understand your latest question though. Are you able to provide a screenshot and more details?