Populate 2 columns with data from a different two columns based on one cell's value

I have a spreadsheet that I am using to design dashboards. The graph will pull its data from the two columns that are blank on the attached spreadsheet. What I am trying to do is have those two columns populate with the data from the respective ranges (i have not named them in excel, but rather gave them a higlighted heading). Cell A1 will either be 1, 2, 3, 4, 5. If A1 =1 then I need the two empty columns to be populated with the data in the columns for that range and so-on. Will probably make more sense with the spreadsheet.

Hi

I'd use a series of defined names. Select the range E5:F9 and give it the defined name _1. G5:H7 is _2 and so on.

Then select the range B5:C11 and array enter the formula =INDIRECT("_"&A1)

If there is missing data in the original range, you will get a #N/A error in the output, but, depending on exactly how you have your graph set up, it may not appear. To be more certain of this update your example file with a sample graph for review.

HTH

rylo

Thanks for the response. I'm not sure what you mean by "Then select the range B5:C11 and array enter the formula =INDIRECT("_"&A1)". The and array part is throwing me off.

As far as the graph goes, the only option I have is to tell it to ignore blank cells at the end of the range. The software for the dashboard is xcelcius and doesn't recognize what #N/A means. So I would need #N/A to display as " ". Otherwise it will treat #N/A as a text string.

Thanks for the response. I'm not sure what you mean by "Then select the range B5:C11 and array enter the formula =INDIRECT("_"&A1)". The and array part is throwing me off.

As far as the graph goes, the only option I have is to tell it to ignore blank cells at the end of the range. The software for the dashboard is xcelcius and doesn't recognize what #N/A means. So I would need #N/A to display as " ". Otherwise it will treat #N/A as a text string.

Ok. So this probably isn' the best fix for this problem but I used a nested IF function =IF($A$1=1,E5,IF($A$1=2,G5,IF($A$1=3,I5,IF($A$1=4,K5,IF($A$1=5,M5,""))))). Then I copied over for the cost column and drug down to affect all rows in the column. That did the trick, but now my problem is that if there is no information in the designated column the value returned is 0. I tried to insert another IF around the first to say that if it is 0 display as blank, but I got a circular reference. Is there another way to tell it to display 0 as blank?

Hi

OK, slight change

Enter the defined names as before, select B5:C11 and array enter the formula =IF(ROW()-4>ROWS(INDIRECT("_"&A1)),"",INDIRECT("_"&A1)). To array enter a formula, hold down the CTRL and SHIFT keys, then press enter. You should find that the formula will then be surrounded by braces ({}). This should put in a blank as required.

HTH

rylo