Set for example A1 to page name

H

Hamidam

Hi, with page name i mean, those at the bottom of excel, i really don'
know what their called, i hope you understand.:rolleyes:

I would like to set A1 to the name that is given to the page name, i
the page name is changed then A1 will do the same...

Thanks alot / Jennife
 
G

Gord Dibben

Those are called Worksheet Tabs.

The word "Pages" is generally used when speaking of printed pages.

To place the sheet tab name into A1 enter this formula in A1.

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

The A1 in the formula can be any cell reference, but must have some cell
reference

NOTE: workbook must have been saved at least once in order for the formula to
return the sheet name.

For more info on this see Bob Phillips' site.

http://www.xldynamic.com/source/xld.xlFAQ0002.html


Gord Dibben MS Excel MVP
 
B

Biff

You mean the Worksheet (aka Tab) name.

Enter this formula in A1:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

The file must already exsist. In other words, the file has been saved and
has a file name.

Biff
 
H

Hamidam

I see that you have almost identicall answers so the problem not working
is probably mine!

I have a little problem with the filename... i tried C3 here (A1 was
busy) my file name is :

Mall Packlista Mass Expo AB 2006.xls

=MID(CELL("Mall Packlista Mass Expo AB 2006",C3),FIND("]",CELL("Mall
Packlista Mass Expo AB 2006",C3))+1,255)

but there is some form of problem with formula... i have tried also
with .xls but with no sucess... :(

Jennifer
 
H

Hamidam

I see that you have almost identicall answers so the problem not working
is probably mine!

I have a little problem with the filename... i tried C3 here (A1 was
busy) my file name is :

Mall Packlista Mass Expo AB 2006.xls

=MID(CELL("Mall Packlista Mass Expo AB 2006",C3),FIND("]",CELL("Mall
Packlista Mass Expo AB 2006",C3))+1,255)

but there is some form of problem with formula... i have tried also
with .xls but with no sucess... :(

Jennifer
 
G

Gord Dibben

Sorry for the confusion.

You saw "Filename" in quotes and expected to enter the actual name.

Our mistake in not mentioning that up front.

Do not type in the actual filename.

Copy/paste the exact formula that Biff and I posted.

It doesn't matter if A1 is "busy".


Gord
 
H

Hamidam

Well, it's not working... i get these formula error, i have tried al
different versions, i guess the problem could be different language o
excel? (noo!?) could i send you the file if possible so maybe you ca
figure it out? :)

Jennife
 
D

Dave Peterson

Go back to Gord's original post and just copy his formula into the formula bar
(with A1 selected).

If that doesn't work, post what you tried.

And make sure that your workbook has been saved at least once!
 
H

Hamidam

I did exactly what you told just copied

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

and inserted it A1 and tested also in C3 but same problem comes up...
some error in the formula... hmm...? i should probably mention that
have like 21 different sheets, and also uses several references to a
outside file (settings) but i that shouldn't matter or??

Wait a minute, just about to try to create a clean new file to se
wether it works...

Noo, it still doesn't work...

Check out the attached .gif file... (jpeg was to big

+-------------------------------------------------------------------
|Filename: Formula Error.GIF
|Download: http://www.excelforum.com/attachment.php?postid=5151
+-------------------------------------------------------------------
 
P

Peo Sjoblom

If you are using European delimiters change the commas to semicolon , ;

It's better to use something else than excel forum since you have to be
logged on to be able to view or download files
and many regulars are connected directly via news servers (excel forum is
piggy back riding on msnews.microsoft.com)


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
 
D

Dave Peterson

If Peo's suggestion to use semicolons for the separator didn't fix it, you could
try this:

Open excel
Open your workbook
select the cell that should get the formula
hit alt-f11 (to get to the VBE--where macros live)
hit ctrl-g (to see the immediate window)

Type this on one line and hit enter:

activecell.Formula =
"=MID(CELL(""Filename"",A1),FIND(""]"",CELL(""Filename"",A1))+1,255)"

Notice that it's the same formula that Gord suggested, but the double quotes
have be doubled-up.

If the line above wrapped, try copying these two lines and pasting into the
immediate window (and then hit enter):

activecell.Formula = _
"=MID(CELL(""Filename"",A1),FIND(""]"",CELL(""Filename"",A1))+1,255)"


I did exactly what you told just copied

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

and inserted it A1 and tested also in C3 but same problem comes up....
some error in the formula... hmm...? i should probably mention that i
have like 21 different sheets, and also uses several references to an
outside file (settings) but i that shouldn't matter or??

Wait a minute, just about to try to create a clean new file to see
wether it works...

Noo, it still doesn't work...

Check out the attached .gif file... (jpeg was to big)

+-------------------------------------------------------------------+
|Filename: Formula Error.GIF |
|Download: http://www.excelforum.com/attachment.php?postid=5151 |
+-------------------------------------------------------------------+
Click to expand...
 
H

Hamidam

Peo, your solution has brought me one step closer, i don't have any
problem with formula errors anymore since i pasted this line

=MID(CELL("Filename";A1);find("]";CELL("Filename";A1))+1;255)

but there arises a new but probably not as problematic problem (ppp:)
), the field now says #NAMN? (in english #NAME?)

Some calculation error, a box with a lightning logo is visible and if I
click it and choose "Visa beräkningsteg" (Show calculationsteps),
presses "Utvärdera" (Calculate) several times i get these messages in
order.

#NAMN?(CELL("Filename";A1);find("]";CELL("Filename";A1))+1;255)

#NAMN?("C:\[Bok1.xls]Blad1";find("]";CELL("Filename";A1))+1;255)

#NAMN?("C:\[Bok1.xls]Blad1";#NAMN?("]";CELL("Filename";A1))+1;255)

#NAMN?("C:\[Bok1.xls]Blad1";#NAMN?("]";"C:\[Bok1.xls]Blad1")+1;255)

#NAMN?("C:\[Bok1.xls]Blad1";#NAMN?+1;255)

#NAMN?("C:\[Bok1.xls]Blad1";#NAMN?;255)

#NAMN?


Notice that Blad1 is the same as Sheet1

I should also mention that this line

=CELL("filename")

gives me

C:\[Bok1.xls]Blad1

with no errors, so I guess something with the find method could be the
problem?

To Dave, I tried your solution but nothing happened with the cell??
hmm?

Thanks alot everyone, aprecciate it! :) :) :)
 
D

Dave Peterson

Try that one suggestion once more.

Make sure you hit enter after you paste that line (or both lines).

Then look at the formula bar. What do you see?

If you see nothing, then what you did wasn't correct.

You should see the same formula translated into whatever language you're using.
Peo, your solution has brought me one step closer, i don't have any
problem with formula errors anymore since i pasted this line

=MID(CELL("Filename";A1);find("]";CELL("Filename";A1))+1;255)

but there arises a new but probably not as problematic problem (ppp:)
), the field now says #NAMN? (in english #NAME?)

Some calculation error, a box with a lightning logo is visible and if I
click it and choose "Visa beräkningsteg" (Show calculationsteps),
presses "Utvärdera" (Calculate) several times i get these messages in
order.

#NAMN?(CELL("Filename";A1);find("]";CELL("Filename";A1))+1;255)

#NAMN?("C:\[Bok1.xls]Blad1";find("]";CELL("Filename";A1))+1;255)

#NAMN?("C:\[Bok1.xls]Blad1";#NAMN?("]";CELL("Filename";A1))+1;255)

#NAMN?("C:\[Bok1.xls]Blad1";#NAMN?("]";"C:\[Bok1.xls]Blad1")+1;255)

#NAMN?("C:\[Bok1.xls]Blad1";#NAMN?+1;255)

#NAMN?("C:\[Bok1.xls]Blad1";#NAMN?;255)

#NAMN?

Notice that Blad1 is the same as Sheet1

I should also mention that this line

=CELL("filename")

gives me

C:\[Bok1.xls]Blad1

with no errors, so I guess something with the find method could be the
problem?

To Dave, I tried your solution but nothing happened with the cell??
hmm?

Thanks alot everyone, aprecciate it! :) :) :)
Click to expand...
 
