Help figuring out which formula to use

lm56

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.

Thank you!

Paul Newcome

You need to actually use "@cell"

=MAX(COLLECT({Total Number Of Days}, {Printer}, HAS(Printer@row, @cell)))

Paul Newcome

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

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

Paul Newcome

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(Printer@row, @cell)))

lm56

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

Paul Newcome

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

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?

Paul Newcome

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

lm56

@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

You need to actually use "@cell"

=MAX(COLLECT({Total Number Of Days}, {Printer}, HAS(Printer@row, @cell)))

lm56

Thanks so much!

Paul Newcome

Happy to help. 👍️