Advancing arrays based on value

**Capnponcho** · 01-17-2012, 03:36 PM

Hi,

I have a forumula that references an array. I would like to move the array into different column, based on a date I select in a single cell.

For example, the formula would reference cells A1:A10 for Jan. 1st. If I change the date to Jan. 2nd, I'd like the array to automatically reference B1:B10. January 3rd = B3:B10, and so on. As it stands, I have 60 days in the spreadsheet as column headers. When I enter a specific date into a "select date" cell, the array in my formula should reflect the corresponding date in the array.

I hope that is clear.

If anyone has any experience with this kind of puzzle, I'd be glad to hear your ideas.

Thanks.

CapnPoncho

**OnErrorGoto0** · 01-17-2012, 03:50 PM

It would be easier with a sample workbook, but in general terms the syntax is:
INDEX($A$1:$BH$10,,match(date_cell,$A$1:$BH$1,0))

**tigeravatar** · 01-17-2012, 03:51 PM

Capnponcho,

Attached is an example workbook based on the criteria you described. In cell C15 is the date that will be used. In cell C16 is this formula:
=IF(C15="","",SUM(INDEX(B2:BI11,,MATCH(C15,B1:BI1,0))))

Explanation:
Index(range,row,column) will load the entire range, and then return the cell from that range that is specified by row and column
If, like above, you leave 'row' blank - Index(range,,column) - it will return the whole column specified by 'column'

Let me know if you are having trouble adapting this to suit your needs.

**Capnponcho** · 01-27-2012, 10:19 PM

Hi,

I'm giving this thread a bump; I managed to attach the file I am working on, and I am hoping that the great examples given by everyone so far can be implemented in my formula.

The attached file has two worksheets: a master schedule and a daily schedule. The daily is set up to bring in data from the master. The daily has a drop-down field that allows the user to select the day. I would like the formulas to advance based on the day that is selected. For example, if the user is to select January 30th, the formulas that draw in data for each corresponding day/shift on the master would select data from the January 30th column.

The example given by tigeravatar is excellent, and it has the functionality of what I'd like to do, but I can't seem to get it to work with my spreadsheet.

Any help is great.

Thanks very much!

capnponcho

**tigeravatar** · 01-30-2012, 11:21 AM

Capnponcho,

Attached is a modified version of your example workbook. In 'DAILYSTF'!B4 is this array formula (which needs to be entered with Ctrl+Shift+Enter and not just Enter):
=INDEX('NOV-JAN'!$A$1:$A$32,SMALL(IF(INDEX('NOV-JAN'!$B$1:$BE$32,,MATCH($C$2,Dates,0))=$A$5,ROW($1:$32)),ROW(1:1)))

It is copied down until B9. B10 uses a slightly different version and resets the last argument, ROW(1:1), back to 1. The different version changes the =$A$5 to =$A$11. A5 and A11 contain the codes for that shift section that the formula needs to look for in the 'NOV-JAN' table. The formula also takes advantage of the named range you had setup called Dates, which is how it knows which column to use depending on the date choice made from the drop-down list in C2.