Multiple Sheets & Formula Updates

Mike Rini

I am using Smart Sheets for Paid Search requests. So, when an AE submits a request for their client, we ask them for specific URLs to be entered. In the sheet where they enter in their URLs (via an Update Request), there are some formulas (vlookups) that we use. For the most part, those work fine, but there are times when I need to go in and update the formulas - either because I forgot something or a new workflow presented itself.

Here are the two challenges:

1. There are nearly 200 clients. I've set them up as separate sheets because they are technically separate clients. That's a management nightmare for me. I was thinking about putting all the clients into one sheet, and then breaking them out individually using a Report.
2. I'm using the FIND() function for one of my formulas, and it works...mostly. There are instances where it returns an invalid result because it's looking for just a value to be present, not the exact phrase. An example would be - If my formula was looking for "Search", but the value in the other cell was " Paid Search" - using the FIND() function, it returns the position because it's essentially finding part of the value.

Two questions - For #1 - there has to be a better way (I think) because the maintenance of this is painful. For #2 - is there a different way to use FIND() or a different formula that's better?

Ben Goldblatt

Hi Mike,

For #1, Reports would allow you to consolidate information across multiple sheets into one place but I'm wondering if something like the Smartsheet Control Center would come in handy for managing all of your sheet data. Control Center can help with planning, capturing, managing, automating, and reporting on data and offers the ability to link project assets together. Another Control Center feature that may be beneficial is the ability to perform global updates across multiple sheets at one time. It can't hurt to reach out to our Sales Team for more information on this and to see if they can run a live demonstration for you before deicing whether or not Control Center will fit well with your business needs.

For #2, you may be able to leverage the CONTAINS function for partial values or the HAS function to look for exact matches. Feel free to provide a sample of your current formula and maybe some associated screenshots (with any personal or sensitive data blocked out or omitted) showing the values you're searching for and we'll see if we can help to come up with some formula examples.

I hope this helps!

Thanks,

Ben

Mike Rini

Thank you. The HAS function worked great - I've never used that before. I don't think we have access to control center, but for now, I managed