how to display different content depends on some conditions?

[sam]

Hi,

In one worksheet(detail), I define two ranges, Range("A1:B10") contains
food info, Range("C18") contains beverage info.

And in main worksheet(order), if the customer ticks the food info box,
i want the food info I defined in worksheet(detail) displayed below the
tick box; if the customer ticks the beverage info box, i want the
beverage info I defined in worksheet(detail) displayed below the tick
box. I assumed that only one box can be chosen(either food or beverage
box).

I think I can use the IF clause to slove this problem. I know some
basic knowledge about IF clause, like IF(logic_test; true; False). I
know how to display some character in the field of ture. However, I
don,t know how to display the Range info Like Range("A1:B10") from
worksheet(detail). "if clause" can solve this problem? If you have some
relevant experience about this, could you give me some hints? Thank you
in advances!!!


Regards

Sam

 

[Guest]

Assume you are using option buttons from the forms toolbar and they are
linked to cell F1 of the sheet where the display will be made. Thus, if I
choose the food option button, F1 will display 1 and If i select the beverage
option button, F1 will display 2 (you can put one of the option buttons over
F1 to conceal it).

so option button Food is over F1 and Option button Beverage is in F2

assume the display will start in F4

in F4 put

=IF($F$1="","",IF($F$1=1,Detail!A1,IF($F$1=2,IF(ROW()>11,"",Detail!C1),"")))
You could simplify it to
=if($F$1="","",if($F$1=1,Detail!A1,if(row()>11,"",Detail!C1)))
but you might want to expand it later to include 3 or more choices.

Now select F4 and drag fill to G4
now select F4:G4 and drag fill down to F13:G13

 

[sam]

HI,Tom

Thank you for your help!

I tried this method. It works.
However, it needs to drag the F4 manually to display the whole range
info. Is there any other method can display the whole range
automatively. I tried the clause F4
=if($F$1="","",if($F$1=1,Detail!$A$1:$B$10,if(row()>11,"",Detail!$C$1:$D$8))).
It doesn't work. Do you have some ideas on this issue? Thank you in
advances!!!

Best Regards

Sam

Best Regards

 

[Guest]

Can you put the formula in 1 cell and have it display 20 cells of data

In that one cell: not unless you want to concatenate all the values into a
single string - basically =formula1 & formula2 & char(10) & formula3 &
formula4 & char(10) & . . . & formula20

In a range 10 rows by 2 columns: No, you need one formula per cell - just
like I showed you.

You can enter something similar to it as an array formula, but I don't see
what the issue with doing as i said.

 

[sam]

HI, Tom

Thank you for your reply first!

Now, I am thinking about whether using VBA can solve this problem more
easily. Maybe first I can define an range as an object, and then call
it. Do you have some ideas on using VBA to solve it? Thank you!

Regards

Sam