Right and MID formula to pull from details sheet
I have imported my excel budget and details sheets into Smartsheet and unfortunately my formulas don't import correctly.
I have struggled with rebuilding my formulas from excel to Smartsheet and that is the case with the below:
=RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),5)
How do I rewrite this in Smartsheet so it pulls in my data from the details sheet?
Below is the other formula I can't figure out  pulling from same detail sheet:
=IF(ISERROR(VLOOKUP(($B$2&""&$D$2),COA!$B$4:$N$6608,10,FALSE)),0,VLOOKUP(($B$2&""&$D$2),COA!$B$4:$N$6608,10,FALSE))
Best Answer

Hi @Linda F
For the first formula it sounds like you're looking for a crosssheet SUMIFS formula, is that the one you've figured out already?
It would be something like this:
=SUMIFS({Budget Column}, {Account Column}, Account@row, {S/A Column}, [S/A]@row)
{These} would be crosssheet references looking into your actuals sheet. [These] are insheet references to your Account and S/A values.
For your second formula with RIGHT(MID etc, I'm not quite sure what it is you're looking to do. How are you creating the S/A? Do you have a reference sheet where we can crosscompare the Account and Budget Category?
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Answers

It would be helpful to see a sample of the sheet (with column names and some sample data). And a description of what you're trying to do with each formula...

2 different formulas.
I am trying to pull the totals from the Revised Operating Budget column based on the Account and S/A pulling from both columns to give me grand total for that budget column. It will look at both columns to give me totals only for that account number and S/A columns specifically. Out of thousands of entries, I only want to pull in a specific account w/ sub account totals so it adds all those entries into 1 grand total.
I was able to figure out the 2nd formula already.
The below formula is to pull in the correct S/A number for an account number based on the "Budget Category. There are many account, S/A 'S for a Budget Category.
It's =RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),5) that is harder.

Hi @Linda F
For the first formula it sounds like you're looking for a crosssheet SUMIFS formula, is that the one you've figured out already?
It would be something like this:
=SUMIFS({Budget Column}, {Account Column}, Account@row, {S/A Column}, [S/A]@row)
{These} would be crosssheet references looking into your actuals sheet. [These] are insheet references to your Account and S/A values.
For your second formula with RIGHT(MID etc, I'm not quite sure what it is you're looking to do. How are you creating the S/A? Do you have a reference sheet where we can crosscompare the Account and Budget Category?
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
@Genevieve P. I was able to create a crosscompare formula but had to set up my reference sheet appropriately. The table was so big that I had to limit the data I was trying to pull in and set up my formulas based on Account/SA # first then by division, dept, etc, ; the by budget category. It was challenging but mapping it out helped me figure out my formula. Thank you.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 430 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 284 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!