Help figuring out which formula to use

lm56 · 11/17/21

Hello! I am fairly new to using Smartsheet and need help figuring out if what I'd like to do is even possible and what formula I would use (would it be index/match?). I would like to pull information from one sheet to another sheet. What I am trying to come up with is:

IF the "PRINTER" ON PRINTER LEAD TIMES sheet is the same "PRINTER" ON the PACKAGING FLATS TRACKER, then in the RELEASE TO SGS/PRINTER column on the PACKAGING FLATS sheet, SUBTRACT the number that is in the TOTAL NUMBER OF DAYS column on the PRINTER LEAD TIMES sheet FROM the date that is in the MATERIALS IN HOUSE column on the PACKAGING FLATS sheet.

Does this make sense and is this doable? Right now, I have a long IF statement on just the PACKAGING FLATS TRACKER but I think having a separate sheet where this information is pulled from might be easier when information changes/people need to update.

Screen Shot 2021-11-17 at 4.39.04 PM.png

Screen Shot 2021-10-25 at 3.53.04 PM.png

Thank you!

Paul Newcome · 03/23/22

You need to actually use "@cell"

=MAX(COLLECT({Total Number Of Days}, {Printer}, HAS([email protected],@cell)))

Paul Newcome · 11/18/21

What about on rows where you have multiple entries in a single cell? Would you want to pull the highest number of days for the two entries or would you want to add the number of days together for both entries?

lm56 · 11/18/21

Hi@Paul Newcome- I would want to pull the highest number of days for the 2 entries. Thank you!

Paul Newcome · 11/18/21

In that case you are going to want a MAX/COLLECT/HAS combo to be able to pull the number of days. Something along the lines of...

=MAX(COLLECT({Total Number Of Days}, {Printer}, HAS([email protected], @cell)))

lm56 · 03/17/22

@Paul Newcome- how do I reference the sheet that I want to pull the information from?

Paul Newcome · 03/18/22

When you get to the section where you want to create the cross sheet reference, there should be a blue link in the "formula help box" that says something along the lines of "Reference Another Sheet". Click on that link, select the appropriate sheet, then click on the column header to select the entire column.

lm56 · 03/21/22

Hi@Paul Newcome- thank you! I get "invalid operation" - can you take a look at the formula I have and let me know what I'm doing wrong? Can you also let me know if it's possible to subtract a number of days from the reference sheet from a date i have in the 2nd sheet? If that's not possible, how could I just have the number of days from the reference sheet pull into a column on the 2nd sheet?

Screen Shot 2022-03-21 at 3.13.26 PM.png

Paul Newcome · 03/21/22

Make sure you include the second portion of the HAS function with the @cell reference.

lm56 · 03/22/22

@Paul Newcome- I am getting INVALID REF. What am I doing wrong? I've created a separate column on the Packaging Flats Tracker to pull in the total number of days from the Printer Lead Times sheet. Is there maybe a simpler function I could use?

Screen Shot 2022-03-22 at 3.42.34 PM.png

Paul Newcome · 03/23/22

You need to actually use "@cell"

=MAX(COLLECT({Total Number Of Days}, {Printer}, HAS([email protected],@cell)))

lm56 · 03/23/22

Thanks so much!

Paul Newcome · 03/24/22

Happy to help.️

Help figuring out which formula to use

Best Answer

Answers

Help Article Resources

Categories