Right and MID formula to pull from details sheet

Linda F
Linda F ✭✭✭✭✭
edited 08/26/21 in Formulas and Functions

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:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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?

Answers

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

  • Linda F
    Linda F ✭✭✭✭✭
    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.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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?

  • Linda F
    Linda F ✭✭✭✭✭

    @Genevieve P. I was able to create a cross-compare 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!