Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a workbook named "stu", it has 1 to 30 sheets. In cell H4 I have linked a formula:

=[sub.xlsx]Sheet1!$B$2

in sheet1: cell H4 the formula in cell H4 should be =[sub.xlsx]Sheet1!$B$2
in sheet2: cell H4 the formula in cell H4 should be =[sub.xlsx]Sheet1!$B$3
in sheet3: cell H4 the formula in cell H4 should be =[sub.xlsx]Sheet1!$B$4
in sheet4: cell H4 the formula in cell H4 should be =[sub.xlsx]Sheet1!$B$5
in sheet5: cell H4 the formula in cell H4 should be =[sub.xlsx]Sheet1!$B$6
..and so on..

..................................

So in every sheet, cell no should change automatically. I will not have to press = sign and enter again and again.

share|improve this question
    
Can you please edit and clarify your question? As it stands it does not make sense because you have only made statements, not asked a question. –  CharlieRB Mar 19 '13 at 17:08
add comment

3 Answers

In Excel, type Alt+F11 to open the Visual Basic window.  On the left, right-click on the name of your spreadsheet and InsertModule.  A Module1 - Code sub-window will open on the right.  Insert the following in that window:

Function SHEET_NUM()
    SHEET_NUM = Application.Caller.Parent.Index
End Function

Now go back to the spreadsheet.  Just for fun, to see how this works, type =SHEET_NUM() into any cell.  It should display the number of the sheet that it’s on.  So, enter

=INDIRECT("[sub.xlsx]Sheet1!$B$" & (SHEET_NUM()+1))

into cell H4 on every sheet.  The SHEET_NUM()+1 subexpression will evaluate to 2 on Sheet1, 3 on Sheet2, etc… –– in other words, the row number that you want in your reference.  & is the concatenate operator; it will combine the constant string "[sub.xlsx]Sheet1!$B$" with the computed row number, resulting in the reference that you want.  The INDIRECT function takes a string that looks like a reference and de-references it; i.e., it fetches the value that is referenced.

A few warnings:

  • If you’re dealing with one workbook (stu.xlsx) referencing another (sub.xlsx), you will need to have the second book open whenever you work with the first one –– although Excel can reference data in a closed book, the INDIRECT function cannot.  If this is a problem, you can do a Copy and Paste Values maneuver.
  • You will need to save your workbook as an .xlsm file and enable macros every time you open it.  (Or do the aforementioned Copy and Paste Values maneuver.)
  • AFAICT, worksheets are assigned a number when they are created, and it’s hard to change.  In particular, if you move your sheets around, they will retain their original numbers, which will be confusing.  (But on the other hand, you can rename them without worrying about changing their numbers.)
share|improve this answer
    
I doubt I would go to so much bother :-) but this works and answers the question, as I understand it, so +1. Also I can see the principle being useful elsewhere. Shame about the sheet numbering (minor) issue but it seems inescapable. =CELL(“filename”) may look promising at first glance for a source of the displayed sheet numbers but that is complicated by being either the last one or two digits and in any case suffers critically from being volatile. –  pnuts Mar 19 '13 at 22:09
add comment

The following steps should get precisely the formula you're looking for, without VBA:

  1. Select all the sheets that you want the formula to appear (you can add each sheet to the selection by holding "Ctrl" and clicking on its tab)
  2. Enter the following formula into another blank cell, perhaps H3: =IF(LEFT(RIGHT(CELL("filename",$A$1),2),1)="t",RIGHT(CELL("filename",$A$1),1),RIGHT(CELL("filename",$A$1),2))
  3. Enter the following formula into cell H4 (substituting H3 for the cell you used in step 2): ="=[sub.xlsx]Sheet1!$B$"&H3+1
  4. Select cell H4, copy, and paste values
  5. Clear the formula created in step 2
  6. Find and replace "=" with "=" throughout the workbook (Ctrl+H for find and replace)
  7. Cancel the selection of the other sheets (you can remove each sheet from the selection by holding "Ctrl" and clicking its tab)

Don't do anything else between steps 1 and 7 unless you know how to work with multiple sheets selected.

share|improve this answer
    
Also a bit convoluted for my taste but works and avoiding VBA is always my preference, so +1. I can see the principle (“two step” process to deal with volatility of =CELL) being useful elsewhere. –  pnuts Mar 20 '13 at 10:41
add comment

Your best bet may be to open sub.xlsx, group all 30 sheets in stu, insert one version of the formula (eg =[sub.xlsx]Sheet1!$B$2 in H4 of sheet1 of stu), ungroup and then manually adjust H4 in each sheet of stu (except the one in which the formula was entered, eg in sheet2 replace 2 by 3 etc).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.