Right and MID formula to pull from details sheet

Linda F

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

Genevieve P.

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?

MCorbin

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

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.

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

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