Pulling data in Excel

[DG_Silva]

Hi there, I was wondering if there were an Excel wizards in here who could help me out please? I'm trying to find out if there is a way of creating a dropbox using sheets? What I want to do is have a basic UI on the first sheet that would highlight things like weekly target, balance remaining, wtd total, etc, but it would have a dropbox on there so you could select the week you are getting data from. Has anybody got any ideas please? I have tried using Google, but no joy (I can't get the search engine to understand what exactly I want).

 

[TenEightyOne]

Hi there, I was wondering if there were an Excel wizards in here who could help me out please? I'm trying to find out if there is a way of creating a dropbox using sheets? What I want to do is have a basic UI on the first sheet that would highlight things like weekly target, balance remaining, wtd total, etc, but it would have a dropbox on there so you could select the week you are getting data from. Has anybody got any ideas please? I have tried using Google, but no joy (I can't get the search engine to understand what exactly I want).

Enable the Developer (or Visual Basic) tab, you can add dynamic objects in there. You'll need to address them via code depending on your Excel/Office version.

I'd advise considering using a date/time picker object rather than a dropdown. If you want me to have a look once you've worked something up then I'd be happy to.

 

[DG_Silva]

I'm using OpenOffice. Basically, I've got 13 sheets containing data, numbered 1-13 (for each week in the first quarter), and I want to be able call the data from that sheet after choosing the week number from drop down box. I've got the drop down box working fine, but I don't know how to get that value referring to the sheet number. Using =$B$2 doesn't work. I don't really want to use VB code, because I need it accessible on GoogleDocs.

What about using vlookup and getting the user to type in the number? Let's say we have WTD sales value in cell B10 in each sheet, I need to refer that value in cell B5 on sheet 'WTD'. Any help with the syntax please? I've seen a lot of references to INDIRECT, but could you help me please?

 

[Barra333]

This might be an over simplified answer, and I don't know whether Open Office works the same, but wouldn't you need a Sheet! argument there to tell it which sheet to look at?

eg =Sheet1!$B$2

 

[DG_Silva]

Yeah, that's fine, but I need it to refer to a choice of sheets, not just the one. This is what I think would work, but it keeps coming up with error 508:

=VLOOKUP($WTD.A5,INDIRECT("'"&$B$2&"'"&".$B$1:$J$2"),2,0)

 

[TenEightyOne]

I'm using OpenOffice.

So not

Excel

Try this link; https://suite.io/mark-alexander-bain/xxa2aa

As @Barra333 says you need to address the sheet at the beginning of the object identifier. In Office you could do this with Sheetn! or workbook.sheets("xxxxx") and use a loop to step through sheets. You could also use a foreach if you're likely to have a dynamic number of sheets. I'm not sure in OpenOffice, not even sure why you'd use it in a work context?

 

[DG_Silva]

I thought I'd give it a try on GoogleDocs, as ultimately that is where it is going to end up, it's sooooooo much easier, lol, as easy as this:

=INDIRECT(CONCATENATE(B2, "!B2"))