I'm searching for a way to change the data area of a diagramm with a drop down menu:

on sheet1 i want to have the diagramm and a drop down menu where i can select from which sheet the diagramm take its data

so something like this:


to this: (pseudo code)


where B1 is the cell where the drop down menu is located

(discalmer: I don't know if there is a command like VLUEOFF() )

in this drop down menu you can choose between the difrent sheets


You're looking for INDIRECT(), as in INDIRECT("$" & "Sheet1" & ".$A$1").

In your example you would use INDIRECT("$" & $B$1 & ".$A$1:$A$10").

Note that this LibreOffice feature is not portable to Microsoft Office's Excel owning to the way that the two suites reference sheets differently. Furthermore, because the "..." quoted parts are literal strings they do not adapt when (if) you insert/delete adjacent cells and their cell references move.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.