How to quote cells when "IF" word is involved

  • Thread starter Thread starter Ming
  • Start date Start date
M

Ming

On Sheet1, I have a data set (C1:C100) based on the input of cell A1.
When A1 has various method choices,say,"method 1","method 2","method
3", each cell in the data set will have a new value accordingly.
(=IF(A1="method 1",formula1,IF(A1="method 2",formula2,formula3))

I want to quote those three data sets on Sheet2. A lousy way is I could
just duplicate Sheet1, set A1 to different value and quote data sets
from different sheets.

Is it possible to quote three data sets simultaneously from Sheet1?

Thanks,
Ming
 
Perhaps use a 2 variable data table ?

In Sheet1
---
Assume you have a DV in A1 allowing the selections of either:
method 1, method 2, method 3
(A1 will be variable 1)

And you have in C1:
=IF(A1="method 1",B1*2,IF(A1="method 2",B1*3,B1*5))

B1 may contain say, any number between 1 - 100
(B1 will be variable 2)

Using 4 empty columns to the right, say cols E to H

Put in E1: =C1
Put in F1:H1 : method 1, method 2, method 3 (for variable 1)
Fill / list down in E2:E101, the values 1 - 100 (for variable 2)

Select E1:H101

Click Data > Table

In the Table dialog, put:
For row input cell: A1
For column input cell: B1
Click OK

The resulting data table will return all the possible values of C1
for the possible values in A1: methods 1 to 3,
and for possible values in B1 between 1 - 100

Now, just select E1:H101 and do a copy > paste special > paste link to say,
A1:D100 in a new Sheet2. Note that the data table needs to be set-up in the
same Sheet1 as the row/col input cells. But we can always mirror it
elsewhere, as above.

The same formula: {=TABLE(A1,B1)} will appear in every cell within F2:H101.
Albeit it looks like an array formula, it cannot just be entered as such.
The construct must be done / invoked via the Data > Table steps outlined.
 
And you have in C1:
=IF(A1="method 1",B1*2,IF(A1="method 2",B1*3,B1*5))
Click to expand...

Perhaps a better, "tighter" example formula to use in C1 would be:
=IF(A1="method 1",B1*2,IF(A1="method 2",B1*3,IF(A1="method 3",B1*5,"")))

(to ensure that calcs will only proceed if A1 contains a valid input)
 
Hi Sir this is Gio from Philippines...... it's my first time here in
this site... i am wondering how can i solve this problem in excel and please
help me.. I used excel 2000 in creating an inventory program in the hospital.
I used this excel inventorry program in our suppply room, i used one
worksheet per item. and i have almost 300 items in the supply room or almost
300 worksheets. I saved it as a template for all i know it is safer to save
it as template rather than saving it as ordinary excel files. The program was
working well, but not when i started linking(hyperlink) it from a certain
file that i always used. Then i have save it several times as a template but
i notice that the program malfuncitons, it doesnt compute the formulas i
created and some formulas are gone. Why is this happening. when i add some
items in the inventory it wouldnt add to the current balance, why is this
happening? Will you please help me, you wer the only people who can only
help me with this kind of problem......please....
 
Thanks, Max !
I was stuck in something and forgot to check this thread.

The 2-variable table is new to me and it does solve the single data set
case.
Unfortunately, I have a lot of data sets in Sheet1(That's why I am
seeking a way to avoid adding more content to the crowded sheet), each
changes with A1 accordingly.
Should I build a table for each set or the table can be expanded to
accommodate all data sets?

As to the A1 value in the example above, I used data validation so I
can only choose from three options.

Good weekend
 
Back
Top