title: Excel - Using formula to reference values on other sheets
body: <p>Due to software (Mac &amp; Windows compatibility is making this a harder task than it needs to be) &amp; security limitations, I can't use Macros like I normally would, so I have to rely solely on formulae in Excel. I have to consolidate text (no numbers) information from multiple sheets onto one summary sheet in a workbook that will have sheets added to it as time goes on. I have the names of all the sheets listed on a new sheet, and I'd like to use these cells as the source of the sheet name. E.g. I want to find the value of cell A1 on another sheet, where that other sheet is defined as the value of a cell that is a formula itself.</p>
  
  <p>For example, Sheet 1 has 3 rows saying "Blue" "Chevrolet" "Camaro", Sheet 2 has "Red" "Ford" "Mustang", Sheet 3 has "Yellow" "Nissan" "370Z", so the summary sheet will have one column listing all the colours, another listing all the manufacturers, and the final listing all the models. They way I've approached this is by having all the sheet names listed on a new sheet (A1 = "Sheet 1", A2 = "Sheet 2", A3 = "Sheet 3") and I'd then have another sheet that has A1 = the Colour of the first car, A2 = the Colour of the second car, and so forth. This sheet would get the value of the first car's colour by looking at the first cell on the sheet that lists all of the sheet names, and take the value from A1. I've searched quite a bit but I can't find a positive answer (it seems like what I'm looking for may not be feasible with just formulae); the closest I came was to some posts here saying to use the indirect formula, but I haven't had any success with that. </p>
  
  <p>My apologies for the rather convoluted explanation; I'd post a workbook with sample data but I'm unsure how to.</p>
  
  <p>Thank you very much for reading this; any help is greatly appreciated.</p>