Announcement

Collapse
No announcement yet.

OT Excel Function Required

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • OT Excel Function Required

    Hi

    I am looking for a way to use two woorksheets in excel as data input points, then have a calculation on another sheet based on which sheet is active so want to say-

    if active sheet is a then (look at a cell in sheet a for value) else look in sheet b etc.

    cant find a way to return the active sheet, to use in if statement anyone ever found a way?

    Steve Larner

  • #2
    I'm not sure what you mean by "active sheet." Do you mean the sheet you have selected? If you put a formula on Sheet1 and tell it that the data is on Sheet3 and a formula on Sheet2 with data on Sheet4, when you select Sheet1 it will use the Sheet3 data and when you select Sheet2 it will use the Sheet4 data. I don't get what you are trying to do. Can you give an example?

    Tom
    Tom - Spotsylvania, VA

    Comment


    • #3
      Just a comment

      Never thought about it, since up until now a single sheet was large enough...they are huge, way, way larger than most think but I could see it being too small if one were plotting lots of points for machining.

      Comment


      • #4
        Originally posted by flathead4
        I'm not sure what you mean by "active sheet." Do you mean the sheet you have selected? If you put a formula on Sheet1 and tell it that the data is on Sheet3 and a formula on Sheet2 with data on Sheet4, when you select Sheet1 it will use the Sheet3 data and when you select Sheet2 it will use the Sheet4 data. I don't get what you are trying to do. Can you give an example?

        Tom
        I have data input on sheet 1 that is used on sht 3 to do some calcs and then displays answers on sheet 1
        I have data input on sheet 2 that is used on sht 3 to do some calcs and then displays answers on sheet 2
        Sheet 3 has a lot of factors that feed into some calcs on sht 3 that also use 3 inputs from sheet 1 or 2
        Sheet 3 will be hidden
        If sheet 1 is active sheet 3 (the hidden one) will do calcls based on sheet 1 input
        If sheet 2 is active sheet 3 (the hidden one) will do calcls based on sheet 1 input
        so the plan was on sheet 3 to say if sheet 1 active(selected) use cell sheet1 a1 or if sheet 2 active use sheet2 a1

        Hope thats clearer

        Comment


        • #5
          Originally posted by RussZHC
          Never thought about it, since up until now a single sheet was large enough...they are huge, way, way larger than most think but I could see it being too small if one were plotting lots of points for machining.
          This is being used to consruct a calulator for sales so sheet 1 & 2 are for data input and answers, sheet 3 is hidden and does all the work

          Steve Larner

          Comment


          • #6
            I've never done it but it looks like a macro could help. From a brief search of active sheet in excel help, I got this link:

            http://peltiertech.com/WordPress/mak...dent-of-sheet/

            I have done something similar (calculations hidden on a page) but if I were doing this myself, I'd just have two hidden pages, each which pull the data from a respective sheet. I might even expand it to five sheets:

            sheet 1 - all generic data
            sheet 2 - specific output from hidden sheet 4
            sheet 3 - specific output from hidden sheet 5
            sheet 4 - calculates sheet 2
            sheet 5 - calculates sheet 3

            OR, I've also done it with vlookups in nested logical statements,but that becomes tedious.

            Comment


            • #7
              It sounds like what your describing would lend itself to a dialog function. Where you would create a dialog to accept your inputs and display your answers. the dialog programming and formulas would be on a hidden page and would handle all the inputs and outputs and display them within the dialog as well as post them to their proper cells.
              It's been a few years since I've used them, (or even a spread sheet for that matter) and I use Open Office rather than Excel, so my terms may not quite match Excel's. but I'm sure Excel has something similar.
              I cut it twice, and it's still too short!
              Scott

              Comment


              • #8
                I'm sure you know...link

                http://www.mrexcel.com/

                watched a lot of this guy in years past of "Tech TV", very good explanations and very busy...I think there is a section of the website with previously asked questions and parts from old TV shows as well [or at least answers discussed with reference to given shows/topics]

                it may help, sort of assuming you have heard of him but maybe not?

                Comment

                Working...
                X