help: how to get tab name

Alek_Fantomas · General User Joined: 31 Mar 2007 Posts: 5

Hi to you all! I'm new here. I'm Italian and sorry for bad english
this is my problem: I write the function to know the minimum value between cell in diffrent tabs with MIN() command. In another cell I shuld display the name ov the tab where is situated the value taken.
How I can do?

I'm waitin for your help. Thanks
_________________
Hasta la vista....

Villeroy · Posted: Sun Apr 01, 2007 2:07 am Post subject:

Name of this sheet:
=MID(CELL("Filename");FIND("#$";CELL("Filename"))+2;1000)
Name of next sheet (Sheet2). You have to specify the sheet name once in the formula. Then you can copy the formula to other sheets and rename sheets without losing the reference:
=MID(CELL("Filename";Sheet2.A1);FIND("#$";CELL("Filename";Sheet2.A1))+2;1000)
1000 is just a big number to return the rest of string. See help on MID.
Don't translate "Filename". See help on CELL.
A simple macro for translating english function names.
http://www.oooforum.org/forum/viewtopic.phtml?t=23270
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

Alek_Fantomas · General User Joined: 31 Mar 2007 Posts: 5

Hi Villeroy

Thanks for reply! I think I wast understood!
I need to specify the name of the tab where I find the min value with the MIN() function. I think that this function have the same name in the english version of OOo. I use the Italian one.
Example:
- In Sheet1 I have in A1 the value 50
- in Sheet2 I have in A1 the value 100
- in Sheet3 I have in A1 the function to find the minimum value "=MIN(Sheet1.A1;Sheet2.A1)"
- I need in Sheet3 A2 to display where the min value was find, in this case Sheet1.A1

I hope I'm clear now!

I hope in your help again!

Thanks a lot! Wink

_________________
Hasta la vista....

Villeroy · Posted: Sun Apr 01, 2007 3:14 pm Post subject:

The usual lookup-functions don't work across sheets.
If Menu:Data>Consolidation is not applicable then you can create links to all A1-values.
A1 =Sheet1.A1
A2 =Sheet2.A1
A3 =Sheet3.A1
...
=MATCH(MIN(A1:Axx);A1:Axx;0) returns the position where the minimum has been found.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

Alek_Fantomas · General User Joined: 31 Mar 2007 Posts: 5

Thank you very very much!!

I'm sorry that we can't calculate across the sheets. I will try something else.

Thank you very much again!
_________________
Hasta la vista....