OT Excel Function Required

[SDL]

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

[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

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

[SDL]

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

[SDL]

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

[jrude]

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.

[Scottike]

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.

[RussZHC]

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?