Search Adjacent Row

I want a formula in a cell that searches the adjacent cell with its drop downs and then it would populate with my other referenced quantity cells elsewhere.

Cell is UNIT that I select from a drop down list: S.F. Bldg., Weeks, Months, Other

I want this formula in my cell at adjacent row of REF. QTY. to read cell UNIT and go get the numbers I populated once up at the top of my sheet in the same named column. And for the dop down selected Other, I wanted just to insert the number 1 that is typed in the formula already.

I had this working before in another sheet and somehow I accidentally deleted the formula and now I can't get the syntax right for doing more than one IF in the list. All the searches I find for this have variations of bracket types and ways of doing this. I keep getting an error showing #UNPARSEABLE.

I'm probably missing something painfully obvious but I need other eyes on this. Thanks for your help.

=IF(Unit@row = "S.F. Bldg.", $Ref. Qty.$78,

IF(Unit row = "Months", $Ref. Qty.$76,

IF(Unit@row = "Weeks", $Ref. Qty.$77,

IF(Unit@row = "Other", "1",))))

Find more posts tagged with

Formulas

Accepted answers

All comments

JYokoi

@FORM64 You have a comma after the "1". Also, you do not need to have the quotations around the "1" unless you are wanting it to come in as a text rather than a number.

FORM64

Thanks.

Removing the comma makes no difference, same error. And I do want it to come in as text because I will put some default text in like "N/A" in some other items to be in the drop down. I shortened the drop down for this example.

JYokoi

I would try completely retyping the formula. I tried copying your formula and replacing the correct cells from my sample sheet and it would give me the same error. But once I just typed out the formula again, it worked.

FORM64

Still not working. Perhaps I'm eliminating or adding spaces but I can't tell where yours has them or not.

Are you able to paste your code in directly into a reply so that I can see exactly where the spaces are or not?

And any tips on using an outside editor to be able to copy and paste from? The silly little box within Smartsheet is ridiculous to see and keep track of a huge list the way we can with a code editor or even Notepad.

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.