I'm trying to get an Excel formula to "sheet reference", if I can put it in those terms. For example, if I put in cell A1 "=B1" and copy down to cell A10 I will get the values corresponding to cells B2, B3, B4 etc in the A column of Sheet1. But what if I wanted cell A2 to equal cell B1 in Sheet2, cell A3 to equal cell B1 in Sheet3, cell A4 to equal cell B1 in Sheet4 etc. Is it possible to do this from any formula/function or is it a matter for some VB?
Thanks VoG™ and cherria. I've tried cherria's method and it works to some extent, but if I change the sheet names to text (which is what I need to do)it throws out the formula. Unless I'm not doing something right...
I have in Sheet1, column A, a list of names and column B the total of amounts paid to each of those people. Each person has an individual sheet with which the amounts paid to them are listed in the column B of that sheet: B2:B10 where Cell B1 calculates a sum of those amounts.
So, in Sheet1, (the summary sheet) cell A2 I need the value of cell B1 of the next sheet which I've called Sue. In Sheet1, cell A3 I need the value of cell B1 of the third sheet called Paul. The next sheets are called Christine, John, Laura etc. This can be easily be done by manually adjusting each cell formula but that defeats the purpose of Excel!
Therefore, what I need the formulas to calculate in Sheet1 are: