The Scenario
You are an inventory controller. The monthly inventory audit is due Friday. You have 80 item codes in an Excel workbook — the Audit tab — the same 80 SKUs you review every month. You need the current stock balance for each one from ERPNext's "Main Store" warehouse so you can compare against your physical count.
Your current process: open ERPNext, run the Stock Balance report, export to CSV, open the CSV in a separate workbook, write VLOOKUP formulas against your 80 item codes, debug the #N/A returns — some are zero stock, some are name mismatches — fix the mismatches, and paste the quantities into the Audit tab. On a good month, ninety minutes. On a month when someone renamed an item, longer.
The bad version of audit week:
- Export the ERPNext Stock Balance report for all items
- Open the CSV, write VLOOKUP formulas against 80 item codes
- Debug the 12
#N/Areturns - Fix name mismatches, re-check, paste quantities into the Audit workbook
- Friday is here and you are still reconciling three items.
The fast version is one prompt.
The Easy Way: One Prompt in SheetXAI
SheetXAI reads your item code list and fetches the current ERPNext stock balance for each one, writing the quantity directly into the workbook.
Open the SheetXAI sidebar and type:
For each item code in column A of the Audit tab, fetch the current ERPNext stock balance from warehouse 'Main Store' and write the quantity on hand into column B. If the item is not found in ERPNext, write "NOT FOUND" into column B.
SheetXAI queries ERPNext for each item code and writes the result into column B. No VLOOKUP, no CSV, no cross-referencing.
What You Get
An updated Audit tab with 80 rows of stock balance data:
- Quantity on hand — pulled from ERPNext "Main Store" at the moment you ran the prompt
- NOT FOUND flags — items that returned no result, so you investigate those specifically
- Variance-ready — add a formula in column C (column B minus your physical count) and the audit view is complete
No VLOOKUP errors to debug. The quantities are in the workbook and the audit proceeds.
What If the Data Is Not Quite Ready
Inventory audits have reliable complications. SheetXAI handles them inline.
When item codes in the workbook have inconsistent formatting
Some codes are uppercase, some mixed case. ERPNext is case-sensitive.
Normalize all item codes in column A of the Audit tab to uppercase before fetching the ERPNext stock balance. Write the quantity on hand into column B. Write "NOT FOUND" if the item returns no result.
When you need balances from two warehouses in the same row
The audit covers "Main Store" and "Finished Goods" separately.
For each item code in column A of the Audit tab, fetch the ERPNext stock balance from 'Main Store' and write it into column B. Then fetch the balance from 'Finished Goods' and write it into column C. Leave blank if the item has no stock in that warehouse.
When you want items below reorder level flagged automatically
Reorder levels are in column C. You want column D to show a flag for any item at or below that level.
Fetch the ERPNext stock balance for each item code in column A of the Audit tab from 'Main Store' and write the quantity into column B. Compare each value in column B against column C (reorder level). Write "REORDER" into column D for any item at or below the reorder level. Leave column D blank otherwise.
When you need to pull stock balances, flag discrepancies, and summarize the audit in one pass
Your physical count is already in column B. You want ERPNext stock in column C, variance in column D, and a summary paragraph.
Fetch the ERPNext stock balance from 'Main Store' for each item code in column A of the Audit tab and write the quantity into column C. Calculate the variance (column B minus column C) and write it into column D. Write "INVESTIGATE" into column E for any row where the absolute variance exceeds 10 units. Then write a one-paragraph audit summary at the top of the Audit tab: total items audited, total items with variances, and the three largest discrepancies by absolute value.
The pattern: fetch, compare, flag, and summarize in one instruction.
Try It
Get the 7-day free trial of SheetXAI and run it against your audit item list before the next physical count. The ERPNext integration is included in every SheetXAI plan. For other inventory and operations workflows, see how to pull open Sales Orders for fulfillment planning or the ERPNext in Excel overview.