A cross sheet formula that reference back to the main sheet?

Elly

Hi all! I'd be very grateful for your help...

I have a Smartsheet where I would like to have an index match or vlookup formula or similar, which references a secondary sheet. The problem is that the relevant field in the secondary sheet contains a formula that needs to reference back to the main sheet.

I have made up some dummy data to explain this better. Please see the below screenshots of the main sheet and the secondary sheet.

In the main sheet, in the "Wording" column, is where I want an index match or vlookup formula or similar.

The formula should tell Smartsheet to:

1, look at the value in the "Company" column on that row in the main sheet

2, then go to the secondary sheet

3, look for the the correct company

4, take the "Wording formula" on that row

5, and put that into the cell in the "Wording" column on the main sheet.

You can see the "Wording formula" (a rough version, anyway) in the secondary sheet - it references back to the first five columns in the main sheet.

So if I was able to get it working...

1, the result in Ashley's line should read:

"Ashley of Zebra Inc. will be paid a total of $100 upon completion of project Avocado. The team is comprised of Anna, Annette and Andy."

2, the result in Bruce's line should read:

"XYZ Inc. and Yellowstone Inc. have agreed that Bruce will be paid a fee of $200 plus expenses to oversee project Briefcase."

3, the result in Carrie's line should read:

"On the subject of project Chocolate, we the undersigned agree that Xylophone Inc will be paid a fee of $300 in three equal installments. Upon completion, Charlie, Coral and Carl will be entitled to a bonus of $10 and Carrie will be entitled to a bonus of $20."

4, Ivan, who is also with Yellowstone Inc, like Bruce, would have a result that would be very similar, but slightly different, to Bruce's:

"XYZ Inc. and Yellowstone Inc. have agreed that Ivan will be paid a fee of $900 plus expenses to oversee project Icecream."

This is only dummy data and dummy wording. In my real main sheet I currently have over 20 "Companies", and there would be more added in future. The wording is also lengthier.

At present, the wording formula is contained in the main sheet itself. It is an IF formula:

=IF(Company@row = "Zebra Inc", " + Name@row + " of " + Company@row + " will be paid a total of $" + Fee@row + " upon completion of project " + Project@row + ". The team is comprised of " + Team@row + ", IF(Company@row = "Yellowstone Inc", "XYZ Inc. and " + Company@row + " have agreed that " + Name@row + " will be paid a fee of $" + Fee@row + " plus expenses to oversee project " + Project@row + ", IF(Company@row = "Xylophone Inc", On the subject of project " + Project@row + ", we the undersigned agree that " + Company@row + " will be paid a fee of $" + Fee@row + " in three equal installments. Upon completion, " + Team@row + " will be entitled to a bonus of $10 and " + Name@row + " will be entitled to a bonus of $20.", ...etc

The IF formula works. But the reason that I would like to put the individual formula for each individual "company" on the secondary sheet and be referenced by the main sheet is that I am exceeding the character limit in the cell with the IF formula.

Is it possible at all to get Smartsheet to do what I have outlined above?

Thanks in anticipation!

Michael Culley

I don't believe it's possible to achieve what you're trying to do. You're basically saying that you want the formula to compile another formula based on words from a different sheet correct?

I don't see that as a possibility but I could be wrong. Maybe try to think of a different way around this problem. There's no way to shorten the characters on your wording?

Michael Culley

Maybe another creative solution would be to put the different phrases into a separate sheet and then reference those.

For example:

Sheet1 Column A Cell 1 could say "will be paid a total of"

Sheet1 Column A Cell 2 could say "will be entitled to a bonus of"

Then reference those during your big main formula =If(Company@row="Zebra",{wbp)+" $100")

Perhaps that's an idea ?

Elly

@Michael Culley Thanks for your comments! I'll have a think about your suggestion and see whether I might be able to make it work.

In the meantime, if anyone else has any ideas, they would be gratefully accepted!