Hi,
I am trying to include vlookup function in sheet2 (cells: B2,B3,B5) to look up values in sheet 1 (File is attached herewith).
When two sheets are created in two workbooks, the mentioned vlookup function works correctly. But when sheets are get to one workbook, lookup function does not return the correct value.
Highly appreciate if someone could help.
Many thanks!
Thank you for the direction. I will try to get the issue sorted myself. I too believe i must do some self study in regard with tables and formulas.
Thank you very much for the support extended so far!
Could you please have a look at the file Test 5. Please ignore Receiving summary sheet (although it is a part of the whole, this issue is not relevant to that sheet:Table 1). Only Inventory and Order sheets are applicable.
Thank you very much for the support!
Hope attached file will give you better understanding on my requirement.
Inventory sheet shows inventory of items. Order sheet shows what has been ordered by different people. If the ordered item qty (check row by row) is less than inventory quantity for the same item, I need to get '1' in stock availability column in Order sheet. If ordered quantity is greater than inventory quantity, '0' should appear in stock availability column.
Thank you very much for the support!
Actually I want to compare item wise quantity in the two tables. The one you have given is fine if item order is same in both tables. But item order is not same in tables, it can be vary.
Thank you very much for the support!
Sorry, i do not understand what you mean by new formula. As suggested by you I used tables instead of hyperlinks and command buttons and it is working fine. The function i have mentioned above is the final part of my project but i can't get it right.
Below i have mentioned the formula again for your reference. This is not working.
=IF(Table2[Item]:[Quantity]<Table3[@[Item]:[Quantity]],0,1)
Much thanks!
Helo Roy,
Shall I open a new thread for above issue of excel tables?
Hi Roy
Hope my concern here is in line with the forum rules. I am still struggling to get above issue sorted. Waiting for a solution from you.
Much thanks!
Thanks for the explanation. But what I need is to first check the first row with inventory (in inventory sheet) and if inventory is enough 1 should be written in G2 (via formula). And then in next column (status) if i marked as "ordered" the same quantity from the same item should be deducted from inventory sheet.
I have mentioned the same question in below post also.[xpost][/xpost]
Yes it is the same question. I got to know about the rule of cross posting same question later. [Hyperlink to deduct specific cell value in excel vba][/Auto create a command button when click another command button in a specific cell and assign code to the newly created command button] .
Please excuse for that.
I have attached the wrong file before. Herewith attached the file with tables. Highly appreciate if you could check it and assist me below (this is the same mentioned above).
"I have got an issue while trying to compare Item quantities of two sheets.
Below function is not working in Issue sheet (cells: F2, F3, F4, F5). I want to get 0 for Stock availability column in Issue sheet, if item qty in table 3 is less than the same item quantity in table 2 which is Inventory sheet., and to get 1 for Stock availability column if, item qty in table 3 is more than the same item quantity in table 2. Please help me to sort this out.
Below is the formula i tried:
=IF(Table2[Item]:[Quantity]<Table3[@[Item]:[Quantity]],0,1) "
Many thanks!
Helo Roy,
As you advised i tried with formulas and it is perfectly fulfilling my requirement.
I have got an issue while trying to compare Item quantities of two sheets.
Below function is not working in Issue sheet. I want to get 0 for Stock availability column in Issue sheet, if item qty in table 3 is less than the same item quantity in table 2 which is Inventory sheet., and to get 1 for Stock availability column if, item qty in table 3 is more than the same item quantity in table 2. Please help me to sort this out.
Below is the formula i tried:
=IF(Table2[Item]:[Quantity]<Table3[@[Item]:[Quantity]],0,1)
Much thanks in advance!
Hi Could you please explain me why below function is not working in Issue sheet. I want to get 0 for Stock availability column if, item qty in table 3 is less than the same item quantity in table 2., and to get 1 for Stock availability column if, item qty in table 3 is more than the same item quantity in table 2.
Below is the formula i tried:
=IF(Table2[Item]:[Quantity]<Table3[@[Item]:[Quantity]],0,1)
Much thanks in advance!
Helo,
Your link and advise was really helpful and i was able to link the tables and update with command button as required. I will try on deducting from Inventory sheet when order request is accepted as you have advised.
Highly appreciate your support!! Many may thanks! Keep doing the good work! 
Isn't there a way to auto copy the row to the table?
Here I have attached the excel vba file i'm developing. Please have a look at it.
In this file above mentioned Receiving summary sheet is equivalent to Inventory sheet and above mentioned Inventory sheet is equivalent to Stock sheet.
Highly appreciate your effort. Thanks.
Thank you for your quick reply.
If Receiving Summary sheet is updated from VBA user form, is it still possible to have it as a table?
Hi, yes i checked it. I got an issue while trying to do it with formula. Could you please check the file attached and advise me on below.
I need to insert a formula to auto sum up the item quantity in Inventory Sheet (adding up the items in receiving record item wise).
Here I need to auto insert a new item to the Inventory sheet if receiving record had a new item. As an example, if blue pen is received on 20th Jan 2020, i need to add blue pen to cell B5 and add up the quantity of it to the cell C5.
Many Thanks!
Is it. Okay i will try the previous method.
Apart from that could you please check the attached file and advise me on how to insert a formula to auto sum up the item quantity in Inventory Sheet (adding up the items in receiving record item wise).
Here I need to auto insert a new item to the Inventory sheet if receiving record had a new item. As an example, if blue pen is received on 20th Jan 2020, i need to add blue pen to cell B5 and add up the quantity of it to the cell C5.
Many Thanks!
Is there a way to do this using a check box?
Yes that will be great. Just tell me the way and i will find out the details from internet.