D

Dave Peterson

One more thing...

What language are you using?

Maybe someone knows what =mid() translates to (as well as how
=cell("Filename",...) translates.
Peo, your solution has brought me one step closer, i don't have any
problem with formula errors anymore since i pasted this line

=MID(CELL("Filename";A1);find("]";CELL("Filename";A1))+1;255)

but there arises a new but probably not as problematic problem (ppp:)
), the field now says #NAMN? (in english #NAME?)

Some calculation error, a box with a lightning logo is visible and if I
click it and choose "Visa beräkningsteg" (Show calculationsteps),
presses "Utvärdera" (Calculate) several times i get these messages in
order.

#NAMN?(CELL("Filename";A1);find("]";CELL("Filename";A1))+1;255)

#NAMN?("C:\[Bok1.xls]Blad1";find("]";CELL("Filename";A1))+1;255)

#NAMN?("C:\[Bok1.xls]Blad1";#NAMN?("]";CELL("Filename";A1))+1;255)

#NAMN?("C:\[Bok1.xls]Blad1";#NAMN?("]";"C:\[Bok1.xls]Blad1")+1;255)

#NAMN?("C:\[Bok1.xls]Blad1";#NAMN?+1;255)

#NAMN?("C:\[Bok1.xls]Blad1";#NAMN?;255)

#NAMN?

Notice that Blad1 is the same as Sheet1

I should also mention that this line

=CELL("filename")

gives me

C:\[Bok1.xls]Blad1

with no errors, so I guess something with the find method could be the
problem?

To Dave, I tried your solution but nothing happened with the cell??
hmm?

Thanks alot everyone, aprecciate it! :) :) :)
Click to expand...
 
P

Peo Sjoblom

Well you came to the right person, I am Swedish. I don't have access to the
computer where I have a Swedish version but

MID is the same as EXTEXT and FIND is HITTA and CELL is the same in
Swedish and English

so if you replace MID and FIND, then use semicolons. I believe the English
word for "filename" will work if not use "filnamn"


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
 
Top