Automatically Populate Data to Multiple Sheets

lillianjbrown · #1 06-22-2009, 08:14 AM

My goal is to have the data from the "Main" worksheet to automatically populate to the appropriate cells within the other worksheets based on the information needed for each worksheet.

Thanks.

JBeaucaire · #2 06-22-2009, 12:22 PM

By adding some "Key" columns at the end of your data set, the data can flag itself for which sheet it should "flow" to in realtime. You can hide those columns AC:AE if you wish.

I also turned on Excel's "LIST" feature since you were already using the AutoFilter. LIST has Autofilter built into it, too.

The LIST feature bounds your data set in a blue box. When you put your cursor IN the data, the list expands by one row offering you a new place to add new data. If you enter data into ANY cell in the new row, the dataset permanently expands to incorporate this line...AND any cells that have special formatting or formulas is copied for you into the new row. So your data validation lists are copied as well as my formulas in the columns AC:AE.

This makes maintenance of an expanding "list" quite simple.

Anyway, with those helper columns in AC:AE in place, each of your Stack and Rank sheets simply pulls over the values as they are indexed in their own specific column on "MAIN".

Have a look.

lillianjbrown · #3 06-28-2009, 07:11 PM

JB -

Thanks so much for your assistance. I have taken your information and applied it to the other worksheets. However, I am experiencing some problems with a couple of the worksheets and need further assistance with some of the other worksheets.

Here they are:

1) The LEADERSHIP TEAM MATRIX worksheet finds three items but only two are shown on the worksheet. I have attempt to troubleshoot by cross checking all the cells but find nothing. one and need to know if I can

2) The RETENTION RISK worksheet finds 17 items but only 15 are shown. I would like to populate the data from AI:AK for this worksheet but it does not let me. Is there a way to make this happen?

3) For the KEY ETHNIC TALENT-DM DIR LEVEL & KEY ETHNIC TALENT-SMs LEVEL worksheets is it possible to get all ethnic groups (i.e., asian/pacfic islander, native american, etc.) to populate as well as multiple job titles (i.e., DM, DOM, etc.) for this worksheet?

4) For the KEY FEMALE TALENT-DM DIR LEVEL & KEY FEMALE TALENT-SMs LEVEL worksheets, I can get the GENDER - Female only to populate but is it possible to populate multiple job titles (i.e., DM, DOM, etc.) for DM DIR Level worksheet and SM only job titles for SM Level worksheet?

Any assistance that you would provide is greatly appreciated.

I look forward to hearing back from you.

[/list]

JBeaucaire · #4 06-28-2009, 07:40 PM

1) You need to understand this piece of formula, specifically what the ROW() function does.

=ROW() will return the row # of the row this formula is in. So if you enter that anywhere in row1, the answer will be 1. Entered in row 15, the answer would be 15.

How this is useful is we are going to create a "list" and in the first cell we want the "first" item in the list to appear. That means we want item #1. And we want a formula that we can copy downwards and have it increment in the list by 1 item each time.

ROW() seems perfect then, doesn't it? And it is. Except, you have to "fix" the first cell with this formula in it if it's not row 1.

The formula I gave you was meant to be put in row 2, and it used ROW()-1 to convert the row(2) answer back into a 1. But you then added a new row called PERFORMANCE DISTRIBUTION, this pushing the first cell with the formula in it down to row 3. That means you'll have to change every instance of ROW()-1 to ROW()-2 to convert 3 into 1. Then copy those new formulas down the column.

That's why the formula works on RVP RACK & STACK using ROW()-1 since the formulas start on row2, but not on LEADERSHIP TEAM MATRIX where you moved it down to row3.

2) The key you're using for retention is 3 columns wide (=MAX(AI:AK)) and there is no basis in this layout to deal with that. The key needs to be one column.

Looking at your 3 columns, it appears ALL the data falls into those 3 categories. You're just creating a duplicate of all items in the main?

You'll need a new technique for this sheet if that's what you're after. What's going in this sheet?

3) & 4) ...show me.

JBeaucaire · #5 06-28-2009, 09:02 PM

