Alright, here's my question. I have about a 100 sheet workbook for a project
my company has upcoming. All the sheets are numbered 1-100. I also have
another sheet that has the description of each pay item on ROW B of the sheet.


Well here's my question. I'm trying to get a function that i can just copy
and paste in each sheet instead of changing the number on each sheet.

For instance...

I would be using =Descriptions!B2 for sheet 2, and then =Descriptions!B3 for
sheet 3 and so on.

My question is does anyone know how i can get the sheet name so then i could
just copy and paste one function that would be like

=Descriptions!B(SheetName)

So that way i can just copy and paste instead of having to change it each
time.

I found this function to get the sheet name but can't seem to incorporate it
into the =Descriptions!B

MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))+1

Theoretically i would want to be able to use the function like this

=Descriptions!B(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))+1)

Where the entire code of
"MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))+1" would just change
to whatever the sheet is called whether it be 1,2,3,4,5 and so on.

Basically so the end result would be =Description!B2 or =Description!B3 ...
the last number depending on the name of the sheet which they are numbered
1-100.

Thanks!

This get you the sheet name (the file has to have been saved at least once
for this to work)
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)
So you need
=INDIRECT("Descriptions!B"&MID(CELL("Filename",A1) ,FIND("]",CELL("Filename",A1))+1,255))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"tripflex" wrote in message
...
Alright, here's my question. I have about a 100 sheet workbook for a
project
my company has upcoming. All the sheets are numbered 1-100. I also have
another sheet that has the description of each pay item on ROW B of the
sheet.


Well here's my question. I'm trying to get a function that i can just copy
and paste in each sheet instead of changing the number on each sheet.

For instance...

I would be using =Descriptions!B2 for sheet 2, and then =Descriptions!B3
for
sheet 3 and so on.

My question is does anyone know how i can get the sheet name so then i
could
just copy and paste one function that would be like

=Descriptions!B(SheetName)

So that way i can just copy and paste instead of having to change it each
time.

I found this function to get the sheet name but can't seem to incorporate
it
into the =Descriptions!B

MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))+1

Theoretically i would want to be able to use the function like this

=Descriptions!B(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))+1)

Where the entire code of
"MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))+1" would just
change
to whatever the sheet is called whether it be 1,2,3,4,5 and so on.

Basically so the end result would be =Description!B2 or =Description!B3
...
the last number depending on the name of the sheet which they are numbered
1-100.

Thanks!

Awesome worked perfect! Thank you so much!!

"Bernard Liengme" wrote:

This get you the sheet name (the file has to have been saved at least once
for this to work)
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)
So you need
=INDIRECT("Descriptions!B"&MID(CELL("Filename",A1) ,FIND("]",CELL("Filename",A1))+1,255))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"tripflex" wrote in message
...
Alright, here's my question. I have about a 100 sheet workbook for a
project
my company has upcoming. All the sheets are numbered 1-100. I also have
another sheet that has the description of each pay item on ROW B of the
sheet.


Well here's my question. I'm trying to get a function that i can just copy
and paste in each sheet instead of changing the number on each sheet.

For instance...

I would be using =Descriptions!B2 for sheet 2, and then =Descriptions!B3
for
sheet 3 and so on.

My question is does anyone know how i can get the sheet name so then i
could
just copy and paste one function that would be like

=Descriptions!B(SheetName)

So that way i can just copy and paste instead of having to change it each
time.

I found this function to get the sheet name but can't seem to incorporate
it
into the =Descriptions!B

MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))+1

Theoretically i would want to be able to use the function like this

=Descriptions!B(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))+1)

Where the entire code of
"MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))+1" would just
change
to whatever the sheet is called whether it be 1,2,3,4,5 and so on.

Basically so the end result would be =Description!B2 or =Description!B3
...
the last number depending on the name of the sheet which they are numbered
1-100.

Thanks!

You are most welcome! Thanks do the feedback.
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"tripflex" wrote in message
...
Awesome worked perfect! Thank you so much!!

"Bernard Liengme" wrote:

This get you the sheet name (the file has to have been saved at least
once
for this to work)
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)
So you need
=INDIRECT("Descriptions!B"&MID(CELL("Filename",A1) ,FIND("]",CELL("Filename",A1))+1,255))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"tripflex" wrote in message
...
Alright, here's my question. I have about a 100 sheet workbook for a
project
my company has upcoming. All the sheets are numbered 1-100. I also have
another sheet that has the description of each pay item on ROW B of the
sheet.


Well here's my question. I'm trying to get a function that i can just
copy
and paste in each sheet instead of changing the number on each sheet.

For instance...

I would be using =Descriptions!B2 for sheet 2, and then
=Descriptions!B3
for
sheet 3 and so on.

My question is does anyone know how i can get the sheet name so then i
could
just copy and paste one function that would be like

=Descriptions!B(SheetName)

So that way i can just copy and paste instead of having to change it
each
time.

I found this function to get the sheet name but can't seem to
incorporate
it
into the =Descriptions!B

MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))+1

Theoretically i would want to be able to use the function like this

=Descriptions!B(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))+1)

Where the entire code of
"MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,
LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))+1" would just
change
to whatever the sheet is called whether it be 1,2,3,4,5 and so on.

Basically so the end result would be =Description!B2 or =Description!B3
...
the last number depending on the name of the sheet which they are
numbered
1-100.

Thanks!