Formula for automatically entering information from a master sheet

[Guest]

I'm really drawing a blank on this one!!

In my workbook I'm creating a worksheet for each employee. I would like to
input all information on a master sheet and have it automatically enter to
the correct employee worksheet.

MASTER has the following information: A1=Employee #, B1=Employee name,
A2=000, B2=Jane Doe.

TEMPLATE for Employee worksheet: B3=Employee #, C3=formula that will pull
from MASTER, B4=Employee name, C4=formula that will pull from MASTER

 

[Guest]

If I understand correctly, you want to pull Employee# and Employee Name from
the MASTER. If you have the Employee#, you can get the name using VLOOKUP.

But how do you know which Employee is on which w/sheet?

What are C3 and C4 "pulling" fom the MASTER?

 

[Guest]

Thanks for replying!!

Each worksheet is named with the employees name.

I used TEMPLATE as an example for the name of a employee worksheet.

C3 and C4 are the cells I would like to put the formula in to pull employee
name and number from the MASTER worksheet.

Please help!!

 

[Guest]

Try:

in C3:

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

This will return the worksheet name BUT the w/book must have been saved.

in C4:

=INDEX(Master!A:A,MATCH(C3,Master!B:B,0))

Assumes C3 (worksheet name) is EXACTLY the same as name in MASTER

HTH

 

[Dave Peterson]

When this calculates:
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

It'll return the name of the activesheet--no matter what workbook you're in.

I'd use this:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
(A1 can be any cell on the worksheet with the formula--including that same
cell.)

Try:

in C3:

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

This will return the worksheet name BUT the w/book must have been saved.

in C4:

=INDEX(Master!A:A,MATCH(C3,Master!B:B,0))

Assumes C3 (worksheet name) is EXACTLY the same as name in MASTER

HTH

Thanks for replying!!

Each worksheet is named with the employees name.

I used TEMPLATE as an example for the name of a employee worksheet.

C3 and C4 are the cells I would like to put the formula in to pull employee
name and number from the MASTER worksheet.

Please help!!

 

[Guest]

Hi,

Thanks for helping me, but I think I need to explain myself better.

My workbook has a worksheet for each staff member to calculate budget (75 to
be exact). Instead of typing in everyone's employee # and name I created
another worksheet within the same workbook that list the names and numbers of
the staff members so it can be automatically entered to the correct staff
members worksheet.

On the first worksheet called "Employees", I entered all Employee #'s in
column A and Employee names in column B. I would like this information to
find the correct worksheet and have the information placed in the specified
cell.

So as an example I'm using "TEMPLATE" as the worksheet name for a staff
member. I would like the employee # to be placed in C3 and the employee name
to be placed in C4 automatically from the "Employees" worksheet I created
with everyone's name and number.

Hope I'm explaining it better! Thanks.

When this calculates:
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

It'll return the name of the activesheet--no matter what workbook you're in.

I'd use this:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
(A1 can be any cell on the worksheet with the formula--including that same
cell.)

Try:

in C3:

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

This will return the worksheet name BUT the w/book must have been saved.

in C4:

=INDEX(Master!A:A,MATCH(C3,Master!B:B,0))

Assumes C3 (worksheet name) is EXACTLY the same as name in MASTER

HTH

Thanks for replying!!

Each worksheet is named with the employees name.

I used TEMPLATE as an example for the name of a employee worksheet.

C3 and C4 are the cells I would like to put the formula in to pull employee
name and number from the MASTER worksheet.

Please help!!



:

If I understand correctly, you want to pull Employee# and Employee Name from
the MASTER. If you have the Employee#, you can get the name using VLOOKUP.

But how do you know which Employee is on which w/sheet?

What are C3 and C4 "pulling" fom the MASTER?

:

I'm really drawing a blank on this one!!

In my workbook I'm creating a worksheet for each employee. I would like to
input all information on a master sheet and have it automatically enter to
the correct employee worksheet.

MASTER has the following information: A1=Employee #, B1=Employee name,
A2=000, B2=Jane Doe.

TEMPLATE for Employee worksheet: B3=Employee #, C3=formula that will pull
from MASTER, B4=Employee name, C4=formula that will pull from MASTER

 

[Dave Peterson]

I wouldn't make this automatic. If you make a typo and the automatich routine
fires, then who knows what got updated and what needs to be fixed.

In fact, I would do my best to keep the data in one worksheet, then use
data|filter|autofilter to see the items that I want.

But if you need to have the data separated, I'd do at the end of all the data
input.

You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Hi,

Thanks for helping me, but I think I need to explain myself better.

My workbook has a worksheet for each staff member to calculate budget (75 to
be exact). Instead of typing in everyone's employee # and name I created
another worksheet within the same workbook that list the names and numbers of
the staff members so it can be automatically entered to the correct staff
members worksheet.

On the first worksheet called "Employees", I entered all Employee #'s in
column A and Employee names in column B. I would like this information to
find the correct worksheet and have the information placed in the specified
cell.

So as an example I'm using "TEMPLATE" as the worksheet name for a staff
member. I would like the employee # to be placed in C3 and the employee name
to be placed in C4 automatically from the "Employees" worksheet I created
with everyone's name and number.

Hope I'm explaining it better! Thanks.

When this calculates:
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

It'll return the name of the activesheet--no matter what workbook you're in.

I'd use this:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
(A1 can be any cell on the worksheet with the formula--including that same
cell.)

Try:

in C3:

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

This will return the worksheet name BUT the w/book must have been saved.

in C4:

=INDEX(Master!A:A,MATCH(C3,Master!B:B,0))

Assumes C3 (worksheet name) is EXACTLY the same as name in MASTER

HTH

:

Thanks for replying!!

Each worksheet is named with the employees name.

I used TEMPLATE as an example for the name of a employee worksheet.

C3 and C4 are the cells I would like to put the formula in to pull employee
name and number from the MASTER worksheet.

Please help!!



:

If I understand correctly, you want to pull Employee# and Employee Name from
the MASTER. If you have the Employee#, you can get the name using VLOOKUP.

But how do you know which Employee is on which w/sheet?

What are C3 and C4 "pulling" fom the MASTER?

:

I'm really drawing a blank on this one!!

In my workbook I'm creating a worksheet for each employee. I would like to
input all information on a master sheet and have it automatically enter to
the correct employee worksheet.

MASTER has the following information: A1=Employee #, B1=Employee name,
A2=000, B2=Jane Doe.

TEMPLATE for Employee worksheet: B3=Employee #, C3=formula that will pull
from MASTER, B4=Employee name, C4=formula that will pull from MASTER

 

[Guest]

Thank you so much for everyones input.

I wouldn't make this automatic. If you make a typo and the automatich routine
fires, then who knows what got updated and what needs to be fixed.

In fact, I would do my best to keep the data in one worksheet, then use
data|filter|autofilter to see the items that I want.

But if you need to have the data separated, I'd do at the end of all the data
input.

You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm