Excel challenge - Sheet referencing?

  DongQuyCao 21:25 21 Sep 04

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?

  VoG II 22:06 21 Sep 04

This is not possible using sheet formulas.

It is using VBA - please explain exactly what you want to do and if you need these to be formulas that change if you update the workbook, or is it a "one off".

  cherria 12:20 22 Sep 04

You can do this provided your sheet names have regular numbers such as sheet1, sheet2, sheet3 etc.

in A1 on sheet1 type =INDIRECT("sheet"&ROW(A1)&"!B1")

then copy this down.

ROW return the row number of the reference so for row 2 you get a 2 so the formula inside the indirect becomes sheet2!B1.

the INDIRECT formula then return the value from this, similarly for row 3-10 etc.

This is very sensitive to changes in sheet names or row numbers but does what you want.

  VoG II 12:47 22 Sep 04

Very neat.

I've made a mental note not to say that things are impossible when I really mean that I cannot immediately think of the way :o(

  cherria 13:07 22 Sep 04

Thanks. I'm sure it was you who once said that there's not a lot that's impossible in Excel when you introduced us to cellvaders.

I noticed there is now also a pacelman as well


  Graham ® 13:24 22 Sep 04
  DongQuyCao 20:54 22 Sep 04

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:

Cell A2 = Sue!B1
Cell A3 = Paul!B1
Cell A4 = Christine!B1
Cell A5 = John!B1
Cell A6 = Laura!B1

I hope I've explained it clearly - maybe I'm using too complicated a method to track something that is quite simple, but I've always wanted to know if this sort of thing could be done in Excel!

  DongQuyCao 21:08 22 Sep 04

Graham ® I'm not sure what all this pacelman, cellvaders is, but I'll take some time to study it! Thanks.

  Graham ® 21:40 22 Sep 04

You and me both!

  mammak 22:02 22 Sep 04

Oh my heavens this is what i take in my next
module on ECDL,
I am going home with my head hung in shame,
Havent a clue????

  VoG II 22:43 22 Sep 04

In your Excel Workbook press ALT+F11 to open the Visual Basic Editor. Insert/Module. Paste in the following up to and including End Sub

Sub test()

Sheets("sheet2").Range("A1").Formula = "=sue!B1"

End Sub

Change "sheet2" to whatever your master sheet is called.

Go back to your worksheet, Tools/Macro/Macros, click on test then the Run button.

You can modify this macro to include each sheet.

It does not do anything more than you could do manually. If you want some sort of automation then you need to be using regular numbers, as cherria said.

This thread is now locked and can not be replied to.

Hands-on: Acer Predator Triton 700 review

1995-2015: How technology has changed the world in 20 years

D&AD Awards 2017: see the best design, advertising, illustration, animation and VR of the past year

Apple predictions 2017