Can I make a formula for this?

[kmlo39]

I'm trying to do an IF function that looks like this
=IF($A$2="Name", Sheet2!A7:B18, IF($A$2="Name1", Sheet2!C7:C18, etc etc.

A7:B18 Range - A7 and B7 are merged

C7:C18 Range - C7 and D7 are merged and so on and so forth

It's giving me a value error and I don't know how to create a VBA or Macro for this. Can someone help with with a formula?

 

[Aladin Akyurek]

Care to word what you want to achieve, preferably by means of an Excel readable small sample posted directly here?

 

[kmlo39]

For example in sheet 2, I have a range of cells. A7:B18. A7 & B8 are merged (because it's the title of that range). A8:A18 are descriptions for B8:B18. B8:B18 are numbers. In sheet 1, I have a data validation dropdown with options Week 1, Week 2, Week 3, etc. On one of the cells, I inputted this IF formula =IF($A$2="Week 1", Sheet2!A7:B18, IF($A$2="Week 2", Sheet2!C7:C18, etc etc.

That formula gives me a value error. If I do this formula =IF($A$2="Week 1", Sheet2!B8, "No data"), it would copy only one cell and I have to drag and drop to show the rest of the numbers.

I want to be able to copy the range below to a different sheet once an option has been chosen. Let's pretend the 1st row is merged.
[TABLE="width: 300"]
<tbody>[TR]
[TD]Week1
[/TD]
[TD]Week1
[/TD]
[/TR]
[TR]
[TD]Price
[/TD]
[TD]$20,000
[/TD]
[/TR]
[TR]
[TD]DP %
[/TD]
[TD]3%
[/TD]
[/TR]
[TR]
[TD]DP $
[/TD]
[TD]$600
[/TD]
[/TR]
[TR]
[TD]IR
[/TD]
[TD]4.99%
[/TD]
[/TR]
[TR]
[TD]PMT
[/TD]
[TD]$$$
[/TD]
[/TR]
[TR]
[TD]etc
[/TD]
[TD]$$$
[/TD]
[/TR]
[TR]
[TD]etc
[/TD]
[TD]$$$
[/TD]
[/TR]
[TR]
[TD]etc
[/TD]
[TD]$$$
[/TD]
[/TR]
[TR]
[TD]etc
[/TD]
[TD]$$$
[/TD]
[/TR]
[TR]
[TD]etc
[/TD]
[TD]$$$
[/TD]
[/TR]
[TR]
[TD]etc
[/TD]
[TD]$$$
[/TD]
[/TR]
[TR]
[TD]etc
[/TD]
[TD]$$$
[/TD]
[/TR]
</tbody>[/TABLE]