Formulas changing when I refresh data.

[mark hansen]

I have a long list of names that I import each time the spreadsheet open. Since the name list Is very long, I set the helper columns to allow the user to type a few characters to shorten the list, when they use the Data Validation drop down box.

The problem I'm seeing is each time I open and reimport the list, last couple of names are not showing on the list because the formulas are changing. I re-drag down the formula and it works again. I save my work and reopen, and the formulas are changed back. I'm using a Data Connection to pull in the data from a text list. The formulas are outside the area of the connection, to the right.

The first helper columns has a 1 if the text is found in the name. I'm using =--ISNUMBER(SEARCH($BD$32,AD364)) where $BD$32 is the cell with the text string to search for. AD364 is the cell with the name. Of course the line number changes but matches the row number and the Name is in column AD. The formula yields a 1 or a 0. This is the column where the formula changes. Throughout the list, the AD364 is on line 364 but when I get to a certain point, the AD364 address will start looking a different line. So, as I start on line 357 the formula is correct. As I arrow down the column, the formula is correct then the AD row number will jump a line (down) and start looking at the row below.

I hope this makes sense. I know the AD364 argument is relative but I don't know what would cause the data import to adjust the formula.

Any ideas regarding what I should look for?
Mark

 

[Jeffrey Mahoney]

I get what you're saying, it's just hard to visualize without seeing your spreadsheet. So many unknowns to start. Seeing the process for me is the key.

I use Index and Row() or Offset so that the formula isn't hardwired. Like =Index($AD:$AD,row()) will give you AD364 if your formula is on row 364. or if your formula is in AE364 then you could use =Offset(AE364,0,-1)

Cut and paste don't ruin the formulas this way.

 

[Fazza]

I like Jeffrey's post. I'm also finding it hard to visualize. My comments might be off track.
If the formulas are contiguous with the import, check the property/setting to fill down formulas (upon refresh).
Another thought, and this may be not helpful, consider if using named ranges helps. Named ranges on worksheet imports adjust with refreshes so may help somehow - that is if you can use named range references in the formulas, Somehow I think this is not relevant here.

 

[mark hansen]

Jeffery and Fazza, thanks so much for your help. I was thinking along the lines of Jeffery's direction and was doing searches on how to use Row() in a formula like that, and didn't get anywhere... I didn't think about wrapping it in INDEX. So far it's working as I reimport over and over for testing...

Fazza, I'll look into the property settings to auto fill down formula. Currently I have the formulas going down to row 5000 which way more than this name list will ever hold. by having the formulas fill in only as long as they need to, will save some diskspace, memory etc, since I don't have unneeded rows occupy space. Currently the connection fills X columns, and I just put the formulas outside that space...If I moved them into the next column, that could save space.

Thanks again form your help!!
Mark