Fill cells automatically from 2 variables

N

Ned Harrison

Hello - I am trying to add a function to a spreadsheet that means when 2
variables are completed (currency & month - 4 options on each) it returns a
predetermined value. I thought I'd nearly got there (with a large amount of
help) by using a combination of If & AND functions, but it keeps giving an
error message. Can anyone help?

In case, I'm not being clear, I need a formula that will deal with this:
If M =1, C=1, then E = a
If M =1, C=2, then E = b
If M =1, C=3, then E = c
If M =1, C=4, then E = d
If M =2, C=1, then E = e
If M =2, C=2, then E = f
If M =2, C=3, then E = g
If M =2, C=4, then E = h
If M =3, C=1, then E = i
If M =3, C=2, then E = j
If M =3, C=3, then E = k
If M =3, C=4, then E = l
If M =4, C=1, then E = m
If M =4, C=2, then E = n
If M =4, C=3, then E = o
If M =4, C=4, then E = p

Where M and C are selected by the user from 4 options, and E is the result
given from values that I have provided. Any ideas?
Many thanks
N
 
S

Shane Devenshire

Hi,

Set up a table like this

1 2 3 4
1 a e i m
2 b f j n
3 c g k o
4 d h l p

then us a formula like this
=INDEX($B$2:$E$5,MATCH(A11,$A$2:$A$5,),MATCH(A12,$B$1:$E$1,))

In this case the user is picking the M value from A11 and C value from A12
and the above table is in A1:E5
 
P

Pete_UK

So, are you saying that in your example "a", "b", "c" etc represent
values that you want to return rather than those letters literally?

One way of doing it is to put those values in a column somewhere, eg
in X1:X16, in the order shown, then you could use this in the cell you
refer to as E:

=INDEX(X$1:X$16,(M-1)*4+C)

where M and C will have to be changed to the cell references that they
represent.

You might like to build in some error-checking:

=IF(OR(M="",C=""),"",INDEX(X$1:X$16,(M-1)*4+C))

Hope this helps.

Pete
 
N

Ned Harrison

That is absolutely brilliant - Thank you!

Shane Devenshire said:
Hi,

Set up a table like this

1 2 3 4
1 a e i m
2 b f j n
3 c g k o
4 d h l p

then us a formula like this
=INDEX($B$2:$E$5,MATCH(A11,$A$2:$A$5,),MATCH(A12,$B$1:$E$1,))

In this case the user is picking the M value from A11 and C value from A12
and the above table is in A1:E5

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire
 
J

JP Ronse

Hi Ned,

The usual way will require a lot of if's and goes perhaps beyond the limits
of Excel (I didn't try)...

I tried following and was working for me (if did understand your question
correctly...)

In column A, I've put M (1,1, 1, 1, 2, 2, 2, ...)
In column B, I've put C (1, 2, 3, 4, 1, 2, 3? 4 ...

In column C, (E result) following formula:

=IF(A1=1;CHAR(96+B1);IF(A1=2;CHAR(100+B1);IF(A1=3;CHAR(104+B1);CHAR(108+B1))))

The CHAR function gives the corresponding character of the ASCII value: 97
=> a, 98 => b, ....

Note that you may have to change ';' in ',' to get the formula working.

Please let me know if this was helpful.

Wkr,

JP
 
J

JP Ronse

Hi Teetless mama,

That's what I would have answered if I was able to set-up the formula. Was
busy with M1^2 and didn't see 4*M1.

Wkr,

JP
 
N

Ned Harrison

Thanks people for all the responses. I've used Shane's version which seems
to work fine for what I need - but I really appreciate all the help. All of
your solutions look a lot simpler than my starting point. Cheers!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top