output data from another reference sheet

WinaHath

thanks, Paul!

this is my Main output Sheet

this is the reference sheet: the top section use [project codes] and the bottom (highlighted) use [Dept Codes]

I need to get the email address from the reference sheet for the dept or project approver if their signing authority is greater than $3000 or greater than $4000 and output them to either Final Approver Provost or CFO.

Thank you!

WinaHath

@Paul Newcome

what is wrong with this formula?

=IFERROR(INDEX(COLLECT({Approvers email}, {Dept Code}, @cell = [Dept Code]@row, {Authorized Limit}, =2999.99), 1), "")

Paul Newcome

@WinaHathYou need to provide more details. Are you getting an error message on the formula? I don't see any syntax issues.

Paul Newcome

What are you getting when you remove the IFERROR?

=INDEX(COLLECT({Approvers email}, {Dept Code}, @cell = [Dept Code]@row, {Authorized Limit}, =2999.99), 1)

WinaHath

@Paul Newcome

#invalid value

Paul Newcome

It looks like you have a mix of numbers and text in your Dept Code column. You are going to need consistent data types. Insert a text/number column along with this column formula:

=[Dept Code]@row + ""

Do that on both sheets and then reference this column instead.

WinaHath

@Paul Newcome

Paul,

That didn't work...

WinaHath

@Paul Newcome

️

Referenced Sheet - Approval Tracks - both Approver and Approvers Email are contact lists. The email column is a formula, matching the Approver with the User Sheet

thank you for evaluating my issue.

Every time something changes, the formulas stop working. Also when it did work, it will only pull from some departments but not for all even if the data and the formula are consistent.

All I am trying to achieve are:

• match the approver in the sequence with the selected department code so I can run the automated approval workflow
• match the authorized amount limit with the approver in the sequence if <> the amount is true

I have been given a final deadline and if it still doesn't work, we will be looking at a different software when our term with smartsheet expires.

this is not my only issue, the biggest one is the approval workflow. Unfortunately, leadership will not upgrade to Enterprise if the Pro Plan can't even provide us what we need. I am the only one working on creating the workspaces, and I am obviously not an expert.

I've lost my credibility because I keep having issues. any help will be much appreciated! Have a great weekend!#formulaissue

Paul Newcome

You need that helper column on both sheets and use those instead of the regular columns.