I just don't know how to thank you for this... I've been trying to work something like this into database review tool for a few weeks.. Even watching the video, it took a while but you sir, are a saint. Thanks. I'm subscribed and will be recommending this site to everyone whose as geeky as me!
Nicely done! Your description of the functions and how they work together was perfect ... thanks again for sharing your knowledge. You are an excellent teacher.
try this video: Excel Magic Trick 844: Last Date In Column A For "N" In Column B or Excel Magic Trick 938: Lookup Row Associated With Employee And Get Last In That Row Excel Magic Trick 942: Lookup Last Shipment Date For Customer, Sorted & Not Sorted Date Column
for this example: cell B16: =IF(G13="ALL",ROWS(B30:B39), then SUMPRODUCT formula ) cell B18: =IF($G$13="ALL",INDEX(B$30:B$39,ROWS(B$18:B18)), then extract formula )
Excellent stuff. Had been looking for a way to filter with formulas only (and without tagging the source data with an extra column!) Also, I think F9 while selecting formula is going to change my life :-) Thanks
This is an incredibly useful formula. I have one question that I can't seem to answer. What do I need to input in a given criteria cell to return any result? Let's say we want to look at any sales rep that meets all the other criteria? The answer with advanced filters or sumifs is to input "" and this criteria instructs Excel to ignore all non-blank cells. Thanks in advance!
I managed to get it to workk across the sheets but now only some of the records show randomly and #VALUE! in its place. By the way you are a legend as spent hours pouring over forums and help site until I watched you vid and it all clicked. Step by step with you holding my hand thank you a million times...
Glad you like hows and whys!!! Yes, if you want to know the hows AND the whys, excelisfun the place : ) Thank you for giving your Sub, and leaving a Thumbs Up and small comment on each vid to help support : )
Here is THE video that covers INDEX in great detail: INDEX & MATCH functions Beginner to Advanced 19 Examples (Excel VLOOKUP WEEK Video #3) youtube [dot] com/watch?v=u-8D2G0y9EM
I'm going to have to watch this video a couple of times to truly understand it, but you make a lot of sense. I just need to watch while I have my workbook open. I have one question though, I like the idea of using the formulas, but how do I return a value from a column that isn't part of the condition? I want to return all of the rows you have filtered on, but also including an account number. Thanks.
ExcelIsFun question I know there are several ways to do a formula, for trick 703 do you need to use the multiplication array I know sometimes you just use a ambersome to connect your criteria like trick 822
Very good video. Solves a problem that has been troubling me since long. Appreciate it. I have one small question. What when E13 and E14 are blank? Which means I want to view the whole data set, and summed up by date or date ranges. Any solution?
Thank for all the videos that you post. They're very useful. I'm faced with this scenario: What if the search criteria doesn't appear in every row but rather only as a column header?
I love your "tricks" :) and I need your help. I have a formula in a cell. for my next operation I need to read the "value" in this cell and not formula. (I am trying to use the "text to columns" in formulas tab and it needs to read values of the cell) thanks a million for all the lovely videos you have.
Great video!! One question though. So the column that I pulling my data from all have hyperlinks. When I get my output list, how do I get that hyperlink to transfer with the word. is that possible?
Hi Mike, could I use the approach in this video to extract which days are monday and friday in order to get a weekly increase from start to finish without the middle days??? If I had for example the same data as in this video but for larger amount of dates,,, just date and sales data
Example, if user selected Safety Stock in List box 1 and selected Safety Stock = 0 in List Box 2, I want the data that appear are only all products with safety stock = 0. I have already done formula in another sheet where the data are listed that for any safety stock = 0 put YES and count how many of YES in the rows. If the row where the result should appear is greater than the count then leave Blank (as per your example above). At the moment I have to keep changing the column ref manually.
I am using this method to return a set of results in multiple locations with different criteria in each location, so I would need too many helper columns adjacent to the underlying data. Is there any way to do it within the sumproduct formula or in the specific criteria cell? I just need a way to disable one or multiple criteria without pulling those out of the formula itself. I thought using "does not equal blank" in the criteria would work, but it didn't. Thanks!
Is there a simple way of data being automatically extracted from one sheet to another when a condition is greater than 0. example a full list of products (100+) on sheet 1 when an order Is placed it automatically goes on to sheet 2 with the product code qty, description and price. Hope that makes sense, thanking you M Miller
Hello again. I figured out a work around for my previous question. It wasn't as pretty as I was hoping but now my bigger question, when doing the count with the SumProduct, can you use other worksheets in the function? If the count is on the first sheet and you have data you want to count on sheets 2 through 5, how can you get it to count those with the logic you used? Thank you.
I no longer see the file emt703-706 under the Vlookup fiels section in your download page, when i googled it i get an edited version! is it somewhere else under a different topic menu on your downloads page??
Thank you for the great videos! How would you copy a column from worksheet A, lining up the cells to match matching data cells in the existing column of worksheet B? (assuming that there is only a subset of cells that match)
i am trying to extract a row with the Column "HOT LEAD". i can do an advanced filter to extract the rows to a new worksheet only from 1 sheet. how do i set it up to pull "HOT LEAD" from multiple sheets?
Thanks for the demonstration but I am having trouble incrementing the rows within the index function. The formula is pulling the correct total of instances but it only displays the first record. What am I missing in the formula?
I would like to commend you on your videos. I have used many of your Magic Tricks over the years. I just recently watched your video on Magic Trick 703 and found it was exactly the solution I was trying to incorporate in a spreadsheet I am working on. However, when I followed your instructions I got a #VALUE error in the "Count" formula. I am using dynamic tables and named ranges in Excel 2010 and I was wondering if this may be the cause of the error. Can you help me resolve the error.
Hi, Thank you so much for this video. Helped me a lot. I need some more help, I want to extract data but it should be sorted date wise. If you can solve this then it would be a great help. Thanks in advance. 🙂
Hi! Thanks a lot for the above details. 1 small change in my requirement - in your case the formula criteria is static where as the data i am working with will have formula criteria dynamic. So do you have any video where in the array formula can be used for dynamic range of criteria? say from 1 row to a few 100 rows say 500... so how to use array formula in this condition to pull all the conditions that are part of the data within these 500 rows.. ignoring blanks... Thanks, Manish
The solution works but I'm not always getting values specific to the conditions set in the filter.. I can't figure out why.. is there a way to integrate MATCH in here?
Could you help me on this one I am using your formula to extract data from one sheet and placing in a table on sheet 2 I have the count right and entered the formula for my data. It pulls Data for Column 1 Row 1 but all the other columns and rows comes up with #NUM! . Could you tell me what I did wrong? Using 2007 version
Hi, i feel that what im looking for (for very long time) is somewhat related to this, but not exactly. Will excel be able to count the difference between 2-3 different but constant Cell Value(s) in an ever changing cell or row positions? I don't know how can i send you my workbook to see what i mean, but i have bunches of columns per week from Jan 1 to Dec 31 = 52 weeks (whole year), and large rows of names of my staff - doing a shift works: (AM) morning, (PM) afternoon, and (ND) night and these shifts are largely irregular (never a staff will have the same set of shifts from previous week to another); and they entitled 1 DO (day off) and 1 RD (rest day) per week) and 12 PH (public holidays) along the year; NOW, the return that im looking for is, an extra column at the end of each week to count "how many days they are working between their off days, example from the last DO to the next RD, if DO happen to be on Mon and RD on Sun (manually thats 5 working days); or, if DO/RD are together and happened to be on Sat and Sun, between these 2 is Zero but how many they have been working before (since that last DO which can be found on last week) or after (before the next DO comes from next week) or, how many days they already been working that week including from last week if their last week DO and/or RD was given on the Mon and Tue of that last week). Haha, im not testing your wits, but i hope you find that this is complicated enough - to challenge you and makes sure day ever more FUN!
Not really a video for trouble shooting. #NAME! means the ranges are not really ranges, or some word in your formula is not being recognized as a function or defined name or sheet reference. For back and forth dialog or to get custom solutions or have people help trouble shoot, THE best site to post question to is: mrexcel [dot] com/forum This site is not too good because the comments section does let us post all types for formulas or characters.
HELP,in my spreadsheet I used two List Boxes. If a user selected an option from List box 1, another list appear in List Box 2. The user need to select List Box 2. Then the data that I want to bring as the result is based on what user selected in List box 1 & 2. In my data worksheet, I have already done Index match formula to bring column no based on the selection but I don't know how to put these together with your formula.
Hi I have gone thru few of the videos of the excel, they are awesome. Being in profession for last few years, i have intention to go thru all of them to hone up my skill in a better way. Can you tell me how should i proceed. Every videos is in separate. Is there any videos or link, wherein you have mentioned that this need to be done first (including videos and excel files link) and then following chapters to be followed etc etc. Just to put them in semblance order. This would be of great help for other also. I have no idea where to put my words for suggestions, so i have added over here. Will it come to your attention.
Start with Excel basics series: ruclips.net/p/PL3FBEE51974F03CCF Then go on to Highline Community College Excel Class: ruclips.net/p/PLrRPvpgDmw0nh4BBxqjcsnbB4ME4JjzfJ You can also watch the auto play video at the channel: ruclips.net/user/excelisfun
ExcelIsFun Thanks for replying so far, Got it, once am done with 1 - 23 basic series, is there any way, i can get the video 24 next like wise, so that i can clearly go by numbers 1 - 2000 or so whatever you have done so far. Is there any way, i can type excellfun 24 or video 24 etc. let me know Thanks once again.
After Basics, you do Highline Excel Class (This class covers all the concepts needed for all other 2000 videos): Excel 2007: ruclips.net/p/PLD8EEA7196D943ED5 Excel 2013: ruclips.net/p/PLrRPvpgDmw0nh4BBxqjcsnbB4ME4JjzfJ
Hi! My table has say 10 columns but I need to review conditions only in say 1 to 3 columns and get rest of the data as well? So how do we do that? Please advice. Thanks, Manish
Please help! I have a golf tournament and I'm trying to create a formula on adding multiple minumims.See Example below- Mark is on A1- I would need for day 1 to calculate the lowest 4 scores added. Day 2,can be a separete formula,but haveing the same results. I don't want to click click click for 220 golfers Day 1 Day 2 Mark 95 99 Jack 94 92 Joe 74 92 Paul 85 84 Sam 100 84 Total
Can anyone help. I'm using this example in a spread i have a work containing multiple entries. some cells are intentionally blank until information is entered, also some have additional formatting for a number of reasons. 1. how do i set it to copy blank cells to intentionally copy blank 2. how do i copy additional formatting 3. how do set the array to account for additional entries in the master list
I have a problem, if a small excel list is to be deleted from a main big list. For example - if from 600 numbers, 200 numbers are to be deleted forever, then what is the command? Kindly help. Thanks
If customer name cell is blank in criteria then I want to extract data by only sales rep criteria,else by both criteria then for this which formula work
Could you help me with following task: I have table A:FirmName; B:Tariff numbers (several successive imports of various products, including the same tariff numbers several times per a firm), quantity per one tariff number. I need to filter in following way: by firm and by tariff number (I did it). Now is tricky. Additionally, I need to consolidate the same tariff numbers within one firm (i.e. instead of heaving several rows with the same firm and tariff number, I need those with the same tariff number to be like one row with sum of several quantities). At the end, it should be like: I have several firms with organized tariff numbers per a firm, without duplicated tariff numbers.
i am looking for excel to analyze a spreadsheet that lists dates, order numbers, and order amounts $, look at that spreadsheet, find a specific date, then copy all the orders that have that date listed with them to a new sheet. is that possible? then the next day do the same thing with a new date, ultimately creating a new list that takes data and accumulates it without needing to reference back to the original data set once that day is over. can that be done in excel?
Try posting question to: mrexcel [dot] com/forum RUclips is not a good place to have back and firth dialog to get Excel solutions. You can even send me the link after you post.
I will make a video for you, but it won't be posted until about two weeks. I will do this because there is a MUCH easier way to do it, as long as you have M 365 Excel. What version do you have?
It is great ! With your knowledge I was able to built a dynamic employee's list for a schedule tool. AWESOME. A pleasure to "meet" someone with more knowledge ;-) A master ;-)
Try running the formula evaluator in the Formula Ribbon tab and watch it step by step. Basically, the logical test gives a TRUE for when criteria is matched and that gets the relative position from the ROW()-ROW()+1, then the array of matched relative positions sits inside SMALL, and as the formula is copied down the SMALL takes the first smallest, then the next smallest and so on.
I followed everything to the letter but when I dragged the formula from left to right in cell B18, the same value appears in cells C18, D18 and E18. I did not use dates nor did I use multiple variables (as you have in cells E13 and E14); I just used numeric thresholds in my Row 13 data: Cell B18: =IF(ROWS(B$18:B18)>$B$16,"",INDEX($B$30:$B$39,AGGREGATE(15,6,(ROW($B$30:$B$39)-ROW($B$30)+1)/(($B$30:$B$39>=$B$13)*($C$30:$C$39>=$D$13)*($D$30:$D$39>=$E$13)*($E$30:$E$39>=$F$13)),ROWS(B$18:B18)))) Cell C18: =IF(ROWS(C$18:C18)>$B$16,"",INDEX($B$30:$B$39,AGGREGATE(15,6,(ROW($B$30:$B$39)-ROW($B$30)+1)/(($B$30:$B$39>=$B$13)*($C$30:$C$39>=$D$13)*($D$30:$D$39>=$E$13)*($E$30:$E$39>=$F$13)),ROWS(C$18:C18)))) Help!
I need help again! I have set up a spreadsheet to pay my staff which is a fairly straight forward afair but I am having difficulty extracting some required data. I will explain as briefly as possible. Col A is thestaff names, B their clock on time c their clock off time, d the hours the have worked, e the hourly gross pay rate, f tax, g net pay, simple so far. I have agreed with my staff to help them out with their petrol, but I want to automaticially incorperate this figure after their net pay so that I have an 'all-in-one' figure to pay them. I have tried extracting the info with lookup and vlookup, but the problem is that if my staff have work more tahn one shift the lookup stops at the first amount,, I have tried index and match, but the same issues. Say Jo blokes has worked 3 sessions and is due 15.00 petrol money for session 1, 12 for session 2 and 20 for session 3 how do I extract that info from the spread sheet I haveset up which only has 3 columns. A unique number, the name and the amount due. I am certain it wll be simple but Iamat my whits end!!
Need some help... I try you formula in excel magic 185 treat dynamic formula extract data. but in my number of records it only shows 1, it didnt change. Where I go wrong?
This is fabulous! What a wonderful gift to me! I just took a new job, I am an older worker, and now I can do it! Thank you so much!
I love to hear that! You are welcome for the videos!
I just don't know how to thank you for this... I've been trying to work something like this into database review tool for a few weeks.. Even watching the video, it took a while but you sir, are a saint. Thanks. I'm subscribed and will be recommending this site to everyone whose as geeky as me!
I am glad that the videos help!
This formula shows one way:
Excel Magic Trick 900: Simulate Filter With Formula to Create Immediate Filter Results
It shows a Helper column method
Nicely done! Your description of the functions and how they work together was perfect ... thanks again for sharing your knowledge. You are an excellent teacher.
+Ben Cacace Glad you like it!
You are welcome!
try this video:
Excel Magic Trick 844: Last Date In Column A For "N" In Column B
or
Excel Magic Trick 938: Lookup Row Associated With Employee And Get Last In That Row
Excel Magic Trick 942: Lookup Last Shipment Date For Customer, Sorted & Not Sorted Date Column
for this example:
cell B16:
=IF(G13="ALL",ROWS(B30:B39), then SUMPRODUCT formula )
cell B18:
=IF($G$13="ALL",INDEX(B$30:B$39,ROWS(B$18:B18)), then extract formula )
Excellent stuff. Had been looking for a way to filter with formulas only (and without tagging the source data with an extra column!)
Also, I think F9 while selecting formula is going to change my life :-)
Thanks
This is an incredibly useful formula. I have one question that I can't seem to answer. What do I need to input in a given criteria cell to return any result? Let's say we want to look at any sales rep that meets all the other criteria? The answer with advanced filters or sumifs is to input "" and this criteria instructs Excel to ignore all non-blank cells. Thanks in advance!
Thanks so much Mike. Love to learn new things from you. You are one of (if not the) best :)
I managed to get it to workk across the sheets but now only some of the records show randomly and #VALUE! in its place. By the way you are a legend as spent hours pouring over forums and help site until I watched you vid and it all clicked. Step by step with you holding my hand thank you a million times...
Real informative. No other video on youtube explained AND OR in advanced filter the way you did. Keep up the good work.
Glad you like hows and whys!!! Yes, if you want to know the hows AND the whys, excelisfun the place : ) Thank you for giving your Sub, and leaving a Thumbs Up and small comment on each vid to help support : )
Sure, I really appreciate anything and everything that is good, genuine and outstanding.
You are doing great 👍
Glad this helps you!
Thank you mike I love ur videos and teaching, too good thank u.
Well done again, sir! I especially liked the efficiency of the Advanced Filter tool. However, the dynamic strategy was impressive. ☺☻
Here is THE video that covers INDEX in great detail:
INDEX & MATCH functions Beginner to Advanced 19 Examples (Excel VLOOKUP WEEK Video #3)
youtube [dot] com/watch?v=u-8D2G0y9EM
I'm going to have to watch this video a couple of times to truly understand it, but you make a lot of sense. I just need to watch while I have my workbook open.
I have one question though, I like the idea of using the formulas, but how do I return a value from a column that isn't part of the condition? I want to return all of the rows you have filtered on, but also including an account number.
Thanks.
Did you enter the formula with Ctrl + Shift + Enter?
@tompope84 , that should work just fine, blank, blank, date lower and date upper.
thanks man
was having a lot of problem as i needed some help
your video helped me a log
thanks again
Awesome Mike with EXCELlent video
Thank you for this!
An amazing and easy to follow video and does exactly what I needed it to do.
Brilliant
+Ryan Evans Glad you like it!!!
ExcelIsFun question I know there are several ways to do a formula, for trick 703 do you need to use the multiplication array I know sometimes you just use a ambersome to connect your criteria like trick 822
Fantastic video - thanks a bunch!
Well thanks, you got yourself a subscriber !
Thank you for your comment, MCH, and for the support with Thumbs Up and Sub : ) Many more fun videos to come!!!!
It is there.
Very good video. Solves a problem that has been troubling me since long. Appreciate it. I have one small question. What when E13 and E14 are blank? Which means I want to view the whole data set, and summed up by date or date ranges. Any solution?
Thank for all the videos that you post. They're very useful. I'm faced with this scenario: What if the search criteria doesn't appear in every row but rather only as a column header?
I love your "tricks" :) and I need your help.
I have a formula in a cell. for my next operation I need to read the "value" in this cell and not formula. (I am trying to use the "text to columns" in formulas tab and it needs to read values of the cell)
thanks a million for all the lovely videos you have.
fantastic tutorial.......
Great video!! One question though. So the column that I pulling my data from all have hyperlinks. When I get my output list, how do I get that hyperlink to transfer with the word. is that possible?
Did you ever find a solution for this?
This video is awesome! Thank you so much for sharing it!!
You are welcome so much! Thanks for your support with your comment, thumbs up and your Sub : )
Hi Mike, could I use the approach in this video to extract which days are monday and friday in order to get a weekly increase from start to finish without the middle days??? If I had for example the same data as in this video but for larger amount of dates,,, just date and sales data
excellent, thanks.. can this be done with multiple criteria?
Example, if user selected Safety Stock in List box 1 and selected Safety Stock = 0 in List Box 2, I want the data that appear are only all products with safety stock = 0. I have already done formula in another sheet where the data are listed that for any safety stock = 0 put YES and count how many of YES in the rows. If the row where the result should appear is greater than the count then leave Blank (as per your example above). At the moment I have to keep changing the column ref manually.
I am using this method to return a set of results in multiple locations with different criteria in each location, so I would need too many helper columns adjacent to the underlying data. Is there any way to do it within the sumproduct formula or in the specific criteria cell? I just need a way to disable one or multiple criteria without pulling those out of the formula itself. I thought using "does not equal blank" in the criteria would work, but it didn't. Thanks!
Is there a simple way of data being automatically extracted from one sheet to another when a condition is greater than 0. example a full list of products (100+) on sheet 1 when an order Is placed it automatically goes on to sheet 2 with the product code qty, description and price. Hope that makes sense, thanking you M Miller
am soo happy with this video
Glad you are so happy : )
Hello again. I figured out a work around for my previous question. It wasn't as pretty as I was hoping but now my bigger question, when doing the count with the SumProduct, can you use other worksheets in the function? If the count is on the first sheet and you have data you want to count on sheets 2 through 5, how can you get it to count those with the logic you used? Thank you.
I am currently overwhelmed at work . Try posting your question to the mrexcel[dot]com/forum
7 years old and it's still Awesome :)
Ya, the compare and contrast videos are my favorite too. Thanks for your support, John : )
I no longer see the file emt703-706 under the Vlookup fiels section in your download page, when i googled it i get an edited version! is it somewhere else under a different topic menu on your downloads page??
Thank you for the great videos! How would you copy a column from worksheet A, lining up the cells to match matching data cells in the existing column of worksheet B? (assuming that there is only a subset of cells that match)
I should mention my question is in regard to the sumproduct array formula dynamic filter.
i am trying to extract a row with the Column "HOT LEAD". i can do an advanced filter to extract the rows to a new worksheet only from 1 sheet. how do i set it up to pull "HOT LEAD" from multiple sheets?
Thanks for the demonstration but I am having trouble incrementing the rows within the index function. The formula is pulling the correct total of instances but it only displays the first record. What am I missing in the formula?
hi, gone through video, its awesome. can i get training videos in cd or some tutorials
THUMPS UP :)
Thanks!
I would like to commend you on your videos. I have used many of your Magic Tricks over the years. I just recently watched your video on Magic Trick 703 and found it was exactly the solution I was trying to incorporate in a spreadsheet I am working on. However, when I followed your instructions I got a #VALUE error in the "Count" formula. I am using dynamic tables and named ranges in Excel 2010 and I was wondering if this may be the cause of the error. Can you help me resolve the error.
Hi,
Thank you so much for this video. Helped me a lot.
I need some more help,
I want to extract data but it should be sorted date wise.
If you can solve this then it would be a great help.
Thanks in advance. 🙂
In case we use improper data set(eg. misspellings. etc), can we use wildcard type criterias ? pls. explain dear
Thanks "ExcelIsFun"....
Hi!
Thanks a lot for the above details. 1 small change in my requirement - in your case the formula criteria is static where as the data i am working with will have formula criteria dynamic. So do you have any video where in the array formula can be used for dynamic range of criteria? say from 1 row to a few 100 rows say 500... so how to use array formula in this condition to pull all the conditions that are part of the data within these 500 rows.. ignoring blanks... Thanks, Manish
The solution works but I'm not always getting values specific to the conditions set in the filter.. I can't figure out why.. is there a way to integrate MATCH in here?
Could you help me on this one
I am using your formula to extract data from one sheet and placing in a table on sheet 2
I have the count right and entered the formula for my data. It pulls Data for Column 1 Row 1 but all the other columns and rows comes up with #NUM! .
Could you tell me what I did wrong? Using 2007 version
Next comment after formula:
then put ALL in cell G13 or leave it blank.
Hi, i feel that what im looking for (for very long time) is somewhat related to this, but not exactly. Will excel be able to count the difference between 2-3 different but constant Cell Value(s) in an ever changing cell or row positions? I don't know how can i send you my workbook to see what i mean, but i have bunches of columns per week from Jan 1 to Dec 31 = 52 weeks (whole year), and large rows of names of my staff - doing a shift works: (AM) morning, (PM) afternoon, and (ND) night and these shifts are largely irregular (never a staff will have the same set of shifts from previous week to another); and they entitled 1 DO (day off) and 1 RD (rest day) per week) and 12 PH (public holidays) along the year; NOW, the return that im looking for is, an extra column at the end of each week to count "how many days they are working between their off days, example from the last DO to the next RD, if DO happen to be on Mon and RD on Sun (manually thats 5 working days); or, if DO/RD are together and happened to be on Sat and Sun, between these 2 is Zero but how many they have been working before (since that last DO which can be found on last week) or after (before the next DO comes from next week) or, how many days they already been working that week including from last week if their last week DO and/or RD was given on the Mon and Tue of that last week). Haha, im not testing your wits, but i hope you find that this is complicated enough - to challenge you and makes sure day ever more FUN!
Not really a video for trouble shooting. #NAME! means the ranges are not really ranges, or some word in your formula is not being recognized as a function or defined name or sheet reference. For back and forth dialog or to get custom solutions or have people help trouble shoot, THE best site to post question to is:
mrexcel [dot] com/forum
This site is not too good because the comments section does let us post all types for formulas or characters.
I did, the ctrl+shft+enter - now I have the #NAME! error, is that progress? Is there a video that can help me resolve that error?
How does this work across multiple sheets as I have a senario wherby I am doing this with the reporting section on another sheet.
Good
Perfect! Thanks :-)
HELP,in my spreadsheet I used two List Boxes. If a user selected an option from List box 1, another list appear in List Box 2. The user need to select List Box 2. Then the data that I want to bring as the result is based on what user selected in List box 1 & 2. In my data worksheet, I have already done Index match formula to bring column no based on the selection but I don't know how to put these together with your formula.
Hi, how to watch the previous Excel magic trick such as from the beginning?
Hi
I have gone thru few of the videos of the excel, they are awesome. Being in profession for last few years, i have intention to go thru all of them to hone up my skill in a better way. Can you tell me how should i proceed. Every videos is in separate. Is there any videos or link, wherein you have mentioned that this need to be done first (including videos and excel files link) and then following chapters to be followed etc etc. Just to put them in semblance order.
This would be of great help for other also.
I have no idea where to put my words for suggestions, so i have added over here. Will it come to your attention.
Start with Excel basics series:
ruclips.net/p/PL3FBEE51974F03CCF
Then go on to Highline Community College Excel Class:
ruclips.net/p/PLrRPvpgDmw0nh4BBxqjcsnbB4ME4JjzfJ
You can also watch the auto play video at the channel:
ruclips.net/user/excelisfun
ExcelIsFun
Thanks for replying so far, Got it, once am done with 1 - 23 basic series, is there any way, i can get the video 24 next like wise, so that i can clearly go by numbers 1 - 2000 or so whatever you have done so far.
Is there any way, i can type excellfun 24 or video 24 etc.
let me know
Thanks once again.
After Basics, you do Highline Excel Class (This class covers all the concepts needed for all other 2000 videos):
Excel 2007:
ruclips.net/p/PLD8EEA7196D943ED5
Excel 2013:
ruclips.net/p/PLrRPvpgDmw0nh4BBxqjcsnbB4ME4JjzfJ
nice video! how can i make a formula if i only want extract "product" only but other criteria remain empty?
Hi!
My table has say 10 columns but I need to review conditions only in say 1 to 3 columns and get rest of the data as well? So how do we do that? Please advice. Thanks, Manish
I have no idea without look at the data and formula. Send workbook to excelisfun at gmail and i will take a look.
Can you please help me? I am getting wrong output when i use this formula across sheets.
hi, how can i modify it if there is one more choice for the salesrep let say is * which mean to extract data of the all salesrep?
Suppose I make d13 empty do I still get correct results ?
Love'em :) :)
Thanks for the love : ) : )
Please help! I have a golf tournament and I'm trying to create a formula on adding multiple minumims.See Example below- Mark is on A1- I would need for day 1 to calculate the lowest 4 scores added. Day 2,can be a separete formula,but haveing the same results. I don't want to click click click for 220 golfers
Day 1 Day 2
Mark 95 99
Jack 94 92
Joe 74 92
Paul 85 84
Sam 100 84
Total
what should we do if they ask for filter criteria atleast 100r data and our data gets filtered plz reply as soon as possible
Can anyone help. I'm using this example in a spread i have a work containing multiple entries. some cells are intentionally blank until information is entered, also some have additional formatting for a number of reasons.
1. how do i set it to copy blank cells to intentionally copy blank
2. how do i copy additional formatting
3. how do set the array to account for additional entries in the master list
GOOD👍👍👍👍
Glad you like it, vijay : )
I have a problem, if a small excel list is to be deleted from a main big list. For example - if from 600 numbers, 200 numbers are to be deleted forever, then what is the command? Kindly help. Thanks
If customer name cell is blank in criteria then I want to extract data by only sales rep criteria,else by both criteria then for this which formula work
Why not use countifs to get the count ?
Could you help me with following task: I have table A:FirmName; B:Tariff numbers (several successive imports of various products, including the same tariff numbers several times per a firm), quantity per one tariff number.
I need to filter in following way: by firm and by tariff number (I did it). Now is tricky.
Additionally, I need to consolidate the same tariff numbers within one firm (i.e. instead of heaving several rows with the same firm and tariff number, I need those with the same tariff number to be like one row with sum of several quantities).
At the end, it should be like:
I have several firms with organized tariff numbers per a firm, without duplicated tariff numbers.
Seems that the columns are returning in the correct ones but the rows are messed up.
My count is correct but the values returned are incorrect and jumping rows including the dreaded #VALUE! sporadically placed.
I am not sure. Try THE best Excel question site:
mrexcel [dot] com/forum
What's the syntax for "Contains" or "Does Not Contain" if I want to use that in my criteria/formula?
The array of TRUEs and FALSEs can be created using: Contains: ISNUMBER(SEARCH(Condition,Column)), NOT Contains: ISERROR(SEARCH(Condition,Column))
i am looking for excel to analyze a spreadsheet that lists dates, order numbers, and order amounts $, look at that spreadsheet, find a specific date, then copy all the orders that have that date listed with them to a new sheet. is that possible? then the next day do the same thing with a new date, ultimately creating a new list that takes data and accumulates it without needing to reference back to the original data set once that day is over. can that be done in excel?
Try posting question to:
mrexcel [dot] com/forum
RUclips is not a good place to have back and firth dialog to get Excel solutions. You can even send me the link after you post.
Is there any new video newer than this one with the same topic
I will make a video for you, but it won't be posted until about two weeks. I will do this because there is a MUCH easier way to do it, as long as you have M 365 Excel. What version do you have?
Thanks for your reply. I'm using Microsoft Excel 2021 @@excelisfun
It is great ! With your knowledge I was able to built a dynamic employee's list for a schedule tool. AWESOME. A pleasure to "meet" someone with more knowledge ;-) A master ;-)
Läuft bei dir. F4, F9. Alles klar!
I did not get it: how can SMALL function tell the row number for INDEX function?
Try running the formula evaluator in the Formula Ribbon tab and watch it step by step. Basically, the logical test gives a TRUE for when criteria is matched and that gets the relative position from the ROW()-ROW()+1, then the array of matched relative positions sits inside SMALL, and as the formula is copied down the SMALL takes the first smallest, then the next smallest and so on.
sir what we have 300 columns
Hello, I have a recordset more than 300000 rows to extract data. The formulas doesn't work. How can I solve this problem.
A formula like; =SUMPRODUCT(FREQUENCY(Values,Values)) will count unique numbers.
I followed everything to the letter but when I dragged the formula from left to right in cell B18, the same value appears in cells C18, D18 and E18. I did not use dates nor did I use multiple variables (as you have in cells E13 and E14); I just used numeric thresholds in my Row 13 data:
Cell B18:
=IF(ROWS(B$18:B18)>$B$16,"",INDEX($B$30:$B$39,AGGREGATE(15,6,(ROW($B$30:$B$39)-ROW($B$30)+1)/(($B$30:$B$39>=$B$13)*($C$30:$C$39>=$D$13)*($D$30:$D$39>=$E$13)*($E$30:$E$39>=$F$13)),ROWS(B$18:B18))))
Cell C18:
=IF(ROWS(C$18:C18)>$B$16,"",INDEX($B$30:$B$39,AGGREGATE(15,6,(ROW($B$30:$B$39)-ROW($B$30)+1)/(($B$30:$B$39>=$B$13)*($C$30:$C$39>=$D$13)*($D$30:$D$39>=$E$13)*($E$30:$E$39>=$F$13)),ROWS(C$18:C18))))
Help!
I am currently overwhelmed at work (80+ hours a week). Try this site:
mrexcel[dot]com/forum
Hi, i am getting result as #VALUE! , could anyone help to fix this.
9:03
doesn't work...i too would like to see...need it
After watching this I have to find out what the Index formula was... totally lost me there
Wow! Thanks a lot for saving my ass (y)
I need help again! I have set up a spreadsheet to pay my staff which is a fairly straight forward afair but I am having difficulty extracting some required data. I will explain as briefly as possible. Col A is thestaff names, B their clock on time c their clock off time, d the hours the have worked, e the hourly gross pay rate, f tax, g net pay, simple so far. I have agreed with my staff to help them out with their petrol, but I want to automaticially incorperate this figure after their net pay so that I have an 'all-in-one' figure to pay them. I have tried extracting the info with lookup and vlookup, but the problem is that if my staff have work more tahn one shift the lookup stops at the first amount,, I have tried index and match, but the same issues.
Say Jo blokes has worked 3 sessions and is due 15.00 petrol money for session 1, 12 for session 2 and 20 for session 3 how do I extract that info from the spread sheet I haveset up which only has 3 columns. A unique number, the name and the amount due. I am certain it wll be simple but Iamat my whits end!!
For back and forth dialog to get Excel solutions, try THE best Excel question site:
mrexcel.com/forum
Need some help... I try you formula in excel magic 185 treat dynamic formula extract data. but in my number of records it only shows 1, it didnt change. Where I go wrong?