** Q&A: Importing Other Projections **

[Mike MacGregor 20]

flappos, I think I answered something similar to what you are asking earlier today by email. You mean, how do you indicate the references when they are in a separate Excel file and worksheet? Let me post the question and answer from that email.

 

Question:

 

Mike, I have a question regarding importing data. I am sure you have heard this before, but I have not been able to follow your advise on the board. I am not real familiar with excel. Anyhow, I think I understand vlookup, but my confusion comes with the next part of the equation. I have downloaded some projections and inserted them into an excel spreadsheet called Book1. The data in Book1 is on sheet1. What is the formula that I use to get the compiler to get the data from Book1/Sheet1 and place it into the right cells. My other question is about the next part of the formula. In the example you gave in the forum you use 2,3,4... as the column number from which you get the cell info you want. Can I use letters such as C,DF? I will continue to work on this, but if you could give me a couple of hints I would appreciate it.

Response:

 

When the data is in another file and/or worksheet (which it almost always is), you do need additional references. However, these are easily filled in by doing the following:

 

To add a VLOOKUP formula, click on the cell where you want it and either hit the little formula button on the toolbar or in the formula bar (fx symbol) and choose from the available Excel formulas VLOOKUP. Hit OK. Now, it shows a box to layout the different parts of the formula. Instead of typing in the cells in each of these boxes where it is asking for a reference, use your mouse to highlight the reference or range instead. If that involves going to another file, choose the different window, find the correct tab, and highlight the range. It should fill this all in back in your VLOOKUP formula.

 

In the end, the formula will look something like this:

 

=VLOOKUP($C5,’[ffchamps_compiler_with ranking formulas.xls]DEF'!$C$5:$AN$36,AF$4,0)

 

See the file name and sheet? You can type it in, but getting used to highlighting with the mouse is much faster and tends to decrease the chance of error.

 

On the column number, you can’t use the letters (stupid, I know) but still the number of the column across in your highlighted range. So, if you are highlighting a range from the player name (column C) all the way over to column CF, column CF would be column number 82. Quick way to count – click a cell in the first column, hold SHIFT and use the arrow keys to the right highlighting a section. Soon, it will show in a small box the number of rows and columns you are highlighting. Once you hit the column you are wondering about, the number in this box is the correct count.

[BigSeXXXy 0]

Mike I am having all kinds of problems with the vlookup, maybe if you can tell me what $A$2:$D$3 is doing I can figure out why its not working for me?

[BigSeXXXy 0]

OK Mike disregard last post, figured out I had to highlight the whole section not just one line for the second half of the formula

[madscene 0]

I don't think this has been answered... what if we want to copy in our own FFP predictions but NOT the associated stats, and then have the players ranked by that alone. I am thinking about using the AVT, so I would generate FP for 2000-2002 seasons, and then do an average calculation to get the avg FFP for each position each year. I'd then rank the positions the way I wanted, and paste in the avg FFP... know what I'm saying? I'd then want the compiler to use only my FFP projections, not based on any actual stats. Hopefully this makes sense.

[Mike MacGregor 20]

Hi madscene. If I already had FF Pts I wanted to input, I would just add them to a Site B stat category (any one) and then change the scoring for that category to a 1 point per 1 unit scoring. Allocate to Site B, and there you go. The Compiler calculated FF Pts will equal the FF Pts you added. Make sense?

[madscene 0]

Hey. Yeah, that makes sense. Thanks! I am totally behind on my preparation... draft is tomorrow! This will be a big help. Thanks again!

[madscene 0]

Actually... that didn't seem to work, fully. It got the FFP right for each player, but then ranked them wrong. i.e. I had Stephen Davis ranked 14th among RBs, in terms of the FFP I entered, but he came out as the 18th RB in the cheatsheet, even though it says right next to him that he has more FFP than the players above him. I should mention that the way I did this was to generate average FFP for each position from highest to lowest, then I actually shifted rows up and down in the compiler (i.e. if I wanted to move S. Davis up above another RB, I cut and pasted his row). Then once I had my order set, I pasted the Average FFP I calculated into the RuTD column of Site B, and set the scoring to 1pt per RuTD, and 0 for everything else... Why whouldn't that work?

[madscene 0]

I figured it out. If I just pulled the player names onto the sheet where I did my averaging, and then put the players in the order I wanted, and imported them back into the spreadsheet using the vlookup, it worked!