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

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

@WinaHathYour 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:

{审批人邮件},{部门代码}=(部门代码)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

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

output data from another reference sheet

Best Answer

Answers

Help Article Resources

Categories