Cells reference won't update when dragged down

**benedictexcel** · 02-09-2012, 10:25 AM

Hi
I'm having a Hard time with my excel sheet. I wrote the following formula in the H9 cell:

=IF(ISBLANK(G9),"-",VLOOKUP(G9,Sheet2!$F$5:$G$370,2,FALSE))

I need this formula to lookup for G10, G11, G12,...., GN but when I drag it down, the G9 don't change for G10 even thought I'm in the H10 Cell. I am really mixed up because there aren't any $ removing the freedom of this cell in the formula. It's the first time I have a problem like that.
I tried updating the fields, the formula is working, I tried retyping it still won't increment. If I enter 10 instead of 9 manually, it works fine withe the new cell.
I also tried to select the first formula with G9 and the second with G10 and drag both of them, but then it alternate with G9 and G10 for the cells where I dragged the formula.

Please Help me!!!

**NBVC** · 02-09-2012, 10:40 AM

Hmm... strange... does it happen in other workbooks? Can you post the workbook (if not confidential).. or recreate another that shows similar issue?

**benedictexcel** · 02-09-2012, 10:50 AM

I tried it in another workbook and it works there. But I've worked a long time on special format for the sheet and reproducing it would take a long time since copying all the sheet in another workbook don't solve the problem. Here's a copy of the part of my workbook where the non-working formula is.

Book2.xlsx

**NBVC** · 02-09-2012, 10:58 AM

If you put the workbook in Normal view instead of Page Layout, it works.

**benedictexcel** · 02-09-2012, 11:15 AM

I worked on that for more than an hour! How come it took you 5 minutes ... 0_o but it's strange the view changes the behavior of the worksheet!! Still Thanks a lot!!

**NBVC** · 02-09-2012, 11:19 AM

I think Page Layout is designed for letting you see what it looks like printed... I guess they didn't feel that people would actually work in that mode