Using \"IF\" statement to test worksheet order relationships??? I\'d like to use

Question

Using "IF" statement to test worksheet order relationships???

I'd like to use an IF statement in a cell to determine whether a certain worksheet name is before or after another worksheet name (within the same workbook), with calculation results dependent upon that relationship. E.g., my workbook has 20 named worksheets within it, and if the worksheet which is named "End" is before (to the left of) a worksheet named "April 2014" one set of calculations will result, but if the worksheet named "End" is after (to the right of when looking at the tabs at the bottsom of the workbook) the workssheet named "April 2014" another calculation will take place.

Is there any way to accomplish this???

Explanation / Answer

You do not need VBA to do this, but you do need to get a little "sneaky". First define 3 workbook level names. Insert-Name-Define. For the first name, call it "Docname". In the refers to field, type:

=get.document(1)

For the second name, call it BookName, and in the refers to field, type:

=left(docname,search("]",docname))

For the third name, call it Sheets, and in the refers to field, thyp"

=get.workbook(1)

Now, in any worksheet, the formula: =counta(sheets) will give you the number of worksheets in the workbook. The formula: =match(bookname&"end",sheets,0) will give you thae sheet number of the sheet whose name is END. Using these 2 formulaas you can eaesily determine whether the current sheet is the last sheet, or before the last sheet.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.