Drop down field

M

Michelle

I have a drop down field in each entry of my budget which
has many option. I also have a total amount for each
option. I want the expenses of each option to be deducted
automatically from the totals when the option has been
chosen. Here is my formulas, but I am having a problem
with it. Can someone help me with this matter?

=IF(Sheet1!E1=1,'2106 - Travel - Locally!'D306-M9,0,IF
(Sheet 1!E1=2,'2106 - Travel - Locally!'D308-M9,0))

In the column next to the option on sheet 1, I have
entered number 1 to 8 (As I have 8 options) and when I set
up my drop down field I enter the (2) column in my INPUT
RANGE, then I use the Cell Link which is E1 as a reference.

What I am doing wrong?

Thank you for all your help.

Michelle
 
A

AlfD

Hi!

I'm not 100% sure what you are doing but I suspect you need somethin
like this:

=IF(Sheet1!E1=1,'2106 - Travel - Locally'!D306- '2106 - Travel
Locally'!M9,IF
(Sheet 1!E1=2,'2106 - Travel - Locally'!D308- '2106 - Travel
Locally'!M9,0)).

That's an awful long worksheet name. Ever thought of using short ones
even if when you've finished designing you change them back to lon
ones? Less scope for errors: less need for ' if you don't have spaces
shorter formulae..

Al
 
M

Michelle

I have tried it and I have a message telling me that I
have too many arguments. (I have done exactly what you
told me and change the name of my worksheet to "Sheet 2",
Thanks a bunch for that tip).

I forgot to tell you that I have 100 drop down boxes in my
workbook all with to the same 8 options.

My budget has places for 100 entries, each time that an
entry is done, there is a drop down box to choose from
different option.(Such as Option 1. Travel, Option 2.
Hospitality, etc). Depending which option has been
selected, I want the amount of that expense to be deducted
from the Total amount allowed at the beginning of the
year. Now because I have 100 entries (with 100 drop
down boxes) that can be deducted, I am a bit confused on
how I should put my formula. The one that I am trying
does not work, except when I use only one argument, then
it does exactly what I want. But one argument is only for
one option in the drop down box when in fact I have 8
options in each drop down boxes.

I hope that you can understand what I am trying to do,
because I need desperately your help.

Thank you

Michelle
 
E

EddyT

I can't see anything wrong with AlfD's formula... here's a reduce
version with S1 and S2 instead of those long names...

=IF(S1!E1=1,S2!D306- S2!M9,IF(S1!E1=2,S2!D308- S2!M9,0))

I've just tested it, and it seems to work fine. If you take the spac
out of your worksheet name, you wont have to use any ' characters, s
maybe that will solve your problem
 
A

AlfD

Michelle:

Did you notice I had reversed the ' and the ! at the end ot the shee
name?
And Sheet1 is easier to work with than Sheet 1.
Al
 
M

Michelle

Because I cannot use the input range twice for the drop
down box (because it will duplicate automatically) I had
to copy my option 100 times (Sorry but that is the only
way that I know) therefore I have 100 different cell links
such as the E1 then J1 and E10 then J10 etc. In my
formula which by the way works wonderfully, I don't seem
to be able to enter a range such as IF(S1!E1:AS98=1,'S2'!
D306 - 'S2'!M9:M303) I have a #value! as answer.

Should I use a lookup formula also and if so how should I
go about it.

Thank you for all your help, you have no idea how it is
appreciated.

Michelle
 
A

AlfD

Hi!

First let's try to get past the "enter a formula"blockage.

Don't type in Brian's formula: copy it from the post : go to you
worksheet:click the cell you want to put it in: then go to the Formul
Bar and paste it there: <Enter>

Second: the snippet in your latest post won't work for n reasons. I'
sure it wasn't expected to?

Come back when you've tried this.

If you want a view on lookups (which was one of the thoughts in my min
when I first read your post) I would have to ask for more info on you
sheets, structure, relationships and formulae. Not a lot: but heading
and a cople of lines of specimen data (in text: nobody around her
likes attachments).

Al
 
M

Michelle

O.K. I have done that and copy the formula from the post
and it works wonderfully.

But I have more than one drop down box.

Here how it goes.

In the drop down box field there is 8 options which are:
DGLRes Office Functions 1 8(E1)
Other Domestic Travel 2
Ares Advisory Group 3
Honourary Cols Conference 4
ABCA Conference 5
Unforcast Trips 6
Travel as nec w/DGLRes 7
Domestic Travel (w/DGLRes/Al.) 8

Like you can see I have show you how I have set up the
input range. and the cell link (E1).
Now I have copy this set up 100 times because each new
drop down box needs a new set up (Hey! let me know if
there is a better way, because this take so much time.I
thought I could just copy the first one, but when I change
the original, all the copy ones change also.)

Now each of this event/option have a total amount which
lets say is $20,000.00 (which is on Sheet2)

Lets says entry # 1 cost $1,000.00 for the option DGLRes
which is E1=1
Entry # 2 cost $500.00 for the option Unforcast Trip which
is E10=6
Entry # 3 cost $2,000.00 for the option Honourary Cols
Conference which is E19=4 and so forth.

Because the entire set up is from A1 to AS98 and that the
Cell link that are by themselves in column E-J-O-T-Y-AD-AI-
AN and AS, I don't know how to make it work and having the
amount of each entries deducted from the total amount.

I am very sorry, if you have difficulties understanding
this but be assured that I am trying my best and also I am
French which could be a bit confusing for you for the way
I express myself.

Thank you for everything.

Michelle
 
A

AlfD

Hi!

N'ayez pas peur! Actually, your English is a great deal better than m
1950's French.
Now I have copy this set up 100 times because each new drop down bo
needs a new set up
Click to expand...

We've got to find a way of reducing this.


Maybe send me a (suitably anonymous) workbook and I'll have a look an
see what I can do?
alfreddearnley<at>btinternet<d o t> com.

Al
 
Top