Cross formula frustration

I have tried every suggestion I've found, I've used AI, and even tried things I knew wouldn't work to get this stupid formula to work.

These are the column names in the target sheet. The data type is number/text, except in the date columns, and those are date types.

These are the column names from the two source sheets. The data type is number/text, except in the date column, and that is a date type.

I want the JournalID from the source sheet 1 to populate in the Journal ID in the target sheet.

Most of the formulas give me anything from #INVALID VALUE to #INVALID DATA TYPE. I have tried index/collect, if statements, a combination of if and index/collect, but the only formula that partically works is

=IF(CONTAINS(PersonID1, {JournalLineDetails_UniqueID}), "Yes", ""), which only half of the criteria

During troubleshooting, I broke up the two pieces, trying to identify the issue, and found

=IF(CONTAINS({JournalLineDetails_JournalID}, {JournalInformation_ID}), "Yes", ""); everytime and in every formula, when I use the {JournalLineDetails_JournalID} reference, I get the #INVALID DATA TYPE. This reference originally pointed to the column JournalID in the source sheet, which is the primary. I created the JournalIDHelper using the formula =JournalID@row and updated the reference to pull from that column, but that didn't work either.

So, the criteria:

If the target_PersonID = the id in source1_UniqueIdentifier AND source1_JournalID = Journal ID in source 2 AND source2_Journal ID = the id in source1_UniqueIdentifier.

Output source1 journalid

I can't send the sheets due to FERPA restrictions; however, the numbers are

journal id = 10 letter/numeric characters

PersonID = 8 numeric characters

UniqueIdentifier = journal id_###_person id

I sure hope someone can help. I've been working on this project for two weeks, and I'm not even close to finishing and my deadline is rapidly approaching.

Thanks - Angelaq

Find more posts tagged with

Accepted answers

All comments

SSFeatures

Hi @perryal, I'm trying to recreate your sheet and come up with a formula.

However, I wanted to double check that this logic is correct:

If the target_PersonID = the id in source1_UniqueIdentifier AND source1_JournalID = Journal ID in source 2 AND source2_Journal ID = the id in source1_UniqueIdentifier.

This logic is doing the following:

Target's PersonID column has Person IDs.
Source1's UniqueIdentifier column matches Target's PersonID, so this column must have PersonIDs
Source1's JournalID column matches Source2's Journal ID, so both of these columns have JournalIDs.
Source2's JournalID matches Source1's UniqueIdentifier column. But this seems weird since Source1's UniqueIdentifier column matched a PersonID. So is it supposed to match both PersonIDs and JournalIDs?

Then output Source1's JournalID.

I'm confused about Source2's JournalID. It seems like it's trying to match both PersonIDs and JournalIDs?

SSFeatures

My thought is that we'll probably want to use JOIN and COLLECT. Here's an example with just the Source1 sheet and the Target sheet.

=JOIN(COLLECT({Source1 JournalID}, {Source1 UniqueIdentifier}, CONTAINS(@cell, UniqueID@row)))

Target Sheet:

Source1 Sheet

The Target sheet is using COLLECT to find the row that matches the same UniqueID value, and then returning the JournalID in that row.

You can put more criterions into the COLLECT function to expand it further. For example:

=JOIN(
  COLLECT(
    {Source1 JournalID}, 
    {Source1 UniqueIdentifier}, 
    CONTAINS(@cell, UniqueID@row)
    {Source2 JournalID}, 
    CONTAINS(@cell, UniqueID@row)
))

Hopefully this helps you in the right direction!

perryal

Hi Nathan,

Instead of giving my logic how about I tell you what I want. :-)

I need the journal ID for the person on the target sheet. Background…

Source1 has hundreds of rows where a person can be associated with many journals. Each row in Source1 has JournalID, Accounting Date, Journal Line Number, Journal Line Amount, PersonID, and UniqueIdentifier. So it looks something like:

SAFIN11111 05/27/25 222 $-16.05 34591586 SAFIN11111_222_34591586

I created a UniqueID column in the source sheet that I upload using DataShuttle, which consists of JournalID_Row#_PersonID.

Target has PersonID, Person Name, Term, Date of Charge, Due Date, and Amount Owed.

34591586 Gator,Albert 2218-Fall2021, 04/01/25, 05/01/25 .15

Source 2 has hundreds of unique rows that have columns for AutoNumber, JournalID, JournalDate, and JournalTotal.

1 SAFIN11111 05/27/25 $-16.05.

I have used cross-sheet references to pull the JournalID from Souce1 to Target but nothing works. When I tried your suggestion, I get #UNPARSEABLE. Here is my formula:

=JOIN(COLLECT({JournalLineDetails_JournalID},{JournalLineDetails_UniqueID},CONTAINS(PersonID@cell,{JournalLineDetails_UniqueID}),{JournalInformation_ID},CONTAINS({JournalLineDetails_JID},{JournalLineDetails_UID})))

Thanks,

Angela

Paul Newcome

So you are manually entering the [PersonID] on the target sheet? And then you want to pull over a compiled list of all cells in the source sheet's [JournalID] column that has that same [PersonID] listed?

=JOIN(COLLECT({Source Sheet Journal ID Column}, {Source Sheet Person ID Column}, @cell = PersonID@row), "delimiter of choice")

If this is not correct, please provide screenshots with sample data.

perryal

Hi Paul,

This information is from my Target sheet

I need to populate the Journal ID from my Source1 sheet

I tried the formula you provided, but I get #UNPARSEABLE.

I need to pull Source1_JournalID to Target_Journal ID

Thanks,

Angela

Paul Newcome

Can you provide a screenshot of the formula open in the sheet as if you are about to edit it?

perryal

I don't want to join anything, I just want to pull the JournalID.

