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

John Ciarrochi

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.

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. 👍️

David Joyeuse

Hi @John Ciarrochi

It's OK not to have false argument within an IF function, it'll leave the cell blank if statement is false. So it's up to you to determine what you want to display if the argument is false.

Now, if your IF function is what you used in the Equipment Name cell of the second sheet, then it has to return the same value, because I believe your ranges {Inventory Master Sheet Range 1} & {Inventory Master Sheet Range 2}. Thus dragging down the formula will always return the same thing as ranges won't change.

From what I understand of both your sheet, the first one is used to order and what you're gonna need in your projects, while the second one (the one with your formulas) will be used mostly as a database of all the inventory (name, type, brand...).

If that's correct, I believe you'll have to do it the other around. Having formulas in the first sheet and adding items in the second one.

If not, what is the purpose of those two sheets and which formulas have you entered in:

• Equipement Name
• Brand
• Model
• Cat. No.

Thanks!

John Ciarrochi

Hello David,

Thanks for your response.

The first sheet is the Master Inventory sheet where the second sheet is the Consumable sheet, I want to automatically populate certain fields from the Source sheet AKA Master Inventory into the destination sheet AKA Consumable so we don't have to re-type all the information in the Consumable sheet. The picture above showcases me selecting the entire column as the reference as opposed to a single cell so when I do that the formula doesn't work at all I get the error seen in the Cat. No. column, however when I choose just a single cell the formula works as seen in Equipment, Brand and Model but doesn't allow me to drag it down.

Below is the formula I used to select the entire Column in which receives an INVALID OPERATION

=IF({TEST Inventory Master Sheet_JC Range 3} = "consumable", {TEST Inventory Master Sheet_JC Range 5})

I'm confused as to why the formula doesn't work when I select the entire column as my reference.

Thank you sir.

David Joyeuse

@John Ciarrochi

Formula doesn't work because on each row it starts checking with the first cell of the range, which is consumable, thus true, and return the first cell of the second range.

What you're trying to do here requires some cell linking between the two columns Equipment Name. But the easiest way would be to just copy/paste the full column from the first to the second sheet, then use some INDEX/MATCH formula to populate the remaining of the sheet.

=INDEX({TEST Inventory Master Sheet_XXX}, MATCH([Equipement Name]@row, {Master Sheet Equipement Name column})

This will work better.

Another way to not have to retype everything is to have some automation from your master sheet to copy rows onto the second one each time you add a new row in the master sheet. Then hide the columns you don't need on the second sheet.

Hope it helped!

John Ciarrochi

Thank you David. However is the INDEX/MATCH simply a one for one match? , what I mean is I only want to copy the Equipment Names, Brand, Model etc. that have a Category of "consumable" . This is why I thought the =IF( statement would define the criteria of just "consumable" I don't need to copy over any other category besides "consumable".

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

Can you set criteria's for INDEX/MATCH?

Thank you!

John C.

David Joyeuse

David Joyeuse

INDEX/MATCH will only works if there's a unique value common to both sheets otherwise it won't be reliable at all.

If you want to do it with consumables, it'll require some advance use of the COLLECT or DISTINCT function here. I know @Paul Newcome got a formula or two that could help here way better than me if you want to collect all the consumable items from sheet one and have them all in separate rows.

There were questions about this not so long ago, so they could help you as well.

John Ciarrochi

David,

Very insightful information and thanks for taking the time to respond to my post, any help @Paul Newcome can provide would be greatly appreciated.

John C~

Paul Newcome

I'll take a read through this thread and see if I can help. I have a pretty full day today, but I wanted to let you know I wasn't ignoring you. Haha.

John Ciarrochi

Thank you Paul, I appreciate any assistance you can provide. In a nut shell I'm trying to Automate the data in specific columns of my source sheet "Inventory Master" to destination sheet "Consumables" with a criteria in the category column of only consumable.

Paul Newcome

Ok. It looks like we have a few different options depending on exactly how you intend to use the data once you get it onto your "Consumables" sheet. Are you running further metrics on this data, or is this just acting like a sort of filter?

John Ciarrochi

The Consumable sheet will have a few columns that we will be manually inputting data, however a majority of the data we would like to have carried over from the Source sheet AKA Master Inventory to save data input time. At this time no other metrics will be running on the destination sheet AKA Consumable that I'm aware of.

I tried =IF({Inventory Master Sheet Range 1} = "consumable", {TEST Inventory Master Sheet_JC Range 2}) and it actually worked but I was only able to reference just the one Cell in the source sheet, when I reference the entire column I get an Invalid error.

Paul Newcome

Is there a reason these additional columns can't be on the master sheet?

Here is what I am thinking...

Add the columns to the Master, pull a Report based on "consumable", only show the pertinent columns that you want pulled and the extra columns for manual entry.

Since you don't need to run further metrics and are only using it for manual entry, you can update the report and it will update the corresponding cells in the Master. The report can be built to only show rows that contain "consumable".

You could also add the columns to the Master and just create a filter.

John Ciarrochi

Thanks for the input -

I believe the additional columns are not indicative to the Master and will make it convoluted. Looking to automatically bring specific data over from Master into the Consumables sheet based on the criteria of Master sheet Category Column be "consumable". Is there a way to accomplish this without building out reports?

Thank you,

JC

Paul Newcome

The columns can be hidden on the Master so that they are not visible on the Master sheet, only in the Report.

There are ways to pull the info into a second sheet, but it can get a little tricky depending on the specifics. I really do feel that hidden columns on the Master Sheet that are only shown in a Report is going to be the easiest way to go both in setting up and in managing.

John Ciarrochi

So bring all columns from the Master into the destination sheet (Consumable)? and Hide the ones we don't need to see? and create the report off the Consumable sheet with filtering only the Category "consumable". Is there a way to setup filtering on reports?