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

Find more posts tagged with

Formulas

Accepted answers

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?

All comments

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.

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.