How to Pull Data from Another Sheet based on Criteria in Excel & Google Sheets?
Vložit
- čas přidán 17. 05. 2021
- Learn how to pull data from another worksheet (aka sheet, tab) based on criteria in Excel & Google Sheets.
Sign up to get updates, practice files and code snippets eepurl.com/hwyGg1
#excel #googlesheets
Recent videos:
IF Functions from Hell in Excel & Google Sheets - Formulas czcams.com/video/6cwZoKdZh94/video.html
Excel - Create PDF Files from a Spreadsheet - czcams.com/video/egoYV8SjGj4/video.html
Google Sheets - Share & Work on Spreadsheets czcams.com/video/8ttrJiIL1As/video.html
Excel Online Link to Other Workbooks - czcams.com/video/ivkLvCEJXBk/video.html
Thanks so much for your videos. You explain complex matters really easily, and I'm finding the info incredibly helpful!
Good Tutorial and I use filter function a lot. And I learned to add a second filter function to wrap the first filter function to pick the columns I only needed. And on one of my sheets I made a relationship of checkboxes to choose the columns I wanted. I find the filter function to have a lot of uses. Thanks for all the tutorials!!!
That's what I was looking for when I came across this tutorial.
Thanks!!! I was struggling like mad since yesterday night to come up with a solution for this. I am entering data into a sheet that sums up the company's total expenses, but the expenses are made based on multiple projects. First I used filters, but this gets pretty daunting as the data multiplies. I was looking for a way to split the information according to each project in different tabs so I can see the expenses for each without having manually filter, or risk losing data while using copy-paste each time I have an entry. Your explanations are short, easy to follow, and very clear. Thank you!
Thank you for posting this. I was at a loss as I'm creating a CRM using Google Sheets and this helped me tremendously with this issue. No other video I came across had this exact issue!
Perfect - I've been searching for a formula for the last 3 hours!!! and this works!!
Quick and easy to follow explanation! I was searching and kept coming up with other solutions that didn't meet my needs. Yours did thank you!
Thank you so much! This was so helpful to help me sort commission rule data and customize it to the type of sale my employees did! I am forever grateful!
You just saved me at leasr 5 hours of manual work. Thank you so much!!
Love it. Another feature I just learned existed. This works for the project I have in mind. Thank you so much!!!
Thanks to your previous video about filter functon I was able to make a huge filter thing and my favorite thing is that you can combine different filters putting them all into an array like ={filter(A2:B30,A2:A30="male"),filter(G2:L30,G2:G30>30)} and even sort that range from columns It's insanely useful!
You could even use the Query function for that, but yes, the filter function is insanely powerful!
oh? how can you sort? When I try doing sort by converting the header into filter (from Data>Create a filter), it doesn't work :\. Can you suggest where I should look at?
@@abdullamasud4278 I meant with the sort() formula
(Using Google Sheets)
@@Maestr055 I really don't like the query formula, maybe I don't fully understand it lol
Tho I know SQL this one is weird
This was very simple and useful. Thank you!
Thank you very much. You helped me to succeed with my project in about 1 minute of your video. Awesome.
It was basic video. I have seen a seprate video of filter function on youtube it was a great video. You are doing great job. Keep doing it is sirji.
I appreciate and admire your work
Thank you! This was the best and helped me so much. I was able to link data sheets for easy editing and viewing for my staff.
Be careful with editing part. Data should not be edited in the results sheet.
You have no idea how much you just helped me! Thanks for the video.
Glad to hear!
This is what I need now, this is super helpful. Thank you, glad to find your video.
Great to hear!
Hi mate. You’re videos are great. Easy to understand and very comprehensive. You just earned a subscriber. 🎉
I am looking on how to map specific columns in two separate sheets. So if you have any video on this please share a link otherwise would appreciate if you create a video on this topic. Thanks once again and keep it up. Ta
Thanks 🙏👍 for video,I have used index,vlookup and many other methods,but thanks God,filter is easiest to use,thank u so much,I took me almost 10 days to lookup this video and found what I'm searching for,thanks once again.
Great to hear! Welcome to the channel👍
You have added much value. Thank you!
Great content as always man!
Great Sir your content is an absolute masterpiece! Thank you very much for sharing
you're a hero! thank you for this video
Hello! Does the filter function work where it is filtering text that is based on a formula? For example, I am looking to pick up all cells that are scored higher than 12, but the cells within the array are based on returns from an xlookup formula across 6 other spreadsheets. I hope this makes sense!
Sou super grato ao canal! Queria saber como faço uma ordenação que parece ser básica mas não é lol. Em outro momento eu digo qual é...
Thank you for vídeo!!!!
Great video; I have a similar need and having issues. I want to compare names from from sheet and where the names matches, add the data for the phone number in a cell . Any suggestion on what option I should use?
Great video......as you create always.
I was looking for that!!!! Thanks!!!
omg you just saved me so much time!thank u
You're amazing! Thank you.
Thanks a lot. On the drop down if you wanted to pull down more than 1 field, say 2 fields. Say male and female, is there a way to do that? Thanks
Thank you so much, this video helped us to save a lot of time :) Just one question what if we need to populate the cells with a similar word not exactly the same?
thank you so much ,its really very much helpful
Terima kasih .. memang tebaik bossku!
Very helpful... Thank you!!
is there a code you could use on Google sheets that could make the data you pull from the customers sheet modifyable and also reflect the changes on the customer tab and data pull tab?
very concise and useful
Great content bro
THANK YOU YOU SAVED MY JOB
Great!
Thank you for this. If I am using excel to keep track of my "open" work tasks and I have a tab for each segment of work and within each tab I have tasks that are "OPEN". How do I get one excel sheet to display the Open from several excel sheets?
great video thank you so much.
Very nice! I didn't know Excel has a filter formula now! Anyway, there's a way to use the filter for parts of words (where H2 is the cell where you type what it needs to filter on):
=FILTER(A:B,NOT(ISERROR(SEARCH(H2,A:A))))
Also, there is a way to not have the filter statements be "And" statements, so you can filter on two separate things (where H2 and H3 have search words to filter on):
=FILTER(A:B,(NOT(ISERROR(SEARCH(H2,A:A)))) + (NOT(ISERROR(SEARCH(H3,A:A)))))
thank you bro, help a lot!
You are a LIFESAVER thank you!
Thank You very much kind sir! ❤️
Great video! Happy to find the Filter function. When applying the IF statement to my filter, I receive an error “you’ve entreated too many arguments for this function.” Any assistance would be great to eliminate the zeros. Thanks!
Hi there, thanks for this super helpful video. Is it possible to do a filter in Google Sheets on a partial text criteria?
Thank you for this video.
I have an additional question.
If I make another entry in the "customer" sheet, how can this entry be updated in the
"data pool" sheet?
Please help. Thank you
It was very helpful I tried it work
Thanks for this videos it is a great help, but why are some of the columns being left out when I do it. Is there a certain amount of columns that it can take?
Need your help, don't know if you can. We have a main tab with data of leads. Theres a column:L - with URLS, and Column:M - with Empty space for Emails to go. The 2nd tab is where our VA found SOME of the emails for the URLS in the main tab. So right now in the 2nd Tab it looks like this: Column:L - Same URLs from Main tab, and Column:M - Emails found for some of the URLS. Question: How do we get the emails that were found for SOME of the URLS to move over to the main tab Column:M but match the CORRECT URLs even though the URL's are all mixed up on the main tab Column:L??? Thank you!
Great content. I have a question. While using the Query function, I have added data from different Google sheets through Importance and combined into one. The sheet is not moving data where special characters are used like / +. Please advise how can I include that data too.
Use filter function instead of query.
Hi, please is there a native formula or a way for cell A1 to always contain the active row?
This is a great video, thank you! I am wondering, can you use this filter function to pull information from multiple worksheets? So if you had another sheet with customers in, could you pull based on criteria from more than one sheet (with the same criteria) into the data pull sheet?
i had same query, did you find any solution for the same. Also what i did to solve my problem temporarily is that i placed the first pull out from column A to Column F representing sheet 1 and then the second pull out from column H to column M representing sheet 2, deliberately left column G blank for spacing.
Also looking for this.
Great video, thanks for the upload!
Further extending this scenario, is there a way to automate this process so that when a new entry is created in the masterfile, it will automatically pull the data and display it in the relevant worksheet? [e.g if a new female customer is added to the database, google sheets will automatically copy their data into the worksheet for female customers?]
This should do in Google Sheets czcams.com/video/QTySwuhpHG0/video.html
Fantastic video that answered a question I had searched all day for. One additional question regarding my "data pull" tab: I want to add additional columns on that tab and provide more data and keep that data tied to the row that it's in. Say i added something in in the data pull tab in cell L6, information for Donna Garcia. Then on the native "customers" tab I change Monica from Male to female, or delete her entry. When I do that everything shifts up and the data i entered for Donna is now in the same row as Crystal. Is there a way to tie that info to Donna?
Did you ever get an answer to this?
Hello just a question. How do you do it if you want to filter a column of numbers instead of a string
Sir: I want to know how can I get an output=input/4.5 as per input without using any extra cells or columns. Suppose I typed 450 in one cell of the particular column and it automatically puts 100.
That 'Filter' formula was clutch! The first few minutes of seeing it, I was able to modify it to my needs. However, when I pull the requested data from my Master sheet into another workbook, I cannot double click into the cell without the formula giving me an error.
Is there a way to 'link to the source data' on whatever 'main data set' the filter was taken from?
Gold, thank you so much
:)
if I do have ms-office 2013 then what should i do filter out the same by like by employee name or Dept.. what type of formula i should apply..
I was wondering if this would work if I had a timestamp column and it’s in this format “4/1/23 14:00” could I put my criteria at 4/1/23 to pull all data regardless of the the time .. or would I have to separate it ? Because my software exports the cvs file in this format..
Glad to see this tutorial. It helps me a lot in reporting. Is there a way to have multiple filters, e.g. I want to filter two items from same column?
Yes. Watch FILTER function video on the channel for more information.
Great video! My only issue is not being able to sort. Data is pulled in properly, but I am not able to edit any cells without losing the whole table. Sorting shifts some data down a few rows. help!
Is there a way to have a "Show All" selection in the filter list (Excel)? In other words be able to use the drop down to show male or female or all.
Great 👌👌
Man! Damn! this is a life saver.
👍
Is there a way to do this with a living document? Meaning that the filters will still work etc as you add rows and more info into the sheet?
great explanation, I only have one little question; could you sub filter this by specific Zip code numbers with a drop down list?
sure
THANK YOU SO MUCH !!!!
👍
thanks, its really work
great!
Thank ❤ you so much for this..
:)
Hi! Thanks for this video. Just I have one query. If I have to pull Data from Another Sheet based on a Criteria and I want that this new created sheet automatically get updated based on same criteria if worked on 1st sheet. How to do that. I do hope I will get answer. Thanks again.
Super, thanks 👍
Is source code available on Patreon or elsewhere?
Is there a way that the resulting output will be only specific columns that I want from the array?
In excel, do the formulas in the original spreadsheet copy over and does it stay as a table? Btw. This is a great solution. So easy.
Can data be pulled not from only one sheet to another, but from many as well. Let us say I have sheets from January to December, and wish to pull certain data from each one of them to a new one, is it feasible using this one formula you just showed ??
I have a sheet that brings whole rows over from another sheet depending on a status of a dropdown like your male, or female example. can I change the status of that dropdown in the new sheet? ie change male to female in the new sheet and it disappears while male is selected but is on the list when female is selected? At the moment the first entry has the formula in it so I can't change it.
Thanks!
can we also change the column while it get filter from the original data? i.e: original data has some figure on column "H" but while getting the data I needed that "h" data to be shown in column "g" ......is it possible ? please help
Hi, great video!! I just wanted to ask you... I have a table with multiple product codes with there description, prices, etc. I want that data to be pulled in another sheet when i write a code that matches with one in the table... _Is there any way to do that? Thanks for all you videos I'm trying to learn a little more about spreadsheets codes to make my work easier.
czcams.com/video/XBfBkDYdmjk/video.html
Nice! Thanks for the video. This gives me hope what I am trying to do is possible. I have a list of tasks with target dates. I want to pull the information to another sheet if the target date falls between the current work week. Is that possible?
It's possible. You'll need to use some date functions to figure out the start and end date of your current work week and then use those dates to filter the data using filter function.
Can this same process work if I have multiple different files, not just sheets?
Is there a way to input sheet name from a dropdown list of sheet names?
There is a channel, excel for freelancers teaching a school management system, can you show us something similar using google sheets?
I want to pull the data if the column has any value in it and not blank. Will the same formula work?
Thank you !
:)
IF select from drop down a text "abc" then what ever I write on the same sheet should also write same in real time on the other sheet , and if I select "def" text from drop down then it should be written on the DEF sheet please guide, Thanks in advance
Worked! However, if you try to edit anything in the new list that was pulled, the whole data will erase and shows up as "REF" error
you saved me
How do you ensure that you pull certain headings from previous sheet?
This is great!
what if I only want to filter and auto populate specific columns only in another worksheet?
Use arrays.
Thanks a lot
Amazing. What if we have a date column in data and we want to filter the data by changing date in reference cells?
Use a cell like I did with numbers.
Will this function work if new data is entered and will it move it automatically?
How do I use this formula but instead of male or female, I want to pull line items from a spread sheet if the value is equal to or greater than 1?
Thanks for this. Though I cant use this to my office excel 2019 version, I just transferred editing to google sheets.
It's available in Excel 2021 and later.
If you added a female customer in your master list will it update the items in the data pull tab?
Is it possible to do this with the criteria being a date range? for example: return all data from 9/23/23 - 10/5/23.
Do you help people create custom things?