# Thread: OT Excel Function Required

1. SDL
Senior Member
Join Date
Apr 2006
Location
Banbury England
Posts
393

## 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. Senior Member
Join Date
Apr 2010
Location
Spotsylvania, VA
Posts
437
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. Senior Member
Join Date
Mar 2010
Location
Winnipeg Manitoba
Posts
2,411

## 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. SDL
Senior Member
Join Date
Apr 2006
Location
Banbury England
Posts
393
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. SDL
Senior Member
Join Date
Apr 2006
Location
Banbury England
Posts
393
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. Member
Join Date
Sep 2011
Location
Raleigh, NC
Posts
77
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. Senior Member
Join Date
Dec 2005
Location
Sequim, Wa.
Posts
499
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.

8. Senior Member
Join Date
Mar 2010
Location
Winnipeg Manitoba
Posts
2,411

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
•