Solved

Excel Formula across Multiple Sheets

Posted on 2016-09-14
9
65 Views
Last Modified: 2016-09-15
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)=STORE1011!B5, (FINAL!B6:B500),0)
0
Comment
Question by:Kim Patton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41798124
You can do this using Pivot Tables.

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.
1
 

Author Comment

by:Kim Patton
ID: 41798234
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.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41798245
Can you upload some dummy data with a sample of how you would like the output.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Kim Patton
ID: 41798299
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
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41798316
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.
0
 

Author Comment

by:Kim Patton
ID: 41798358
Of course! I will send you the info I get when I try to do a PivotTable.  Any help is greatly appreciated.
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41799454
Hi Kim,

See attached file with several new tabs.

I have converted your Data tab to a Table format. By doing this any increase (or decrease) in the data rows will be reflected in the Pivots after they have been refreshed.

Pivot - Created Pivot Table and dragged in the columns you required based on your Needed sheet.
   Store - Page Report
   Employee - Row
   Pay Rate etc - Values
Format these columns as required, I have done the Percent column as %

Then refer to Sheet3 for screen shots of how the other sheets were created.

Once you have the separate store sheets, the original Pivot sheet can be deleted unless you want to keep it as a summary for all stores.

If you are going to only refer to one store at a time, you might be able to just use this single sheet by using the dropdown at the top to filter by Store number.

Thanks
Rob
Store-Details.xlsx
0
 

Author Closing Comment

by:Kim Patton
ID: 41799661
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!
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41799677
Glad to be of help; thanks for your patience overnight.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question