Auto Populating Info To Sheet

Hi All!

I was able to have a column auto populate data into a column on a sheet I needed and am for some reason unable to duplicate…. Any ideas?

Above: By putting in the REQ # in the column it would automatically populate the PO# in the next column going off my REQ Form sheet

Above: Trying to do the same formula on this sheet but it's not working…

Thank you!

Find more posts tagged with

Accepted answers

All comments

jmyzk_cloudsmart_jp

Hi @Joe Phelps

You most likely have a formula like this;

=IFERROR(INDEX({REQ  PO : PO #}, MATCH([REQ #]@row, {REQ  PO : REQ #}, 0)), "")

The {REQ PO : PO #} is a cross-sheet reference you need to create manually.

In the INDEX() formula, you need to set the range.

Click the 'Reference Another Sheet.'

Look for your reference sheet, specify the range, and name it.

Do the same for the MATCH function's range.

An easier way is to 'Save as New.'

Make sure in the Sheet data and formatting, 'Cell links and cross-sheet references' is checked.

Joe Phelps

Thanks @jmyzk_cloudsmart_jp the formula I have is:

=IFERROR(INDEX(COLLECT({REQ Form Range}, {REQ #}, HAS([REQ #]@row , @cell )), 1), "")

This is pulling the REQ # and the PO# off of my main req form sheet:

REQ #

PO #

Joe Phelps

@jmyzk_cloudsmart_jp So I have to reference the req form sheet again but do i reference the column on the sheet or the whole sheet? Thank you very much!!

jmyzk_cloudsmart_jp

@Joe Phelps

I updated the demo sheet to use the same structure formula as yours.

=INDEX(COLLECT({REQ  PO : PO #}, {REQ  PO : REQ #}, HAS(@cell, [REQ #]@row)), 1)

https://app.smartsheet.com/b/publish?EQBCT=150b2177df3a4bb89e816e676a6b9600

The {REQ PO : PO #} is the range whose value you want to collect.

So, you need to select the entire column, not the sheet, for the range.

To collect the PO, you use the {REQ PO : REQ #} range as the condition. The HAS function checks if the range (@cell) has the [REQ #]@row value, and the whole COLLECTION formula returns a range that meets the condition.

HAS(@cell , [REQ #]@row )

So, select the entire REQ # column for the range.

In my HAS formula, I put @cell first, because the HAS function is used to check if some value is in the multi-select cell values.

For example, if the REQ column is like the image below, the HAS function expects the range like [17015,
16878], and checks if the range has 17015.

The image below shows a typical use of the HAS function. Note, since the fifth row has both 16874 and

16875, the 6271 - Automatic … are returned in both 5th and 6th rows.

But, I do not think you expect such a reference. In that case, you do not have to use the HAS function. You can simply put [REQ #]@row as the range {REQ PO : REQ #}'s condition.

=INDEX(COLLECT({REQ  PO : PO #}, {REQ  PO : REQ #}, [REQ #]@row), 1)

Joe Phelps

@jmyzk_cloudsmart_jp Thank you, I'm trying to get the req # and po # to reference a different sheet not this one and it keeps saying unsparesable… ?

jmyzk_cloudsmart_jp

@Joe Phelps

The #UNPARSEABLE error usually means your formula is somehow wrong. If your references are bad, you get the #INVALID REF error.

Is this your formula?

=IFERROR(INDEX(COLLECT({REQ Form Range}, {REQ #}, HAS([REQ #]@row , @cell )), 1), "")

The formula itself looks ok except that we place a range, @cell, in front of the criteria, [REQ #]@row in the HAS function, as explained above.

As I wrote above, you do not need to use the HAS function here.

=IFERROR(INDEX(COLLECT({REQ Form Range}, {REQ #}, [REQ #]@row), 1), "")

https://help.smartsheet.com/articles/2476176-formula-error-messages#toc-unparseable

#UNPARSEABLE
Cause
The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.
Resolution
Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (" ").

#INVALID REF
Cause
A reference name to another sheet (in curly braces) doesn’t exist as a reference to another sheet.
Resolution
Either change the name within the curly braces to be that of an already created reference to another sheet, or create a new reference to another sheet.
Learn more about referencing data from other sheets for more information on this process.

Joe Phelps

Thank you, this is my formula below and it still won't pull the PO number from my REQ form sheet into the cell… ?? @jmyzk_cloudsmart_jp

jmyzk_cloudsmart_jp

I don’t see any syntax errors in your Smartsheet formula.

Make sure {REQ Form Range} and {REQ #} are correct cross-sheet references pointing to the same sheet and range sizes.

If possible, create a copy of those two sheets and share them with me at jmiyazaki@cloudsmart.jp.

Joe Phelps

@jmyzk_cloudsmart_jp Thank you for your help, it still isnt workin on my side I'm getting incorrect argument set…

Joe Phelps

@jmyzk_cloudsmart_jp Good morning! I just shared both sheets with you, thank you!

jmyzk_cloudsmart_jp

Hi @Joe Phelps

Thank you for sharing the sheet. Next time you share the sheets with someone outside your organization, ensure you make copies and delete all columns unrelated to the issue.

In the shared sheet, I found the following formula;

Problems in the Current INDEX MATCH Formula

First, no need for HAS.

HAS is only for multi-select cell checks. Here, MATCH should directly compare [REQ #]@row with {REQ PO: PO #}.

=IFERROR(INDEX({REQ PO: REQ #}, MATCH({REQ  PO : PO #} HAS(@cell  , [REQ #]@row  ))))

Wrong column in INDEX()
- The first argument of INDEX() should be the column you want returned (PO #).
- The current formula uses {REQ PO: REQ #}, which points to the wrong column.
Mismatch between MATCH() and lookup column
- MATCH() should search [REQ #]@row against the REQ # column.
- The formula incorrectly references {REQ PO: PO #}, which makes no sense as the lookup column.
Cross-sheet mismatch
- The sheet being referenced has a REQ # column but no PO # column.
- If INDEX() is pulling from a third sheet, it will fail because both INDEX() and MATCH() ranges must come from the same sheet.
Missing row index in INDEX()
- INDEX() requires a row index argument.
Missing error return value in IFERROR()
- Needs a value for errors (e.g., "").
MATCH() missing exact match argument
- Should specify 0 to enforce an exact match.

Business Logic Issue

The REQ # is most likely an Auto-Number from the request form sheet.
There is no way to auto-populate the PO # in that same form sheet, because the purchase order only exists after approval and procurement.
In practice:
- A user submits a request → sheet generates REQ #.
- If the request is handled in the same sheet, a purchase manager manually enters the PO # once the purchase is made.
- If the purchasing process is handled elsewhere (e.g., in a dedicated purchase sheet), then:
  - The request row is copied/moved to that purchase sheet.
  - The purchase manager inputs the PO # there,
  - That sheet becomes the correct place to reference the REQ # ↔ PO # mapping.
Therefore, trying to pull PO # directly from the form-populated request sheet doesn’t work technically (no column) and doesn’t work logically (no business process to populate it there).

Corrected Formula (if both columns exist in the same purchase sheet)

=IFERROR(
  INDEX({PurchaseSheet: PO #},
         MATCH([REQ #]@row , {PurchaseSheet: REQ #}, 0)  ),  "")

or in the demo sheet
=IFERROR(INDEX({REQ Form : PO #}, 
        MATCH([REQ #]@row, {REQ Form : REQ #}, 0)), "")

https://app.smartsheet.com/b/publish?EQBCT=ba2b21c2f1654ae183bc8e4aacbcad54

https://app.smartsheet.com/b/publish?EQBCT=6e3a81bca64248a78d652b53d675a40d

Another tricky issue I encountered examining your sheet is the format of PO # and REQ #.

As the image shows, when I checked the format of the REQ # using the ISTEXT and ISNUMBER functions, it turned out that the supposed number is in the TEXT format.

So, in that case, if the referencing range is a TEXT range, we need to convert the criteria by converting it to text, with a formula technique like MATCH([Number column]@row + "", {Text Range},0).

In the shared sheet, I also found another INDEX COLLECT formula.

Second formula (auto-populated, not a column formula)

=IFERROR(INDEX(COLLECT({REQ Form Range}, {REQ #}, [REQ #]@row ), 1), "")

Structure is fine: COLLECT(return_range, criteria_range, criteria) → INDEX(...,1) → IFERROR(...,"").
But verify ranges:
- {REQ Form Range} must be a single column on the same sheet as {REQ #} (the criteria range), and both must have the same number of rows.
- Use explicit column references to avoid ambiguity, e.g.:

=IFERROR(
  INDEX(COLLECT({REQ Form : PO #}, {REQ Form : REQ #}, [REQ #]@row), 1), "")

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.