I also have a second source sheet that gives just journal information

I've tried to use that as pass through, but that didn't work either.

perryal

I tried this formula to step through the process; however, I get #INVALID DATA TYPE. The columns in both sheets are text/number, except those that are dates.

{JournalLineDetails_JournalID} is the cross-sheet reference where the JournalID lives and that I want to populate in my target sheet. The column referenced is the Primary, which I thought was the issue, so I updated the reference to the JournaIDHelper column (it is a formula column), but I get the same invalid data type message.

=IF(CONTAINS({JournalLineDetails_JournalID}, {JournalLineDetails_UniqueID}), "Yes", "")

Paul Newcome

Can you wrap this all up in a single post? It looks like you are talking about 3 sheets? 2 source sheets and a target sheet? You mentioned each person could have multiple journals listed in one of the source sheets, but you are wanting to only locate a single specific one for the target sheet? How are we supposed to know exactly which journal id we are to pull if there is more than one associated with each person?

perryal

Target Sheet - this sheet is where we record expenses made by an individual (PersonID - Journal ID)

Source - this sheet is where payment is recorded (JournalID - PersonID)

I want to pull the Source_JournalID into Target_Journal ID.

Both sheets have multiple transactions for any one individual, which is why I created the UniqueIdentifier column. This column is a join of JournalID_RowNumber_PersonID. The UniqueIdentifier field is created before the transactions are loaded into the system. The screenshot is missing leading in that the UID shows the same number multiple times, but I don't have time to create a lot of data. I'm working on this project on my own (with a lot of overtime) and while trying to handle other tasks.

My logic is

If Target_PersonID is in the UniqueIdentifier

AND

The Source_JournalID is in the UniqueIdentifier

THEN

Pull the Source_JournalID into Target_Journal ID

Source2 is a list of the journals. I will use that to pull the date and amount of the journal where Target_Journal ID equals Source2_JournalID. I only mentioned the second source sheet because I thought it might be helpful in some way.

If none of this makes sense, then maybe I should remove my post and start over. :-)

Angela

perryal

@SSFeatures

I went back tried your formula. Here is what I entered:

Now I get #INVALID VALUE, which to me is better than #UNPARSEABLE. At least the formula is pulling something.

I think I'm confused with @cell versus @row. What is the difference? If my columns are the same data type, why won't the value pull through?

Thanks,

Angela

SSFeatures

Hey @perryal, thanks a lot for explaining a lot more details.

Going back to this logic that you mentioned:

My logic is
If Target_PersonID is in the UniqueIdentifier
AND
The Source_JournalID is in the UniqueIdentifier
THEN
Pull the Source_JournalID into Target_Journal ID

This logic is impossible from a logic standpoint. The reason why this is impossible is because a row in the Target sheet does not have a way to identify which Journal it wants. We're trying to find the JournalID by using a PersonID, but this isn't enough information. This would be enough information if there was always only 1 PersonID for each JournalID. But since it's possible for 1 PersonID to correspond to multiple JournalIDs, the row must have more information.

Using this screen shot as an example:

imagine we have a row in the target sheet with the PersonID equal to "12345752". If we want to look this up in the Source sheet, it could correspond to the following rows: (SAFIN13345, SAFIN40794, SAFIN40794, and SAFIN40794). In the programming world, this is sometimes referred to as "ambiguous" logic because there's no way to disambiguate which row it is wanting.

Solution:

The solution is to modify your Target sheet so that it has a column named UniqueIdentifier as well. If you put a unique identifier in the Target sheet, then you can lookup both the JournalID and the PersonID directly from the Source sheet.

For example:

Source1 - has the JournalID, PersonID, and the UniqueIdentifier.

Target: Has the UniqueIdentifier and looks up the PersonID and the JournalID

The formula that I used to lookup the JournalID is:

=JOIN(COLLECT({Source1 JournalID}, {Source1 UniqueIdentifier}, CONTAINS(@cell, UniqueIdentifier@row)))

The formula for PersonID is:

=JOIN(COLLECT({Source1 PersonID}, {Source1 UniqueIdentifier}, CONTAINS(@cell, UniqueIdentifier@row)))

Since the Target sheet has the UniqueIdentifier, we can uniquely identify the exact row that we want in the Source sheet. We can tell exactly which JournalID/PersonID combination we're looking for.

But this only works if UniqueIdentifier is actually unique. I noticed that in your screenshot above, some of the UniqueIdentifiers weren't actually unique:

I hope that this works for you, and I hope my explanation for the logic also makes sense!

perryal

Nathan - Your explanation makes perfect sense. However, I can't fix the issue as you outlined since I don't know the journal ID prior to the formula.

Smartsheet_Cross-sheet formula problem.xlsx

In the attached spreadsheet are the resources I have available. I am able to use different formulas to pull the data; however, I get a #INVALID DATA TYPE. This confuses me because all of the non-date fields are the same data type - Text/Number.

I hope by providing some data, someone can help me.

Thanks,

Angela

Paul Newcome

How exactly are we supposed to know which Journal ID to pull for a person though since you mentioned there could be multiple Journal IDs listed for a single person?

perryal

That's what I'm trying to figure out. I thought if I could look up the person from the target sheet in the source_uniqueid column, and look up the journal from source2 in the source_uniqueid column and match source_journalid to the uniqueid column, I could get the journal number.

The format of the source_uniqueid column is JournalID_Journal Line Number_PersonID

Target_PersonID = Source_UniqueID contains Target_PersonID

And

Source_JournalID = Source2_Journal ID

And

Source2_Journal ID = Source_UniqueID contains Source_JournalID

If we can't find a formula(s) that will work, I'll be forced to look through, currently, 500+ rows, to find each piece of information. 😒 Not my idea of a fun time.

Thanks,

Angela

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.