Using INDIRECT to make VLOOKUP exclude the row that the lookup value is on.

[dacheeba]

Hi all,

I've been working on this chart for over a month now, and all the hard work is nearing an end. Aside from a couple of QOL things, I only have one major issue keeping this chart from being the tool I need it to be at work.

For context, my employer has a decent ERP system, but they have been careless in its implementation, no one who currently works there was employed when the system went live, and consequently, it has a LOT of garbage information. For this reason, the system analytic tools are useless.

This chart pulls data from 3 reports generated by the ERP system: the Advise report, the On Hand report, and the Production Forecast report. Its purpose is to analyze on hand quantities of items, and track incoming open orders, while filtering the noise.

The problem:

In order to order effectively, there must be some overlap between incoming orders and on hand quantities, so that there is no production downtime. That overlap needs to be accounted for in the duration of the items, because sometimes the overlap can be quite significant.

Trying to do this with a VLOOKUP causes circular errors because it checks a calculated date that includes the cell where the VLOOKUP calculation is preformed.

My solution is to use INDIRECT to exclude the row that the VLOOKUP is calculated on, which at first glance seems to work. It is to lookup the array above the row, and below the row.

This might sound dumb, but the logic of getting both conditions met, where it performs both actions(lookup above/below), escapes me. I might just be burnt out, I have had to parse some of my formulas in Notepad++ just to work out the logic, but I'd really like to wrap this up. As I said, I've been at this over a month, doing most of the work at home, unpaid. not that it matters a lot, this is for my own purposes after all.

Anyways, here are some notables:

1. The scheduling table has a helper column (Column 1) to index part numbers. Every change to formulas, or the underlying data, should be done with all sorting and filters turned off. Any other time this table must be able to be sorted or filtered.

2. The formula in question is in Column K, the Overlap column of the Scheduling table. It should check the Promised Date(Column I) of the row being calculated, against the End Date(Column N) of all other similar Item Numbers, and if it is less, output the number of NETWORK days of the difference. It needs to check against Item Numbers and On Hand, although On Hand parts will never need to be calculated for an overlap for themselves.

3. The chart has been moved out of the way to BA650, because it takes a while to draw.

That's all that I can think off at the moment. Also, any performance suggestions would be appreciated, it isn't terrible slow, but I feel like it could be faster.

Thanks!

The xlsm is 4.23 MB, I tried to zip it down, got to 3.14 MB, but the forum still won't let me upload it. I put the zip file in my Google Drive.

https://drive.google.com/file/d/1IjW...ew?usp=sharing

[protonLeah]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

[dacheeba]

protonLeah,

Thanks for the info, but as I mentioned in my post, the file size is too big, and for some reason even though the zip folder was under the file size limit listed, it would not accept the upload.

I tried to trim a lot of the underlying data, but could still only get it down to 3.1 MB.

You should be able to download from Google Drive, and you can even do it in incognito mode, so its not like you have to sign into anything.

[dacheeba]

So, I decided that SUMPRODUCT would likely suit my needs better than VLOOKUP, but am having a problem.

Please Login or Register  to view this content.

The INDIRECT inside of the SUMPRODUCT returns a #VALUE error before it checks the condition.I had the same issue with an INDEX MATCH, but I chalked it up to how the CHOOSE function behaves when entered in as an array.

[dacheeba]

So, I have confirmed that:

Please Login or Register  to view this content.

works when entered in as an array, however:

Please Login or Register  to view this content.

does not. INDIRECT returns a #VALUE error.

I need the INDIRECT to maintain sort and filter capabilities. Why is it not working?

[AliGW]

Attach the workbook.

Oh, and explain "not working" - in what way isn't it?

[dacheeba]

Ok, I have made a cheap facsimile of my chart. I don't understand why people on here are so adverse to external file servers, especially when the file size for uploading here is so small, but here it is.

It should be noted that there are a lot of features missing from this example, notably how the data scraping occurs.

The table column "Overlap" is the one in question.

"Not working" as in, INDIRECT returns a #VALUE error?

[AliGW]

Nobody is averse to external file servers. It's quite simple: not all members are able to access file-sharing sites (think company firewalls, etc.). So, if you want the best of the help available, attach the file here. I don't know why so many members are averse to attaching files here.

I will have a look - thanks for the file.

[AliGW]

There is no formula in the column in question in your file, and when I copy and paste the one you gave above, I get a syntax error. What exactly is the formula that is producing the #VALUE! error? Are you entering it with CTRL+SHIFT+ENTER?

=IF(SUMPRODUCT((C6:C14=Scheduling[[#This Row],[Item Number]])*(N6:N14>Scheduling[[#This Row],[Promised Date]])), INDEX(C6:N14,MATCH(1,(C6:C14=Scheduling[[#This Row],[Item Number]])*(N6:N14>Scheduling[[#This Row],[Promised Date]]),0),12)-Scheduling[[#This Row],[Promised Date]],0)

The bit in red is rejected.

[dacheeba]

Thanks for looking Ali, and your explanation of the attachments issue makes sense, but now we are faced with the problem of looking at 2 very different workbooks.

The sample workbook should have something like:

Please Login or Register  to view this content.

entered in H8 as an array formula, copied down(H6 need to be set to 0 to avoid the circular reference in this workbook). But for some reason this does not work in this workbook, were as it does in my actual workbook.

I'm struggling here, lol.

[JeteMc]

Can you explain 'does not work'?
I selected cell H8, pasted the formula from post #10 into the formula bar, confirmed by pressing Ctrl, Shift and Enter, and copied the formula down to H15 (and up to H7).
I got the following values in the column starting with cell H7: 0, 0, 31, 5, 4, 0, 4, 4.
What values would you expect if not those?
BTW, I attempted to work with the file from post #1. After downloading and pressing the 'enable editing' button I got the version 2010 message that my computer was starting Excel. The message stayed on for the entire time that I was out for my afternoon walk, almost an hour. When I got back I had to stop the process and still could not work with the file.
That may be another reason uploading large files does not produce helpful responses.
Let us know if you have any questions.