Solved: Array Problem

[Hoopsah]

Hi

not just an Array problem, also me headbutting the screen problem.

I have a sheet where the user inputs a reference and it should list all the occurances of the reference taken from the Database tab.

It sort of works except for a couple of things.

The formula I have in cell A12 - no matter what I do it repeats itself in cell A13 - I cant change 1 without the other changing too.

This is causing the rest of the formulas not to work and resultantly I don't get the right details populating the correct area.

What am I doing wrong that I can't seperate these 2 cells??

All help will be welcomed,

Cheers

Hoops

[TMShucks]

I would select the first cell with the array formula, press F2, select all of the formula and copy it. Put that formula into a "free" cell and put an apostrophe (') at the beginning (making it just text).

Now, go back to the cells where you have the array formulae and delete them all.

You should be able to paste the formula back into the cell as you haven't done anything to clear the clipboard ... if not, just go back to the "free" cell and copy it again. So, paste the formula into the first cell and press Ctrl-Shift-Enter. You should get 11. Finally, drag the formula down as far as you need it.

Once you're happy, delete the free cell.

Regards, TMS

[mohanvijay]

Try this (not a array formula)

formula in cell A12

=IF(ISERROR(MATCH($I$8,Database!$C$9:$C$22,0)),"",MATCH($I$8,Database!$C$9:$C$22,0))

formula in other cells (A13)

=IF(A12<>"",IF(ISERROR(MATCH($I$8,OFFSET(Database!$C$9:$C$22,Edit_Existing!A12,0),0)),"",MATCH($I$8,OFFSET(Database!$C$9:$C$22,Edit_Existing!A12,0),0)+A12),"")

and copy the A13 and paste A14:A..

[Hoopsah]

Done that,

now when I press CTRL + SHIFT + ENTER nothing happens, no curly brackets appear!!

[mohanvijay]

did you try my above formula?

[TMShucks]

I guess you're replying to my instructions ...?

If you copied the "free" cell, did you remove the apostrophe?


See the attached updated workbook.

Regards, TMS

[Hoopsah]

Sorry Mohanvijay - I hadn't seen your formula when I replied.

TMShucks - I copied your data into mine and it works perfectly.

Thanks for all your help guys :thumb

Marking as solved

[TMShucks]

You're welcome.

Regards, TMS