Formula to populate price based off 3 criteria

I am new to Smartsheet and am moving a large complex excel spreadsheet into Smartsheet. With the formulas being quite different from excel, I am converting them with some success. But this more complicated one has me confused.

I created an example to show what it is I need.

I am new to this community, so if I am not posting this correctly, I apologize in advance.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Your formula would look something along the lines of

=INDEX({RACK3 to ICB7 Columns}, MATCH(MBPS@row, {MBPS Price Column}, 0), MATCH([Pricing Tier]@row + TERM@row, {Top Row}, 0))

KCENTER

Thank you for your suggestion. I surely appreciate it.
When I use this formula I get #INVALID REF. Would I need to be referencing the PRICING Sheet name at all since those are not on the main sheet? RACK3 to ICB7 is within a sheet named PRICING.

=INDEX({RACK3 to ICB7 Columns}, MATCH(MBPS@row , {MBPS Price Column}, 0), MATCH([Pricing Tier]@row + TERM@row , {Top Row}, 0))

Paul Newcome

Yes. Each {Cross Sheet Reference} in the provided formula indicates what to reference within the Pricing sheet. you will need to create each of those cross sheet references using the appropriate steps. This article should help get you started with that part:

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

KCENTER

Paul,
I made the cross reference sheets and my formula looks like below. Yet I am still getting #INVALID REF

=INDEX({PRICING RACK3 TO ICB7}, MATCH(Mbps@row , {PRICING MBPS PRICE}, 0), MATCH([Pricing Tier]@row + TERM@row , {Top Row}, 0))

Is it possibly because my PRICING sheet has several blank rows? I tried to delete them but it won't allow me to save it then.

Thanks again Paul!

Paul Newcome

That error comes from typing in {something here} but not having that set up as an actual cross sheet reference.

Exactly what steps are you taking to create your references?

KCENTER

I go to the cell where I want the result. Click on the formula.

In the { } I click on Reference Another Sheet

I navigate to the PRICING sheet, choose the headers (which highlights the whole column) of RACK3-ICB7. I then rename the range and click insert range. Same step for the MBPS PRICE, only just highlight that entire column. I did rename the sheet reference name to not have any spaces and I read that can be an issue.

Paul Newcome

Try removing the {…..} completely from my provided formula and create from there instead of clicking inside of them.

Note: Spaces shouldn't cause an issue. I use spaces in 99.9% of mine and have never had an issue arise from one.

KCENTER

I removed the reference to the PRICING sheet in your formula and rekeyed it. That didn't fix the error.

I rekeyed the whole formula from scratch, that didn't fix it.

I went to the PRICING sheet and added the header names into the data area so I could grab the header names and the prices with no blank rows. That didn't fix it.

I still get the #INVALID REF

Paul Newcome

It sounds like there may be some latent issues on the back-end. Delete the formula. Delete the column you are putting the formula in. Log out. Clear your browser's cookies and cache. Log back in. Insert a new column. Retype the formula from scratch (no copy/paste) creating the cross sheet references as you go.

If this still does not work, you may need to reach out to support. If you are creating the cross sheet reference properly, that error should not be present. That is the one and only reason that error should populate, so if it is there even though you are following the appropriate steps, something is wrong on the back-end.

KCENTER

I will try that. One question I have about the formula is, the Column in the main sheet called Pricing Tier will have other pricing options in that column. This pricing table ONLY pertains to those that have RACK or ICB as the option in that column.

I really do appreciate all your help.

Here is what my formula in excel looked like:

=IF(D4937="","",IF(D4937="ICB",SUMPRODUCT(PRICING!$E$3:$G$18, (PRICING!$A$3:$A$18=AB4937)(VALUE(LEFT(PRICING!$E$2:$G$2, FIND(" ",PRICING!$E$2:$G$2)))=AC4937)), IF(D4937="RACK",SUMPRODUCT(PRICING!$B$3:$D$18, (PRICING!$A$3:$A$18=AB4937)(VALUE(LEFT(PRICING!$B$2:$D$2, FIND(" ",PRICING!$B$2:$D$2)))=AC4937)))))

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.