asked on
Excel Formula across Multiple Sheets
I have one sheet full of data (sheet titled DATA) of employees names and store numbers. I need to create a sheet for each unique store number, listing the employees names.
I have listed the store number at the top of the new sheet. For any matching store number in column A of DATA sheet, I need it to return the Employee Name in Column B of DATA sheet. And the employee names need to go on a new Sheet (STORE 1011)
My (incorrect) formula:
IF((DATA!A6:A500)=STORE101
I have listed the store number at the top of the new sheet. For any matching store number in column A of DATA sheet, I need it to return the Employee Name in Column B of DATA sheet. And the employee names need to go on a new Sheet (STORE 1011)
My (incorrect) formula:
IF((DATA!A6:A500)=STORE101
Microsoft ExcelMicrosoft Office
Last Comment
Rob Henson
ASKER
Thank you! Unfortunately the PivotTable isn't displaying the type of report I need. I will continue to play with it, but any suggestions would be helpful.
It isn't allowing me to maintain column headers and data in each column.
I would like the PivotTable to be sorted by Store #.
with these column headings:
Name Pay Rate Sales Percent Ranking New Pay Rate
I am able to create the pivot table, and sort by store. But when I try to add data, it just isn't doing what I want.
It isn't allowing me to maintain column headers and data in each column.
I would like the PivotTable to be sorted by Store #.
with these column headings:
Name Pay Rate Sales Percent Ranking New Pay Rate
I am able to create the pivot table, and sort by store. But when I try to add data, it just isn't doing what I want.
Can you upload some dummy data with a sample of how you would like the output.
ASKER
I have two tabs - One for the DATA and one displaying what I need for each Store number. I really appreciate your help.
SAMPLE.xlsx
SAMPLE.xlsx
Only on phone at the minute. Can this wait until tomorrow when back at work? If not I should be able to get chance to use pc tonight.
File looks fine for using Pivot tables though.
File looks fine for using Pivot tables though.
ASKER
Of course! I will send you the info I get when I try to do a PivotTable. Any help is greatly appreciated.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
THANK YOU! The explanation and the file you provided are exactly what I was trying to do. Thank you again. I appreciate the expert help!
Glad to be of help; thanks for your patience overnight.
Setup a pivot table on a separate sheet and use the store number as a Page Report and Employee name as a Row value.
With the cursor in the pivot there will be an extra tab on the ribbon. At the left hand end of the ribbon there is a button for Pivot Options. Alongside it there is a dropdown and one of the options is to create page reports. Doing this will create a copy of the Pivot on separate tabs for each item in the Page Filters.