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?
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?

@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
 10.6K Get Help
 63 Global Discussions
 46 Industry Talk
 387 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!