Hi, My Cross Sheet formula =IF( is displaying the same results when I drag formula down dest column

Hi, When using the following cross sheet formula (see below) it appears to pull the data correctly from the source sheet (top pic) into the destination sheet (bottom pic), however when I drag the formula down it populates the same information - I'm using the Equipment Name as an example. As you can see in the source sheet the Equipment Name is not always "10 ml Luer Lock TEST". So in the Cat. No. of the destination sheet I tried highlighting the entire column when referencing the source and I get an error, but when I only reference just the one cell in the source sheet the data pulls over correctly into destination sheet as seen in Equipment Name, Brand and Model.

=IF({Inventory Master Sheet Range 1} = "consumable", {Inventory Master Sheet Range 2})

Also not sure what to use at the end of my formula for FALSE as I'm only wanting to pull info from the source sheet with Category of "consumable" otherwise if false leave blank I guess ""?

Any assistance would be greatly appreciated, as I'm just getting into learning SS.

Find more posts tagged with

Accepted answers

Paul Newcome

Ok. SO take the columns that are NOT highlighted, add them to the Master Sheet, then hide them.

Then create a new Report that looks at the Master Sheet. In the "What?" section, select the Category column and then enter a criteria of being "consumable". Then click on the "Columns" button in the Report Builder and select whatever columns you want displayed to include the columns hidden on the Master Sheet.

Paul Newcome

Happy to help. 👍️

All comments

John Ciarrochi

Here is a snippet of the Master Sheet, in the Category column I'm only needing the ones that indicate "consumable".

Paul Newcome

No. I mean add all of the extra columns that are solely on the Consumables sheet to the Master sheet. Hide them on the Master sheet. Create a report based on the Category column containing "consumable". Then you can pick and choose which columns are shown in the report even if they are hidden on the Master. Using this route, the Report replaces the Consumables sheet.

John Ciarrochi

Here is a snippet of the Consumable sheet, the highlighted columns indicates the columns that mirror the Master sheet and I would like to be pulled into this sheet automatically.

Paul Newcome

Ok. SO take the columns that are NOT highlighted, add them to the Master Sheet, then hide them.

John Ciarrochi

Hello Paul,

Thanks for your insight, I will certainly contemplate that course of action with the user.

Paul Newcome

Happy to help. 👍️

John Ciarrochi

That is a great alternative, however I'm still curious to why my cross reference formula only worked when selecting referencing a single cell and got an error when trying to reference the entire column?

Paul Newcome

Both ranges need to be of the same size. So if one is a single cell and the other is a full column, you will get an error.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.