Q1. It looks like you're trying to do some kind of date matching, but without seeing the actual sheet, it's a little hard to provide an exact formula.
A few thoughts though:
- You probably already knew this, but you will need to change Cell(i, j).Value to Cell(i, j).Formula and get rid of the apostrophe before "=INDEX" if you want your formula to actually work.
- In your MATCH function, you are searching for AH$4 & TEXT($B8,""m/dd/yyyy"". This looks a little suspicious to me. What is in cell AH$4? Does Oracle spit out some kind of weird prefix before dates? If you just had plain dates in your cells, you wouldn't need to worry about the format (you could get rid of TEXT and just do a pure comparison). Excel stores dates as a "serial" number. Also, check out the help on DATEVALUE. That might help you to get things converted over to pure dates.
- Just a general word of advice. Get your MATCH expression working first. Then try inserting the MATCH expression into the INDEX expression. This will help you to isolate your exact problem.
Q2. It looks like you have the list of pipelines already on your sheet, so you can create a validation that will guarantee that the user only selects a legitimate pipeline.
- Assign a name to your list of pipelines. You do this by selecting your list of pipelines, clicking Define Name from the Formulas tab of the ribbon, then entering a name.
- Choose a cell on the worksheet for the user to select a pipeline.
- Click Data Validation on the Data tab of the ribbon.
- For Allow, choose List.
- For Source, type an equal sign followed by the name you assigned for your list of pipelines. So, if your name was Pipelines, you would enter =Pipelines.
Now, when the user clicks on that cell, only the Pipelines will appear in it.
DanM
2009-07-14 05:34:49