Jul 22 2021 09:21 AM
Jul 22 2021 09:21 AM
The formula works all the way down my data and then just returns #REF! for the last 4 entries but there is still data to read across both sheets sitting there available and it won't register it. I'm at a loss as to what to do.
It is meant to pull stock levels from one sheet and place it on our ordering sheet ('Manyara Order' or 'Walking Order') but it gives up when it gets to the last 4 values - 47, 48, 49, 50. Never experienced this before.
Jul 22 2021 10:05 AM
Jul 22 2021 10:36 AM
Jul 25 2021 08:59 AM
@L z. HI Thank you for taking a look - the stock is drawn from a google sheets and populated to a 'current stock' sheet within this workbook. It is the 'walking order (2)' where is just gives up at cell 180 using a vlookup but then reads the wrong data about 10 cells later. It is so strange how it just won't work - all the data is there to be read - I have seen other posts online about this and then using some convoluted way to get around it but my excel is still very much on the starting blocks so coding and '?VGA?' goes completely over my head.
Jul 25 2021 09:46 AM - edited Jul 25 2021 09:47 AM
Ok. I took the freedom to delete a bunch of unecessary extra rows (see your attached file size now) but that didn't cause the issue
It took me about 5 minutes to understand which column/formula you had an issue with in 'WALKING ORDER (2)' and Yes strangely enough, even after removing the IFERROR around VLOOKUP I didn't get anything but something that looked like a "blank" value (same thing when pressing F9 in the formula bar)
Went to sheet 'CURRENT STOCK' and saw at the bottom a bunch of empty rows, but not all were empty, some had #REF! errors. And that's obvioulsy what caused the issue. After deleting those rows the formula in [Column1] sheet 'WALKING ORDER (2)' populated no problem
Double-check and let me know if anything is still wrong or if I missed something
Jul 25 2021 10:06 AM
Jul 25 2021 10:51 AMSolution
I didn't pay attention to this previously. In sheet 'WALKING ORDER (2)' [Column1] and [Stock] your formula were (respectively) in row 10 (and then down):
The problem here is you don't provide VLOOKUP a value for its optional last parameter [range_lookup]. And the default value for the latter is TRUE/1 which means that the data in your lookup table must be ordered/sorted otherwise you get random/inaccurate results. [INGREDIENTS] are +/- well ordered in Table STOCK but the last few "items": CHARCOAL (BAGS) and KEROSENE (LITRES)
I updated your formulas in [Column1] and [Stock] sheet 'WALKING ORDER (2)' so they now say:
and magically enough