I created a sheet where I can rank the expected performance of new titles coming out along with the performance of older titles (For the case of this example, I'll be using music albums).
I have 2 sheets. A Forecast sheet where all the data is displayed and organized and another sheet with a big table where I input the data and later pulled either through Vlookup or Index and Match
Attached is an image of the basic version of the sheet I'm working on
http://i.imgur.com/bjx744r.png
In the Top 10 section, I have a table set up to order the titles in whatever form necessary.
Columns O through R use an INDEX and MATCH to pull data based on whatever Column M contains. If it finds a match it spits out whatever data is in the database sheet. Pretty basic stuff.
What I want it to do is have Columns Q and R search the 3 sections on the left in addition to the separate Database sheet. If there is a match in either A2, A12, or A22, then I want it to pull the Number 1 and Number 2 data located in cells H10 and J10 (for A2) / H20 and J20 (for A12) / H30 and J30 (for A22), otherwise it just pulls from the database if it were a regular title.
The main reason I want to pull data from the section from the left is because Number 1 and Number 2 are just forecasted numbers. I don't want them in my database at all until I get the real numbers the following week, but I still want to be able to rank our forecasted numbers for the new titles coming along with the older title's hard numbers.
I could just have my crew input these numbers manually into the Top 10 titles, but they always tend to break my cells, formulas and they just plain don't know how to use excel. The only boxes I want them touching are the H10 + J10 / H20 + J20 / H30 + J30 ones and the header sections.
I tried using a combination of AND and OR but I can only manage to get it working for just one of the 3 sections, not all. I'm wondering if this is at all possible and if it is, if there's a method where it's not a very convoluted series of IF, AND, OR
At the moment, I just have a very basic INDEX and MATCH formula in Q and R
=IFERROR(INDEX(Database!$D$2:$D$2105,MATCH($M7,Database!$A$2:$A$2105,0)),"")
Any help would be greatly appreciated
I have 2 sheets. A Forecast sheet where all the data is displayed and organized and another sheet with a big table where I input the data and later pulled either through Vlookup or Index and Match
Attached is an image of the basic version of the sheet I'm working on
http://i.imgur.com/bjx744r.png
In the Top 10 section, I have a table set up to order the titles in whatever form necessary.
Columns O through R use an INDEX and MATCH to pull data based on whatever Column M contains. If it finds a match it spits out whatever data is in the database sheet. Pretty basic stuff.
What I want it to do is have Columns Q and R search the 3 sections on the left in addition to the separate Database sheet. If there is a match in either A2, A12, or A22, then I want it to pull the Number 1 and Number 2 data located in cells H10 and J10 (for A2) / H20 and J20 (for A12) / H30 and J30 (for A22), otherwise it just pulls from the database if it were a regular title.
The main reason I want to pull data from the section from the left is because Number 1 and Number 2 are just forecasted numbers. I don't want them in my database at all until I get the real numbers the following week, but I still want to be able to rank our forecasted numbers for the new titles coming along with the older title's hard numbers.
I could just have my crew input these numbers manually into the Top 10 titles, but they always tend to break my cells, formulas and they just plain don't know how to use excel. The only boxes I want them touching are the H10 + J10 / H20 + J20 / H30 + J30 ones and the header sections.
I tried using a combination of AND and OR but I can only manage to get it working for just one of the 3 sections, not all. I'm wondering if this is at all possible and if it is, if there's a method where it's not a very convoluted series of IF, AND, OR
At the moment, I just have a very basic INDEX and MATCH formula in Q and R
=IFERROR(INDEX(Database!$D$2:$D$2105,MATCH($M7,Database!$A$2:$A$2105,0)),"")
Any help would be greatly appreciated
