0

I’m a new user looking for help on an Excel formula/macro code.

To demonstrate my scenario, let’s say I have a drop down list with values of “Breakfast, Lunch, and Dinner”. If the user selects breakfast from the dropdown, a unique breakfast menu with items and prices as the columns appears below. If the user selects lunch or dinner, a different menu appears below the dropdown cell.

I wish the formula was easy as: IF(A1=“Breakfast”,PASTE(BreakfastMenu) )

Thank you in advance for your help!

Improve this question
2
  • Did you try the FILTER function? support.microsoft.com/en-us/office/…
    – Lee
    Commented Aug 10, 2023 at 8:29
  • Hi Lee, thank you for the help - the FILTER function works, but the formatting of the menus in my example don’t carry over (for example - the fill color and borders do not appear as they do in the referenced table). Thanks again.
    – 190191cd
    Commented Aug 10, 2023 at 14:54

2 Answers 2

Reset to default
0

What version of Excel do you have? The more recent versions allow a formula in one cell to spill into an entire range of cells. That's pretty close to what you want:

=IF(A1="Breakfast",Menu!A1:F38)

That's pretty rough, though, and it would get unwieldy to input each option. Let's combine that with IFS() so we can just stop at the first true result.

=IFS(A1="Breakfast",Menu!A1:F38,A1="Lunch",Menu!G1:L38,A1="Dinner",Menu!M1:R38,TRUE,"(Please select a meal)")

That will return a lot of 0 values, though, because of all the blanks. Let's roll all that up into one variable and then replace all the blanks with actual blanks instead of 0.

=LET(result,IFS(A1="Breakfast",Menu!A1:F38,A1="Lunch",Menu!G1:L38,A1="Dinner",Menu!M1:R38,TRUE,"(Please select a meal)"),IF(result="","",result))
Improve this answer
2
  • Thank you - the formula worked great. The only problem is that the formatting of the menus does not appear underneath (such as font colors, fill colors, and borders). Is there any way to remedy that within the formula? From my research into this problem, it looks like I may need to use macros to solve it…
    – 190191cd
    Commented Aug 10, 2023 at 14:48
  • I have version 2302 of Excel. I believe it’s up to date. Thank you again for all your help!
    – 190191cd
    Commented Aug 10, 2023 at 14:49
0

To get a copy of values with cells' formatting you can use Range.Value(11) property in a UDF. This is an unusual function. It copies its argument from one cell to another, but the function call itself is placed in another non-used cell. Code in a universal module.

Function VAL_11(src As Range, dst As Range) As String
      dst.Value(11) = src.Value(11)
End Function

Function takes two arguments, the first is source range address, the second – destination address.
Both ranges can be individual cells or rectangular areas with the same dimensions.
This function requires ranges (references) as arguments, so it can be used e.g. with IFS function, but not with FILTER function. Its return value is an empty string (providing there is no error).
Example of use with the IFS function from the previous post

=VAL_11(IFS(A1="Breakfast",Menu!A1:F38,A1="Lunch",Menu!G1:L38,A1="Dinner",Menu!M1:R38,TRUE, Menu!S1:X38), C1:H38)

Sample file is here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .