Home

Getting Data from a Different Sheet

ponsolo
Occasional Visitor

Hey all,

 

I'm trying to set up a spreadsheet with data from the 2018 college football season, including a computer ranking system known as S&P+. I would like to display each team's S&P+ (from the "Teams" sheet) in their games (in the "Games" sheet). I just don't know of a way to automate this, and entering it manually seems insane. 

For example, the first game listed is Hawaii vs Colorado State. Cell B2 should be Hawaii's S&P+ rating (-9.6) and cell E2 should be Colorado State's S&P+ rating (-14.2).

 

Any tips related or not related to this question would be great! I'm fairly inexperienced with Excel. I am pretty familiar with Java and Python, so if that's necessary I can handle it.

 

Thanks for reading! Any help is much appreciated. I've attached my Excel file to this post, so feel free to take a look. If you need any more info on what exactly this spreadsheet should do, let me know!

1 Reply

you can use indirect formula to get any value from any sheet you have. For example:

 

=INDIRECT("'TEAMS'!$A$2")

 

will get data from A2 cell of TEAMS sheet. You can also use address, row and column to get address of a cell in tect format and use it in INDIRECT to get value for example:.

 

=INDIRECT("'TEAMS'!" &ADRESS(1,2))

 

This formula will return data from TEAMS sheet A2 cell (row 2, column 1).

 

You can also combine this with formulas that include row(), column(), rows(), columns() to get whatever you need.