INDEX ROW Not Pulling Correct Data

[autoworke]

Hi Everyone,


This is my first post and I hope someone can point me in the right direction. My workbook has 2 worksheets in play:

The first is a "forecast template" where the user selects his/her name from a drop down in (A3) that in turn populates the template with their data from the second sheet. The second sheet is obviously my "database" containing the user's data.

I am using the following formula with no issue to populate columns that correspond with the rows where "MSG" is in column A.

=IFERROR(INDEX('MSG Data'!$A$5:$T$2000,SMALL(IF(('Forecast Template'!$A$3='MSG Data'!$A$5:$A$2000)*('Forecast Template'!$A7='MSG Data'!$E$5:$E$2000),ROW('MSG Data'!$A$5:$A$2000)-MIN(ROW('MSG Data'!$A$5:$A$2000))+1,""),ROW('MSG Data'!F1)),COLUMN('MSG Data'!F1)),"")

The first issue that I am having is populating the columns for the rows that have "Pipeline" in column A with the correct data. I think the problem is that my INDEX is not specific enough. I think I need to add another IF condition which I've tried with no success.

Hard to explain but if you look at my workbook it should be obvious what I am trying to accomplish here. There are several columns that I am returning values for but for this exercise we'll just focus on column F which is Ad Status % I have inlcluded comments in column V to indicate the incorrect values.

The second issue is that I want to populate the yellow section at the bottom with those "Pipeline" accounts that do not already appear in the top portion of the template. In this case, the user just has 2 accounts which I have manually inputted for illustration purposes.

Any insight on how i can fix my issues would be greatly appreciated.

[icestationzbra]

hi there,

have a look at the attached. i have simplified the formula a bit and kept the ranges small enough to be able to troubleshoot. you can extend the ranges once you have verified everything - the formula will work just fine.

1195

[autoworke]

Hello icestationzbra, I'm still going through to fully understand the formula but it appears to be working wonderfully. Thank you very much.

[Tank997]

The formulas will be a little easier to understand if you name your ranges.

Please Login or Register  to view this content.