Pull from another worksheet IF criteria is met

Jennifer Levey

I think I need an IF formula, but what I'm trying to do isn't working so far - so I reach out to the community!

I want to pull information from sheet A (client name, phone number, e-mails, what they purchased, etc) which is the master list of all the clients and what they purchase, to sheet B (to schedule work, this is a template), if on sheet A they are marked as a current client for the current fiscal year (two separate fields). 

Is this possible? Thanks

Jim Hook

Jennifer, it is possible with two approaches. The easiest way is to use a Smartsheet report to scan the master sheet and based on the criteria bring things into the report. However, once in the report it can only be viewed and shared, no calculations are possible in a report.

The second way is much more complicated and uses cross-sheet references and VLOOKUP functions on one sheet to pull select data from the master sheet. The benefit of this way is that you can then do calculations and conditional formatting on a sheet that you cannot do on a report.

If you look through discussions from yesterday, others were trying to do the same thing and there was some info on how to do this. It uses advanced functions which can be a challenge if you're new to them.

 

Jim

Jennifer Levey

I worked with our database programmer to try using VLOOKUP, but we keep getting errors. Can you see what's wrong in the formula?

=VLOOKUP("2017-18", {Customer Information & History Range 3}, 3, true)

I'm trying to pull the name of a customer in column 3, if 2017-18 is in column 1. I actually want to pull it based on two criteria, but I'm not sure if that's possible.

Jim Hook

I don't see a problem with your formula as such. I assume that your external range includes at least three columns with column 3 having the data you're trying to get. Also, I typically set the last argument to "false" to only get exact matches. What error are you getting?

You are correct in that VLOOKUP only allows one criteria unlike the SUMIFS and COUNTIFS functions. Unfortunately, the only way I can see using multiple criteria in this situation is to link everything of interest from the source sheet into the destination sheet and then you can scan the data to get multicriteria results. Reports can also do that but they are only for displaying information since you can't do any analysis in a report.