output data from another reference sheet

WinaHath · 06/06/23

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 · 06/07/23

You would use an IFERROR statement.

=IFERROR(original_formula, "")

parulmishra · 06/07/23

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 · 06/07/23

#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 · 06/07/23

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

WinaHath · 06/07/23

I am still getting #incorrect argument

WinaHath · 06/07/23

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

WinaHath · 06/07/23

@parulmishra - I get an error message #incorrect argument

Please help

Paul Newcome · 06/07/23

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 · 06/07/23

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 · 06/07/23

You would use an IFERROR statement.

=IFERROR(original_formula, "")

WinaHath · 06/07/23

THANK YOU again!!😇

WinaHath · 06/07/23

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

parulmishra · 06/08/23

@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 · 06/08/23

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

WinaHath · 07/11/23

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 · 07/11/23

@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?

Formulas and Functions

Formulas and Functions

output data from another reference sheet

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Formula - use % complete to determine whether in progress

Formula error

TRYING TO FIND THE ERROR IN THIS FORMULA