I corrected all the sheets so that the column A index is pulling over the column A index from the MAIN sheet. Then all the rest of the formulas were changed to draw their information from THAT index value. The benefit here is that we now have the exact same formula on every sheet for pulling first name, last name, etc. Nice?

============
I think I figured out your Q #2...I created a single KEY column that indexes the priority, top priority and not priority items into similar ranges. Then we can pull those items over onto the RETENTION RISK sheet from smallest to largest.

Top priority items index starting at 1
Priority items index starting at 1001
Not priority index starting at 10,001

Make sense?

===========
If you approve of what we did on the RETENTION RISK page, you can do the same thing for your Q 3 & 4 above, I believe. Just create a key formula that indexes each job into a specific range.

DM starts at 1
SM starts at 1001
RVP starts at 2001
...etc.

Have a look.

lillianjbrown · #6 06-29-2009, 04:18 PM

JB,

All I can say is WOW! You are definitely know your stuff. I will take a look at everything and let you know.

Until then...chow!

JBeaucaire · #7 06-29-2009, 06:16 PM

Did I guess right on Q2? Is that what you were trying to do with the priorities?

lillianjbrown · #8 07-01-2009, 08:42 AM

Yes. I have not had an opportunity to test it on the job titles but will let you know what happens.

Thank again for all you have done for me!!!!

I will be in touch.

Have a great 4th of July!!!!

lillianjbrown · #9 07-10-2009, 04:19 PM

Hi,

Here we go. Since the last message, you resolved Q2 and the retention risk situation is good to go for now. But as for it working for Q3 & Q4, maybe I am just not following totally but is it possible to get the data from two different columns (job title & ethnicity) to work with the formula used for retention risk. The goal for Key Ethnic Talent worksheet is to populate specific job title for (DM/Dir Level) and (SM Level) with only ethnic groups (asian/pacific islander, hispanic or latino, etc.). Following the same concept for Key Female Talent to populate specific job titles and females only. I tried to use the formula =IF($L2="","",IF($L2="Female",COUNTIF($L$1:$L1,"Female")+10001,IF($D2="DM",COUNTIF($D$1:$D1,"DM")+10 01,COUNTIF($D$1:$D1,"ROM")+1))) you used for retention risk and change the info accordingly for Key Female Talent (DM/Dir Level) but it did not work properly. What did I do wrong?

Please help.

Thanks.

JBeaucaire · #10 07-10-2009, 05:37 PM

AP2: Key Ethnic Talent DM Dir Level Above Key
=IF(AND($K2<>"White",$U2="DM Dir Level Above"),N(AP1)+1,N(AP1))

AQ2: Key Ethnic Talent Store Managers Key
=IF(AND($K2<>"White",$U2="SM Level"),N(AQ1)+1,N(AQ1))

AR2: Key Female Talent DM Dir Level Above Key
=IF(AND($L2="Female",$U2="DM Dir Level Above"),N(AP1)+1,N(AP1))

AS2: Key Female Talent Store Managers Key
=IF(AND($L2="Female",$U2="SM Level"),N(AQ1)+1,N(AQ1))

lillianjbrown · #11 07-12-2009, 08:59 PM

JB,

First, let me say how grateful I am to you and for your willingness to always assistance. I know that I maybe wearing out my welcome with this thread but it is the most challenging project that I have been given since being in my role. I thought that it was going to be a simple task but has turned into a MONSTER. Everyone has their ideas of how the data should/needs to populate and when I think I have nailed it. I have to figure out how to deliver what is being requested. So thank you so much for your patience. You just DO NOT how much you are teaching me and how much I am learning. My husband thought he was an Excel expert but he told me that this was over his head. I have taken two Excel classes this year and neither of them touched on this.

I understand if you are tired of assisting but would appreciate your assistance again.

I have went placed the formulas that you sent to me for the Key Ethnic Talent and Key Female Talent but NOW need to pull multiple job titles other than DM since it is DM Dir Level. I tried placing a comma after "DM" then placing "RMM" etc. but received #value error.

