Jump to content


Photo

drop down lists and Indirect function right direction?


  • Please log in to reply
2 replies to this topic

#1 diyrednef

diyrednef

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 27 December 2017 - 01:06 PM

Hi everyone, hopefully some kind person can help me.

Apologies firstly as I have very limited excel experience but I picked up a few excel books 3 weeks ago in order to sort out a problem I get as a Wind turbine tech and despite working on this for a ton of hours I am no further forward and I am going around in circles.

 

I work on 53 wind turbines total, and these at 5 different wind farm locations. each turbine has a different i.p address which frequently have to be typed into a browser every time we need to access that turbine.

 

I just need to be able to select a particular turbine that then automatically go to that i.p address in a browser.

 

I have got a drop down list working ok but a 2nd data validation box to reference the first only works on 2 wind farms (glassmoor and deeping) I have used a,  =indirect(A2) but it comes up with an error of "the source currently evaluates to an error" when selecting ok.

 

Am I using the best route to achieve this goal and if so what could I be doing wrong?

 

 

 

 

 

 

 

 

 

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 28 December 2017 - 02:47 PM

Hi diyrednef,

 

As is often the case, there are a few different ways that you could do this. The simplest is very similar to what you already have - splitting each wind farm into a separate table and then using the INDIRECT function to retrieve the appropriate table.

 

I have attached a working example:

 

Attached File  Turbines.xlsx   16.19KB   66 downloads

Turbines.png

 

This example uses structured table references and the INDIRECT function.

You can see more about creating tables in: Expert Skills Lesson 1-11 Convert a range into a table and add a total row.

Structured table references are covered in: Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference.

Using the INDIRECT function to link dropdown list validations together is shown in: Expert Skills Lesson 4-8 Create two linked drop-down lists using range names.

 

An alternative way to achieve this would be to use a single table with a complex OFFSET function that retrieves records for the selected wind farm. You can see an example of this technique in: Expert Skills Lesson 4-6 Create dynamic formula-based range names using the OFFSET function.

 

Finally, in the latest version of Excel 2016 this could be achieved using a single table and the Get & Transform tool, which now allows you to retrieve records from tables according to parameters. The Get & Transform tool is covered in great depth in Session 11: Create Get & Transform queries.

 

I hope this is the solution you were looking for, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 diyrednef

diyrednef

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 28 December 2017 - 04:41 PM

Fantastic, I could not get the =INDIRECT to work but this makes it so much clearer and now I have the excel bug I will work my way thro the other examples you have kindly given links too.

 

Again many thanks for finding the time to help. Cheers






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users