Lock Columns to Scroll and Insert Rows in only One Column

**Jaack** · 11-13-2010, 10:22 PM

Hello folks,

Glad I found this site. I look forward to learning and knocking off the rust when it comes to Excel.

My issue besides being like Sgt. Schultz is I have two date ranges. I'm doing research on OTCBB stocks. One site provides histories with share prices out to the 4th decimal place but does not include dates when no trades occurred.

Another site, Yahoo Finance, only shows share price to the 1st decimal but includes the information for days when no trades were made.

I need the 4th decimal place and the zero trade days to get accurate information. I need the zero trade days to get an accurate volume average over different date ranges and I need the 4th decimal because many OTCBB stocks are penny stocks so the most accurate trade price is required.

I've included a screen shot of the dates. the column on the left are all the dates I need to include. The column on the right are the dates where trades occurred.

Is there a way to freeze the column on the left so I can scroll both the left and right columns as if they're locked to each other but insert rows into the right column only to copy the dates?

\1

**outofthehat** · 11-15-2010, 07:42 AM

Hi Jaack,

Welcome to the forum. I think the attached achieves something close to what you want.
First sheet has a list of all dates; second sheet has a list of prices for dates where trades occurred.
I've added several columns into the first sheet so you can see what's going on.

The first uses VLOOKUP to find the relevant date in the list of trades and return the price. If the date's not there, it returns a #N/A error.

The other two columns use IF and ISERROR to do different things if there's an error - the first puts in a zero; the second puts in the previous price.