If-Statements and Conditional Formatting Help

[lauren.cbj]

Excel version: 2010
OS: Windows 7

So I'm trying to do something for my boss, she has a Source Worksheet (source) that has multiple data entries. I've got about 15 columns of data, and she wants this information to be shown on separate worksheets by department - in the sense that if the row's Column H value is a specific department (text entry), the whole row replicates/is shown on that department's spreadsheet/destination worksheet. Is this possible? I looked at pivot tables, but that doesn't do what I want, and I'm not sure I can split my links to go to specific places based on text entries. I'm not familiar with conditional formatting or if-statements, but I have a feeling one of those might help. Could someone help me understand this? I'm not sure if I explained this right but I'm trying.

Thanks!

[mikeTRON]

I would likely use pivot tables to summarize the data.

Can you post a sample workbook with examples of what you want?

[lauren.cbj]

Here's part of the source: You'll see it has some information, namely Column F with "Department".
Source worksheet.jpg

Based on info in Column F, I want to have that row replicate or show up on a separate worksheet by department, so all items with a Column F value of "Airport" go to "Airport", etc. I don't want to have to filter to get this to work, just to have it automatically generate on the destination sheets.
Destination Worksheet.jpg

How do I make that happen? Is it possible?

[newdoverman]

Yes, it can be done by formula. Please upload a workbook instead of a picture of the workbook. No one can work with a picture without considerable effort to replicate what you have.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[newdoverman]

Here is an example based on your pictures as much as I could easily see. You can adapt this, I think directly into your workbook.

Note that the headers for the columns for the Airport worksheet are 1 row down from those in the Source WS worksheet

This is the formula entered into A3 of the Airport worksheet and filled across and down with made up data on a Source WS worksheet. This formula will accommodate up to row 1000 on Source WS. If the data is going to be greater than that, increase the 1000 in the formula to whatever you need to give LOTS of room for the source data.

All other department worksheets would be the same except for the name in A1 of the worksheet.

Please Login or Register  to view this content.

This is an array formula entered with Ctrl + Shift + Enter When you have correctly entered the formula Excel will surround the formula with {}. Don't manually enter those....Excel has to do it for you.

[lauren.cbj]

Oh, sorry, I misunderstood the directive! Here's part of the workbook, would your formula still work? I've set up my destination sheets and reworked my headers slightly.

Test Workbook for Excel formatting.xlsx

[newdoverman]

I have worked with your file. I found that the headers that you used on the individual worksheets were not consistent. I made them all match the source. Also, there appears to be inconsistencies in the naming of the departments. These must be absolutely consistent....to the letter. These names are then used in A1 of the appropriate worksheets. I suggest that you create a drop down listing with the department names to be used in column H. I have supplied you with this in the example. To add departments to the list just type the name directly under the table on the DEPARTMENTS worksheet (Red tab).

There are some tabs that don't have data and that is because there are no matching departments for what is entered in A1 of the worksheets affected.

[lauren.cbj]

I have worked with your file. I found that the headers that you used on the individual worksheets were not consistent. I made them all match the source. Also, there appears to be inconsistencies in the naming of the departments. These must be absolutely consistent....to the letter. These names are then used in A1 of the appropriate worksheets. I suggest that you create a drop down listing with the department names to be used in column H. I have supplied you with this in the example. To add departments to the list just type the name directly under the table on the DEPARTMENTS worksheet (Red tab).

There are some tabs that don't have data and that is because there are no matching departments for what is entered in A1 of the worksheets affected.

You, sir, truly are a guru. I thought I had fixed all that. Sheesh. Okay, thank you so much. I'm going to fix those items. I just made a test entry and it looks like it worked correctly. How did you do the formula, where did you put it? I have a feeling I'll need to make more entries and department sheets, so I'll need to know how to properly maintain this formula.

Thank you so much!

[newdoverman]

This is the formula and it is first entered in A3 of every department worksheet. It is then filled across under each header and then filled down.

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

The formula is exactly the same on each worksheet. It gets the value it requires to fill in the worksheet from the value in A1 of that worksheet.

Remember, the department names must be absolutely consistent. That is why I made a drop down Data Validation list for you. When you add data to the Source worksheet, copy the drop down list in column H then delete the entry that is brought with it and choose from the drop down list.

[lauren.cbj]

I did see that. Thank you so much! You have made my life so much better. My boss is very impressed with your knowledge! Thank you!

[newdoverman]

Thank you for the feedback. I'm glad to have been able to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.