Getting sheet name and info from cell

[tripflex]

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!

 

[Bernard Liengme]

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

 

[tripflex]

Awesome worked perfect! Thank you so much!!

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

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!

 

[Bernard Liengme]

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

Awesome worked perfect! Thank you so much!!

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

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!