Can I Cross Reference a sheet in client account to a sheet in my account?

I'm trying to enter the following formula

=SUMIFS({Barcode packing lists Range 1}, {Barcode packing lists Range 2}, [PIckup Date]25, {Barcode packing lists Range 3}, [KIT# / SKU#]25, {Barcode packing lists Range 5}, "Green")

where the sheet "Barcode packing lists" is under my Smartsheet Account and the formula is on a sheet under my client's Smartsheet account.

I get the result #INVALID REF

Is there a problem with the formula, or can you only reference sheet within the same account?

Find more posts tagged with

Formulas

Accepted answers

sean59916

https://community.smartsheet.com/discussion/69718/can-i-cross-reference-a-sheet-in-client-account-to-a-sheet-in-my-account

Hi @Sam Knight

Yes, you can. The issue that you are most likely facing (as I have too!!) - is when you record a barcode, it records it with a ' appended to the front so it looks like text. The SUMIF(s) function doesn't appear to work for these type of text strings.

To fix, I created some "Helper" columns with the formula =VALUE(Barcode@row) - Do this on the source & target sheets - your SUMIF(s) should now work :)

I hope this helps you out.

Sean Williams.

All comments

Andrée Starå

Hi Sam,

As long as you can select the range and create the reference, it should work.

Each sheet has to have the reference created because it’s unique for each sheet.

Make sense?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

sean59916

https://community.smartsheet.com/discussion/69718/can-i-cross-reference-a-sheet-in-client-account-to-a-sheet-in-my-account

Hi @Sam Knight

To fix, I created some "Helper" columns with the formula =VALUE(Barcode@row) - Do this on the source & target sheets - your SUMIF(s) should now work :)

I hope this helps you out.

Sean Williams.

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.