Dear awesome viewers! Thank you for your amazing engagement! Remember, the most liked comment gets pinned - so start liking your favorites now! I love your questions and will try to respond to as many as I can. For quick generic queries, the comment section is perfect. But need more in-depth, personalized advice? Book a personalized consultations with me via topmate.io/mehran_vahedi. They're ideal for detailed, specific questions. Your support means the world to me. Let's keep the conversation going! Thank You!
Hi @amadif1793, it's fantastic to hear that the video was exactly what you needed, especially after being stuck for so long! If you have any suggestions or need more insights on related topics, feel free to share. Thanks for watching!
Hey @martinokereafor, thanks for the kind words! I’m thrilled to hear it saved you time. 🚀 Keep watching for more straightforward tips and tricks. And if there’s anything else you’d like to see simplified, just let me know!
Thank you so much, @jmcclure76! I'm thrilled to hear it made your day easier. If you have any other topics or questions you'd like me to cover in future videos, feel free to share them. Happy Excel-ing!
You're very welcome Kritanya! I'm happy that you found the video useful. Make sure you subscribe and turn on notifications so you won't miss out on my future Excel tips!
Hello, Can you help me with a query? When we select a name from the drop-down list and once it repeats, the integer should increment on the other sheet in front of the exact name. Looking forward for your response. Thanks in advance!
Hello @TJ-tk4hz, Thanks for reaching out with your query! It sounds like you're looking to set up a system where selecting a name from a dropdown list increments a corresponding integer on another sheet each time the name is selected again. You can achieve this using a combination of Excel formulas and features. Here’s a basic approach: [1] Setup your dropdown list: On your main sheet, set up your dropdown list using Data Validation to list the names. [2] Track the selections: On a second sheet, create a table with two columns: 'Name' and 'Count'. Every time a name is selected from the dropdown list, you'll record the event here. [3] Increment the count: You can use a combination of COUNTIF and VLOOKUP functions to update the count. In the 'Count' column next to each name, you could use a formula like the one below: =COUNTIF(MainSheet!A:A, NameCell) Replace MainSheet!A:A with the range of your dropdown list, and NameCell with the cell reference that corresponds to the name in the 'Name' column of your second sheet. This setup should increment the count on your second sheet every time a name is selected from the dropdown on the main sheet. If you need more detailed guidance or further customization, feel free to book a paid consultation with me @ : topmate.io/mehran_vahedi. Best, Mehran
Hello Brian, thanks for the comment. I'm not sure exactly what you mean. Are you trying to auto increment the first argument of the VLOOKUP? The first argument is already being auto incremented once you drag the formula down. See the K4 to N5 range. Once I dragged down the formula which I had applied to the first row the auto increment was applied to the lookup value.
Thank you for your kind words! It's great to know the video was helpful to you. Stay tuned-there are plenty more Excel tips and strategies coming your way that I think you'll find just as useful.
Hi @m3s69ll, Thank you for your comment and I'm glad you found the video helpful! To clarify why we use COLUMN(B2) in the formula rather than COLUMN(C2): The COLUMN(B2) function returns the column number of cell B2, which is 2. This is used in the VLOOKUP formula to dynamically select the column index for the lookup value. In the screenshot and the explanation in the video, COLUMN(B2) is used in the VLOOKUP formula within cell K3 to automatically update the column index parameter. This allows the formula to remain flexible and automatically adjust if columns are added or deleted in the data range. If you want to fetch the "Revenue" data specifically, which is indeed in column C as you noted, we need to ensure that the formula uses COLUMN(C2). This would be COLUMN(C2)-COLUMN($B$2)+1 to correctly point to the third column where the Revenue data starts. Hope this clears up any confusion! Thanks again for the comment and feel free to leave any other comments, questions and also video suggestions. Cheers, Mehran
Hi @m3s69ll, Great question! You’re correct that both B2 and B3 would return the same column number, 2, because they are both in the second column. The choice of B2 over B3 in this context doesn't impact the formula’s functionality, as either will correctly return the column number needed for the COLUMN function within our formula setup. The specific cell reference (B2 vs. B3) is often chosen for consistency or based on the starting point of the data range in examples. If there's a specific range or header row highlighted as the reference point in explanations or setups, that's generally the one used to maintain clarity. Thanks for your keen observation, and please keep the questions coming if you have more! Cheers, Mehran
Hello, thank you very much for the comment. I understand there are more than one ways to get this done. Thanks for brining this up. I will create another video explaining how this can be done using the match function.
@@realmehranvahedi Worked perfectly for me. I have a VLOOKUP with 28 columns. I knew this was an option but couldn't remember the function. This is not bad advice, this was perfect. Thank you for taking the time to make this video.
Dear awesome viewers!
Thank you for your amazing engagement! Remember, the most liked comment gets pinned - so start liking your favorites now!
I love your questions and will try to respond to as many as I can. For quick generic queries, the comment section is perfect. But need more in-depth, personalized advice? Book a personalized consultations with me via topmate.io/mehran_vahedi. They're ideal for detailed, specific questions.
Your support means the world to me. Let's keep the conversation going!
Thank You!
This is what I have been searching for. Thank you so much for your crystal clear explaination.
Hi @siamk649, You're very welcome! I'm so glad the video helped you out, and I'm happy to hear you found it clear. 😊
Very helpful. After many hours stuck on this specific problem, this video was exactly what I needed.
Hi @amadif1793, it's fantastic to hear that the video was exactly what you needed, especially after being stuck for so long! If you have any suggestions or need more insights on related topics, feel free to share. Thanks for watching!
Thanks so much, there are lots of convoluted solutions online for this but yours is very neat and saved me lots of time!
Hey @martinokereafor, thanks for the kind words! I’m thrilled to hear it saved you time. 🚀 Keep watching for more straightforward tips and tricks. And if there’s anything else you’d like to see simplified, just let me know!
GENIUS!!!!!!!! Just saved my entire day!!
Thank you so much, @jmcclure76! I'm thrilled to hear it made your day easier. If you have any other topics or questions you'd like me to cover in future videos, feel free to share them. Happy Excel-ing!
Thank you very much, you saved me alot of time!
You are very welcome! I'm glad you found it useful!
Thank you very very much.God bless you!
You are very welcome! I am so happy that you found the video useful. Take care!
LEGEND
Thanks, @MickeyLeeBukowski! Just trying to keep the legend alive, one VLOOKUP at a time! 😎"
Spot on, thanks.
You're very welcome! I'm glad you found it useful!
Thank you so much!
You're very welcome Kritanya! I'm happy that you found the video useful. Make sure you subscribe and turn on notifications so you won't miss out on my future Excel tips!
Hello, Can you help me with a query? When we select a name from the drop-down list and once it repeats, the integer should increment on the other sheet in front of the exact name. Looking forward for your response.
Thanks in advance!
Hello @TJ-tk4hz,
Thanks for reaching out with your query! It sounds like you're looking to set up a system where selecting a name from a dropdown list increments a corresponding integer on another sheet each time the name is selected again. You can achieve this using a combination of Excel formulas and features. Here’s a basic approach:
[1] Setup your dropdown list: On your main sheet, set up your dropdown list using Data Validation to list the names.
[2] Track the selections: On a second sheet, create a table with two columns: 'Name' and 'Count'. Every time a name is selected from the dropdown list, you'll record the event here.
[3] Increment the count: You can use a combination of COUNTIF and VLOOKUP functions to update the count. In the 'Count' column next to each name, you could use a formula like the one below:
=COUNTIF(MainSheet!A:A, NameCell)
Replace MainSheet!A:A with the range of your dropdown list, and NameCell with the cell reference that corresponds to the name in the 'Name' column of your second sheet. This setup should increment the count on your second sheet every time a name is selected from the dropdown on the main sheet.
If you need more detailed guidance or further customization, feel free to book a paid consultation with me @ : topmate.io/mehran_vahedi.
Best,
Mehran
What if instead of the column, I wanted to auto-increment the ROW for the lookup_value?
Hello Brian, thanks for the comment. I'm not sure exactly what you mean. Are you trying to auto increment the first argument of the VLOOKUP? The first argument is already being auto incremented once you drag the formula down. See the K4 to N5 range. Once I dragged down the formula which I had applied to the first row the auto increment was applied to the lookup value.
Thank you, cool
Thank you for your kind words! It's great to know the video was helpful to you. Stay tuned-there are plenty more Excel tips and strategies coming your way that I think you'll find just as useful.
this is very helpful, thank you. but i don't understand why is it column B2 not C because that's where Revenue is lined up in.
Hi @m3s69ll,
Thank you for your comment and I'm glad you found the video helpful! To clarify why we use COLUMN(B2) in the formula rather than COLUMN(C2): The COLUMN(B2) function returns the column number of cell B2, which is 2. This is used in the VLOOKUP formula to dynamically select the column index for the lookup value.
In the screenshot and the explanation in the video, COLUMN(B2) is used in the VLOOKUP formula within cell K3 to automatically update the column index parameter. This allows the formula to remain flexible and automatically adjust if columns are added or deleted in the data range. If you want to fetch the "Revenue" data specifically, which is indeed in column C as you noted, we need to ensure that the formula uses COLUMN(C2). This would be COLUMN(C2)-COLUMN($B$2)+1 to correctly point to the third column where the Revenue data starts.
Hope this clears up any confusion! Thanks again for the comment and feel free to leave any other comments, questions and also video suggestions.
Cheers,
Mehran
@ I see why you use B instead of C now. But does it matter if it’s B2 or B3 since they both return 2?
Hi @m3s69ll,
Great question! You’re correct that both B2 and B3 would return the same column number, 2, because they are both in the second column. The choice of B2 over B3 in this context doesn't impact the formula’s functionality, as either will correctly return the column number needed for the COLUMN function within our formula setup.
The specific cell reference (B2 vs. B3) is often chosen for consistency or based on the starting point of the data range in examples. If there's a specific range or header row highlighted as the reference point in explanations or setups, that's generally the one used to maintain clarity.
Thanks for your keen observation, and please keep the questions coming if you have more!
Cheers, Mehran
thank you
You're very welcome. I'm happy you found it useful!
This is a bad advice. You should have used match function instead of column.
Hello, thank you very much for the comment. I understand there are more than one ways to get this done. Thanks for brining this up. I will create another video explaining how this can be done using the match function.
@@realmehranvahedi Worked perfectly for me. I have a VLOOKUP with 28 columns. I knew this was an option but couldn't remember the function. This is not bad advice, this was perfect. Thank you for taking the time to make this video.