greggwallace
New Member
- Joined
- Feb 18, 2022
- Messages
- 2
- Office Version
- 365
Hi All
I've been working through a complicated costing workbook. I've nearly completed everything task apart from one lookup table. This isn't the final workbook but I've stripped the problem out to make it a little clearer. I have one sheet which is the data source "Cost Data"
And another which I'm trying to create a report that lists part number, date of cost and the cost.
I've stripped out the problem onto one tab below to simplify the problem.
So the problem....... I have the following formula in D16
=INDEX(Table13[[#Headers],[Jan-10]:[Nov-10]],,SMALL(IF(B6:J6>0,(COLUMN(B6:J6)-1)),C16))
I will eventually build up a complete formula but for now I've broken out the variable into B16 & C16. C16 works fine and i will use the "Helper Column" on the report sheet to give me the next date.
the last part of the problem is changing the ROW that the SMALL is using to look up the values. i think i have two options. Either use the ROW number to define the search row somehow or look up the part number to define the row.
This is pretty much where I'm stuck. i cant seam to come up with a way to change the ROW range dynamically. "B6:J6". I'm guessing it is either using INDEX MATCH some how or a VLOOKUP, but i cant quite figure it out.
Any hints in the right direction would be appreciated.
I've been working through a complicated costing workbook. I've nearly completed everything task apart from one lookup table. This isn't the final workbook but I've stripped the problem out to make it a little clearer. I have one sheet which is the data source "Cost Data"
And another which I'm trying to create a report that lists part number, date of cost and the cost.
I've stripped out the problem onto one tab below to simplify the problem.
So the problem....... I have the following formula in D16
=INDEX(Table13[[#Headers],[Jan-10]:[Nov-10]],,SMALL(IF(B6:J6>0,(COLUMN(B6:J6)-1)),C16))
I will eventually build up a complete formula but for now I've broken out the variable into B16 & C16. C16 works fine and i will use the "Helper Column" on the report sheet to give me the next date.
the last part of the problem is changing the ROW that the SMALL is using to look up the values. i think i have two options. Either use the ROW number to define the search row somehow or look up the part number to define the row.
This is pretty much where I'm stuck. i cant seam to come up with a way to change the ROW range dynamically. "B6:J6". I'm guessing it is either using INDEX MATCH some how or a VLOOKUP, but i cant quite figure it out.
Any hints in the right direction would be appreciated.