How to quote cells when "IF" word is involved

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.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Ming" <zhongming@gmail.com> wrote in message
news:1123801935.859627.21650@g47g2000cwa.googlegroups.com...
> 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
>

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

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)

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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....

You should post your query as a new post,
not as a reply to an existing, unrelated post

See Chip's page for new posters:
http://www.cpearson.com/excel/newposte.htm

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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

Could you post a sample of your actual formulas running down C1:C100 ?

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Ming" <zhongming@gmail.com> wrote in message
news:1123886441.520897.48910@g47g2000cwa.googlegroups.com...
> 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
>