Array formula adding a weird line of traveling data

branfootbal

New Member
Joined
Oct 13, 2015
Messages
3
So I have a what I think is very weird inconsistency in my excel workbook. Whenever I update my data the latest day I update for the very first row is always wrong. Now this doesn't effect much in turns of data integrity it is still about 99.9% accurate from the supplied source.

The reason why I want to solve this is because the wrong line travels to the next day so then once the error moves on to the next day all the data is pushed up a row. This means that you need to compensate for that row shift when we enter our manual entries. Which isn't very easy to teach to 25+ people. "Needs to be idiot proof"



Image showing error:

Error always occurs on the first row of data of the latest updated day.
https://www.dropbox.com/home?preview=Screen+Shot+2016-02-20+at+5.41.18+PM.png


Link to workbook:
https://www.dropbox.com/s/i8pg0fgeif431rd/FebSegmentsV3.xlsm?dl=0



Formula being used is:

=IFERROR(INDEX('Paste!'!$C$7:$C$20000,SMALL(IF('Paste!'!$B$7:$B$19998=$C$125,IF('Paste!'!$A$8:A$19998=$B$3,ROW('Paste!'!C$7:$C$19998)-ROW(F$7)+1)),ROWS(F$127:F127))),"")


I know it isn't a work of art but it seems to work pretty well for the intended use.


Any help would be greatly appreciated. Also, Macros are only a print button that isn't fully working and a bunch of expand buttons to hide and unhide data lines.
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
That is a pretty standard ARRAY formula, are you entering it using CTRL SHIFT ENTER, and not just enter?

(I am not able to access file-hosting sites at the moment)
 
Last edited:

branfootbal

New Member
Joined
Oct 13, 2015
Messages
3
I am using CTRL SHIFT ENTER, And the formula works fine but for some reason it is pulling an extra row of data that is following along to the most recent updated day. Then by the extra row showing up it is causing the names that I have to manually enter to be offset a row after the next update. Since the rouge data row is jumping days update after update to the most recent day.
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
I notice that your INDEX range ends at 20000 but the others end at 19998. Try making them all the same
 

branfootbal

New Member
Joined
Oct 13, 2015
Messages
3
Very strange that I didn't notice those somehow changed to 19998, But I went through and evened them up and no luck.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,474
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top