How to use (i.e import) formulas and text data from a table to another worksheet per attached example (from Table 1 to WS1). Thank you in advance for any help.
Looking up and using formulas from another WS
Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
-
-
-
Re: Looking up and using formulas from another WS
I think this what you are looking for the shape
=CHOOSE(D11,'Table 1'!E10,'Table 1'!E11,'Table 1'!E12,'Table 1'!E13) -
Re: Looking up and using formulas from another WS
Here is the other one you need
=IF(D11<=2,$A11*$B11,IF(D11=3,PI()*$C12^2,IF(D11=4,1/3*PI()*$C13^2*$B13)))
Notice this doent actually import a formula but rather has all the formulas. Drag fill the formula down as needed. -
Re: Looking up and using formulas from another WS
Chris:
Can you come up with any other solutions in the event where there would be many cases. The IF function restricts me to only 7 cases.
-
Re: Looking up and using formulas from another WS
Well you are restricted to 7 so the best way beyond that would be to use vba.
How many are you going to need?
Will this be something you do once, or constanly changes? -
-
Re: Looking up and using formulas from another WS
So I am playing around with the choose formula
which allows up to 29 values. So here is what I came up with=CHOOSE(D11,IF(D11<=2,$A11*$B11),IF(D11<=3,$A11*$B11),IF(D11<=4,$A11*$B11),IF(D11<=5,$A11*$B11),IF(D11<=6,$A11*$B11),IF(D11<=7,$A11*$B11),IF(D11<=8,$A11*$B11),IF(D11<=9,$A11*$B11),IF(D11<=10,$A11*$B11),IF(D11<=11,$A11*$B11),IF(D11<=12,$A11*$B11))
This appears to work and using the formula palette is very manageable, So if someone Knows why this wont work (it sounds to easy to me) please let me know.
-
Re: Looking up and using formulas from another WS
Right now I have about 35 different formulas (for 35 different shapes). The list could possible grow to about 50. Once the formula is established it will not change. The formula (or procedure) or function (once it is established) will be used every time a new worksheet is created that needs to use this formula (if that is what you mean). I was hoping there would be some way to name the formula and then to reference it that way.
In effect, what I am really trying to do is create a formula that replaces itself with another formula (which maybe is impossible).
If using VBA code and case loops (or something like that) is the only solution, then I guess that is what I will have to do. I am not experienced with writing code, so I would appreciate your help. Thank you very much.
-
Re: Looking up and using formulas from another WS
I am not the best at VBA so hopefully someone else will step up and help.
I will take a look at on Monday morning, sorry I dont have the time over the weekend. If not worked out by Monday I will get right on it. Good Luck -
Re: Looking up and using formulas from another WS
Thank you for your help. I will keep checking the posts. Anyway, you turned me on to "choose" which I had not used before. I already have some instances where I can use it. Thank you very much.............Dennis
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!