Dear All,
In the example file, there are 3 spreadsheets. The "Lot no. 1" & "Lot no. 2" sheets are used to enter all the information to manage inventory & as you can see, some items are not dispatch so the cell value is blank.
Now, Can anyone please tell me a formula or macro from which i can scan both spreadsheets with blank "Invoice no."or "Dispatch date" columns and if the condition is true then copy all the information in the adjacent row to stock spreadsheet.
Condition I'm looking for is: If a cell in either "Invoice no."or "Dispatch date" column is blank then copy all the information in the adjacent row of "Lot" Sheets to a row below the headings in "Stock" spreadsheet. If the cell is not blank then search for next cell and copy the info if true but it should not leave a blank row in stock spreadsheet. Suppose there are 25+ grades of each material, we can't just write all the grades in stock table & paste the stock formula for quantity. Which is why, the formula should look for all the blank value & keep pasting the info in the stock table.
Is this possible in excel? Can this be done with Vlookup, if, isblank & loop functions?
In the example file, there are 3 spreadsheets. The "Lot no. 1" & "Lot no. 2" sheets are used to enter all the information to manage inventory & as you can see, some items are not dispatch so the cell value is blank.
Now, Can anyone please tell me a formula or macro from which i can scan both spreadsheets with blank "Invoice no."or "Dispatch date" columns and if the condition is true then copy all the information in the adjacent row to stock spreadsheet.
Condition I'm looking for is: If a cell in either "Invoice no."or "Dispatch date" column is blank then copy all the information in the adjacent row of "Lot" Sheets to a row below the headings in "Stock" spreadsheet. If the cell is not blank then search for next cell and copy the info if true but it should not leave a blank row in stock spreadsheet. Suppose there are 25+ grades of each material, we can't just write all the grades in stock table & paste the stock formula for quantity. Which is why, the formula should look for all the blank value & keep pasting the info in the stock table.
Is this possible in excel? Can this be done with Vlookup, if, isblank & loop functions?