98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Need to understand the means
Need to understand the means
Resolved · Urgent Priority · Version 365
Tue 12 Sep 2017, 13:19Delegate Sanjay said...
Sanjay has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Need to understand the means
Hi,
I have to make a drop down menu in a sheet which takes the input from another sheet "Sonus Layout".
A string is already there and I am not able to understand the string.
Will it be possible for you to explain me the the below mention string.
=INDIRECT("drop" & VLOOKUP($H117,SonusDRLook,3,0))
Thanks
Sanjay
Tue 12 Sep 2017, 13:59Trainer Jens said...
RE: need to understand the means
Hi Sanjay,
Thank you for the forum question.
The Indirect function is a lookup & reference function. If you in A1 has 100, in B1 type A1 and in C1 type =Indirect(B1) the Indirect function will return 100 in C1. The Indirect function sees whatever you type inside the brackets as a reference.
You must have a number of range names in the workbook.
The Vlookup looks up information in column 3 in a range called SonusDrLook. Lets just imagine it looks up the word "Test". Then the Indirect function will get the reference "dropTest" wich will also be a range name.
I hope this make sense.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 12 Sep 2017, 14:10Delegate Sanjay said...
RE: need to understand the means
Thanks Jens for the information.
will you please let me know if the given string are correct.
"=INDIRECT("drop" & VLOOKUP($H117,SonusDRLook,3,0))".
As when i am saving this file it shows that this contain some error.
Thanks
Sanjay
Tue 12 Sep 2017, 14:26Trainer Jens said...
RE: need to understand the means
Hi Sanjay,
The string is correct if you have a range in the workbook with the name SonusDRLook and if you have another range in your workbook with the name "drop"+ what the Vlookup looks up in the range "SonusDRLook". If it is for a dropdown list the Indirect function should only reference one column.
If you want I can have a look at your file:
You can send it to:
info@stl-training.co.uk
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 12 Sep 2017, 14:35Delegate Sanjay said...
RE: need to understand the means
please check your mail
Tue 12 Sep 2017, 15:16Trainer Jens said...
RE: need to understand the means
Please check your mail
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Tue 19 Sep 2017: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Quick Absolute Cell ReferencesWhen entering cell references in a formula you probably click the cell, or you may type in the cell reference. If you require any of the 4 variations, press the F4 key now before you press Enter to toggle around the relative and absolute entries ($signs). |
