Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index or Offset at specific intervals???
Hi, I'm struggling here.
Here is my Layout: Period 1 Period 2 Period 3 ... Cost Unit Hours Cost Unit Hours Cost Unit Hours Worker 1 Worker 2 Worker 3 .... On a cover sheet, I have a VBA combo box that lists all the Period #'s I've entered. I also have a "Cost", "Unit" and "Hours" columns on that cover sheet. Depending on which Period # is selected on the drop down, I want to populate the cover sheet with that specific period # data. I need to use a reference function that can skip columns at regular intervals, perhaps by using the reference cell tied to the VBA combo box? To make things trickier, I'm continually adding more data. So every 2 weeks, 3 columns will be tacked on, or all existing data will be shifted over 3 columns (I'd prefer to do it this way), and 1 more Period # will be added to the drop down list. I'm sure someone's had to deal with this before. -Beric |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index or Offset at specific intervals???
I figured it out myself...
=INDEX(Sheet1!$F$2:$JW$300,3,MATCH($B$4,Sheet1!$F$ 2:$JW$2,0)+1) the MATCH function gives the column location for the INDEX to find the value. The "+1" Shifts one column to the right. "Beric Dondarrion" wrote: Hi, I'm struggling here. Here is my Layout: Period 1 Period 2 Period 3 ... Cost Unit Hours Cost Unit Hours Cost Unit Hours Worker 1 Worker 2 Worker 3 ... On a cover sheet, I have a VBA combo box that lists all the Period #'s I've entered. I also have a "Cost", "Unit" and "Hours" columns on that cover sheet. Depending on which Period # is selected on the drop down, I want to populate the cover sheet with that specific period # data. I need to use a reference function that can skip columns at regular intervals, perhaps by using the reference cell tied to the VBA combo box? To make things trickier, I'm continually adding more data. So every 2 weeks, 3 columns will be tacked on, or all existing data will be shifted over 3 columns (I'd prefer to do it this way), and 1 more Period # will be added to the drop down list. I'm sure someone's had to deal with this before. -Beric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OFFSET, INDEX, MOD.. ?? | Excel Discussion (Misc queries) | |||
using OFFSET(INDEX(MATCH))) | Excel Discussion (Misc queries) | |||
INDEX, MAX, OFFSET, MATCH | Excel Worksheet Functions | |||
Index, Vlookup, Offset | Excel Worksheet Functions | |||
Looking in one col, counting in another. OFFSET?INDEX? Help!! | Excel Worksheet Functions |