Results 1 to 8 of 8

Thread: OT Excel Function Required

  1. #1
    Join Date
    Apr 2006
    Location
    Banbury England
    Posts
    385

    Default 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. #2
    Join Date
    Apr 2010
    Location
    Spotsylvania, VA
    Posts
    403

    Default

    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

  3. #3
    Join Date
    Mar 2010
    Location
    Winnipeg Manitoba
    Posts
    1,973

    Default 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.

  4. #4
    Join Date
    Apr 2006
    Location
    Banbury England
    Posts
    385

    Default

    Quote 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

  5. #5
    Join Date
    Apr 2006
    Location
    Banbury England
    Posts
    385

    Default

    Quote 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

  6. #6
    Join Date
    Sep 2011
    Location
    Raleigh, NC
    Posts
    75

    Default

    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.

  7. #7
    Join Date
    Dec 2005
    Location
    Sequim, Wa.
    Posts
    494

    Default

    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

  8. #8
    Join Date
    Mar 2010
    Location
    Winnipeg Manitoba
    Posts
    1,973

    Default 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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •