Logic formula to return a value on another sheet based on two search criteria

sarahm6

I am trying to develop a formula that will return a request ID # to an invoicing sheet.

The source sheet includes the request ID # and two columns with the criteria I want to be met (Lot Charge Fee, Ready to Invoice). I want the request ID # to only show up on the invoicing sheet if the following criteria is met:

• Lot Charge Fee = must have a value
• Ready to Invoice = must be checked

I'll then pull in other data from the source sheet into the invoicing sheet based on the request ID #.

The goal is to not pull in any rows that are not ready for invoicing or that do not require invoicing.

I've gotten an IF/AND statement work, however this does not seem to work on a cross-sheet basis, the formula only worked when referencing cells on the same sheet. INDEX/COLLECT may also work but I cannot figure out how.

Thanks for any help!

KPH

You are correct in thinking INDEX/COLLECT will return rows where the two search criteria are met. The INDEX will be the request ID and the COLLECT will collect rows that meet the criteria. However, you need something on the sheet you are completing to indicate which matching rows to show.

It does not sound like that is what you need. I believe your second sheet is blank and you just need a list of rows to be invoiced. I think a report with two filters would achieve what you want.

https://help.smartsheet.com/learning-track/level-1-foundations/reports

KPH

You are correct in thinking INDEX/COLLECT will return rows where the two search criteria are met. The INDEX will be the request ID and the COLLECT will collect rows that meet the criteria. However, you need something on the sheet you are completing to indicate which matching rows to show.

It does not sound like that is what you need. I believe your second sheet is blank and you just need a list of rows to be invoiced. I think a report with two filters would achieve what you want.

https://help.smartsheet.com/learning-track/level-1-foundations/reports

sarahm6

Ah, reports! That is exactly what I needed, way simpler. Thanks for your suggestion and answer.

KPH

Glad I could help - always take the simple option 😉