Also, I tried to use the same formula you used for "Priority Key" for RVP Pipeline Key but does not seem to be functioning properly as it is pulling "SM" and "MERM". For the LTM Key, I would like for it to pull RVP, FINMGR, FINVP for starters but only pulled SM and RVP.

I am hoping that after this message, I will be able to see the light at the end of the tunnel.

Again, thanks for all your assistance.

I have attached the file.

JBeaucaire · #12 07-13-2009, 02:23 AM

AP2:
=IF(AND($K2 <> "WHITE", OR($D2 = "DM", $D2 = "RMM")), N(AP1) + 1, N(AP1))
...though the sample data only has RMM as white ethnic groups, so no additional matches were made.

AN2:
=IF($D2 = "", "",IF($D2 = "RDO", COUNTIF($D$1:$D1, "RDO") + 1, IF($D2 = "ROM", COUNTIF($D$1:$D1, "ROM") + 101, 0)))
...this now only pulls numbers for RDO and ROM.

AO2:
=IF($D2 = "", "",IF($D2 = "RVP", COUNTIF($D$1:$D1, "RVP"), IF($D2 = "FINMGR", COUNTIF($D$1:$D1, "FINMGR") + 101, IF($D2 = "FINVP", COUNTIF($D$1:$D1, "FINVP") + 201, 0))))

lillianjbrown · #13 07-27-2009, 02:26 PM

JB,

Just when I thought I was done, more ideas have come to life and I need your help. If you are out there PLEASE respond. I need to insert two rows about the rows of data that is being populated but when I do, I loose rows of data. Why is this happening? Also, I am trying to combine the graphs from two or more worksheets but the data does not automatically populate when I do that. Why?

The reason that I need to insert the two rows is to try to insert a text box as we need to try to design the Excel worksheet to look similar to a Power Point template. The reason for needing to combine the multiple worksheets on to one worksheet is so that the data for those worksheets can be depicted on one worksheet with each having a different heading.

Please help.

Thanks.

JBeaucaire · #14 07-27-2009, 02:46 PM

You need to understand the function ROW() function.

If you enter =ROW() into a cell, the answer you get is the row number of the cell. So in A2, the answer is 2. This is useful when we want to create a formula that increments 1 value as you copy the formula down a column.

We are creating "INDEX KEYS" on the Main sheet, and those indexes start at the number 1. So when we want to pull the first value into another sheet based on finding the number 1, we can use the ROW() function. But we have to do some math on the answer ROW() gives.

If we put =ROW() in A2, it answers 2. But the first answer we WANT is 1, so we have to subtract 1 from the answer. So we use =ROW()-1 in A2 to get the answer 1. Then, as we copy that formula down, it increments 1 value per row the way we want.

In Key Ethnic talent cell A2 is this formula:
=IF(ROW()-1>$M$1,"",INDEX(Main!A:A,MATCH(ROW()-1,Main!AP:AP,0)))

See how it's used to spot the 1 in the correct column? The rest works off the answer we get in column A, so this is the only spot we need to fix the formula.

Now, keep in mind what you've learned about ROW(). You now want to start a NEW chart and have the first value appear in A14. You copied the formula from A2 as is, which won't work, because =ROW()-1 in cell A14 is 13, not 1. You need to change the initial formula to:

=IF(ROW()-13>$M$13,"",INDEX(Main!A:A,MATCH(ROW()-13,Main!AQ:AQ,0)))

THEN copy that formula down the column for the new chart.

=============
Now, if you wish to insert blank rows above the data, you can. But once you have done so, you will need to go to the first REAL formula cell in column A and fix the ROW()-??? part. If you insert 2 blank rows and the first cell with real data is now A4, then it changes to ROW()-3. Understand?

lillianjbrown · #15 07-29-2009, 05:31 PM

Hi JB,

Words cannot express how grateful I am to you for your assistance. I don't know what you do for a living but you are not missing your calling when it comes to providing assistance with Excel. You have taught me so much over the past couple of months.

For the record, I have an understanding of the ROW() function.

Thank you so much!

Welcome to the Excel Forum

Forum Statistics