I got this worksheet from a previous thread because the formulas as a whole are what i was looking for. However I need to move Column "D" QTY SOLD as Column A in "ORDER" sheet. In doing so all formulas in "INVOICE" sheet show errors. I have tried different ways to correct/change the formulas but to no avail. Can someone explain what i need to fix and how? or do it for me? I am so lost.
the previous thread is here : http://www.excelforum.com/excel-form...in-values.html
Part of your problem is that VLOOKUP works from left to right, so if you move the quantity column to A, you are going to have to completely rewrite the formulae. This is easy enough to do, but will require INDEX MATCH instead of VLOOKUP. You may also have to redefine the table.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
The VLOOKUP function can only return values from columns to the right of the lookup column.
The INDEX- MATCH functions can do what you want.
This will lookup the value in A4 in Order!B:B and return the value from Order!A:A
=IF($A4="","",INDEX(Order!A:A,MATCH(Invoice!A4,Order!B:B,0)))
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
the formulas are made so that once a QTY is entered in "ORDER" the entire row is copied over to "INVOICE". On both sheets I want the QTY to be in COLUMN A while still allowing the rows to be copied over. I can move QTY to column "A" in "INVOICE" without disrupting anything. If i move it in "ORDER" it disrupts all the formulas in all columns in "INVOICE".
the INDEX didn't work
See attached.
THANKS SO MUCH!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks