# Right and MID formula to pull from details sheet

✭✭✭✭✭
edited 08/26/21

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

Tags:

• Employee

Hi @Linda F

For the first formula it sounds like you're looking for a cross-sheet 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 cross-sheet references looking into your actuals sheet. [These] are in-sheet 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 cross-compare the Account and Budget Category?

October 8 - 10, Seattle, WA | Register now

• Overachievers Alumni

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

• ✭✭✭✭✭
edited 10/20/21

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.

• Employee

Hi @Linda F

For the first formula it sounds like you're looking for a cross-sheet 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 cross-sheet references looking into your actuals sheet. [These] are in-sheet 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 cross-compare the Account and Budget Category?