Array formula not working in current workbook - works fine in previous versions

mattstopel

New Member
Joined
Jun 27, 2018
Messages
10
Hi All,

I am having a real frustration at the moment with one of my workbooks.

I have an array formula, nothing too complex that won't return any results.

In my previous version, V5, it works flawlessly, in V6, no good at all. Formula is the exact same between both worksheets. Named arrays are still the same also.

Formula is =IFERROR(INDEX(EntryID,LARGE(IF(Entryunit=$D$6,ROW(EntryID)),ROW(1:1))),"") and entered as CTRL+SHIRT+ENTER.

Any ideas accepted!!

TIA
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,316
Remove the IFERROR part of your formula to see what error if any is being produced.
 

mattstopel

New Member
Joined
Jun 27, 2018
Messages
10
@AhoyNC

This returned a #value error.

error in value - a value used in the formula is of the wrong data type? Cells are formatted as D6 is formatted as Text, but General doesn't change this?


Just very strange it works in a previous sheet and only difference is new data has been added? I have ensure the EntryID and Entryunit are formatted as general.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,316
No, changing the format of a cell will not change a number enter as TEXT to NUMERIC.

If you highlight the cell or cells and see an ! (exclamation mark) as below.
1574473711502.png


Click on it and choose "Convert to Number"
1574473784078.png
 

mattstopel

New Member
Joined
Jun 27, 2018
Messages
10
unfortunately no luck. Just filtered through the 8,000 sets of data and no issues. The adjacent column has some errors but they are not involved with the issue formula.
 

jmcdaid

New Member
Joined
Jan 9, 2012
Messages
28
Office Version
365
Are you on Office 365? If so, does your Excel have Dynamic Arrays? You can tell by entered this formula into a blank cell =SEQUENCE(10). IF you do, then I believe your formula can be simplified.
 

mattstopel

New Member
Joined
Jun 27, 2018
Messages
10
HI jmcdaid,

it returns 1, so i assume it does have Dynamic Arrays???

If so, keen for help, this has been doing my head in. I have a similar sheet that looks up another column and returns without errors.

Advice greatly appreciated.

Regards
 

Forum statistics

Threads
1,078,440
Messages
5,340,296
Members
399,364
Latest member
farhan11941234

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top