INDIRECT function and broken reference

[butterworm]

Hi Guys,

I'm using the INDIRECT function with data validation to make a dependent list, but when I quit excel, it loses the cell reference and I have to manually fix it. I'm making a spreadsheet for data entry and can't send it out like this, because the peons who will be using it won't have the know-how to fix the problem.

the formula I'm using is

=INDIRECT(SUBSTITUTE(B3," ",""))

after quitting, reopening the file, the formula becomes

=INDIRECT(SUBSTITUTE(#REF!," ",""))

anyone know why it's forgetting about the "B3"?

 

[butterworm]

nobody?

 

[Aladin Akyurek]

Just a hunch: Have you code in VBA doing something with B3?

 

[butterworm]

Just a hunch: Have you code in VBA doing something with B3?

no

I've discovered, though, that the cause is because the dependent list is sourcing its information from an entire column. because older versions of excel have a limit of 65k rows (give or take), anything outside this range returns the error that I'm experiencing. as excel is limiting this information whenever I save the file, it's automatically rejecting my cell reference and replacing it with the invalid reference.

I have the lists where the information is coming from set up so that users can add to them if they need to. is there a way to bound the limits of what will be selected if an entire column is selected. i.e., clicking on the column header will select only the first 1000 (or 10,000, or some number less than 65k) cells of that column?

 

[butterworm]

ok, so after a bit more playing, it's actually because excel doesn't like it if b3 is empty when I save the file. that's what breaks it.

not ideal, because what I'm trying to provide is a template that can be filled out. I don't want to resort to default values like "Choose from this list", but it's looking like I may have to.

 

[tusharm]

You may want to share your *actual* problem rather than what you have worked out so far. There may be a way of addressing this that doesn't break if B3 is empty, not to mention that having a data validation list with thousands of entries is...well, it goes beyond being user-unfriendly, it's nearly user-hostile. {grin}

no

I've discovered, though, that the cause is because the dependent list is sourcing its information from an entire column. because older versions of excel have a limit of 65k rows (give or take), anything outside this range returns the error that I'm experiencing. as excel is limiting this information whenever I save the file, it's automatically rejecting my cell reference and replacing it with the invalid reference.

I have the lists where the information is coming from set up so that users can add to them if they need to. is there a way to bound the limits of what will be selected if an entire column is selected. i.e., clicking on the column header will select only the first 1000 (or 10,000, or some number less than 65k) cells of that column?