I have created an excel invoice that uses vlookup to the orders page. The item number references to the receipt number which references to the first value over 1. I have this formula figured out. What I am having problems with is the second value because it has to find the second value greater than 1. For me this is a huge and complicated formula and I was wondering if if can be done easier. This will also get more complicated when it needs to find the third or fourth positive value.
can you upload a sample workbook because the formulas arent visible correctly
Here is my workbook all of the numbers and names are for test purposes only
.jpg images dont display the formulas.
u need to upload it via excel
Here is my file
the formula contains 23 conditions.they can be broken down and 23 columns can be made.the formula is pretty straight forward
regards
the first formula is simple but I don't know how to create the second formula in the list in the cell C21-C27 and if there are no more values I don't want to see null I want to see a blank cell
the value is never going to change in column C as the formula is started with a $
so did I create the formula wrong? Do you understand what I am trying to formulate? I want each column to represent another purchaced product from the order sheet
each row is what u mean?
each row to represent each colum from order sheet?
each row in the invoice represents a positive column in the orders sheet on the same invoice id
please describe what u want to achieve in Colum C and from where.Be very brief and very clear.I ll try my best to make a new formula.If within my reach
I want column C to search for invoice ID and find it in orders then I want it to find first order number that is 1 or higher. This 1 or higher indicates the number of the above product ordered. It then soruces the name of the item in this location.
I have no idea what it is you are trying to accomplish here... you have hidden values in Row of of Orders.... you have megaformulas, hard coding and other mixed formula in column B.. and you want column C to look for what? And Where?....
Maybe you should start with a blank slate, and fill in all expected values manually indicating where the items come from and why?
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
So I want to be able to fill out the order form with the number of each item purchased. I then want to be able to insert the invoice ID and get all of the orders to line up with the data in the order sheet. Example. Joe orders 1 C D and 2 paper cases. I want to fill out a 1 under the C D value and a 2 under the paper cases. I then want to type the invoice ID into the invoice and get a list of all of the purchased products, the quantity, and the charge. Does anybody understand what I want to do and if it is possiable. If it is not possiable does anybody else have a different format that would work better? Thanks in advance.
Last edited by Log010; 07-23-2011 at 11:10 PM.
Why are there repeated headers in row 2 of the Orders sheet?
Where do the numbers in column A (Quantity) of the Sales Receipt page come from?
Currently the Quantity is manual but I would like to code that in as well. The customer ID and Invoice ID are currently the same because I have not had a returning customer yet if I did then it would not be the same number.
But where are the quantities supposed to come from.?
And why are the headers in Row 2 of Orders Sheet repeated?
They are different headers Invoice ID is unique to every order Customer ID is unique to every customer Invoice ID never repeats where Customer ID can repeat multiple times with every new order. This customer ID is then linked with another sheet with the customer's name and address
OK, i have read this thread and am also confused. I am guessing that you are trying to use your orders sheet as a database of all sales you have made to customers. Hence every sale as a unique Invoice ID. A customer can make more than one transaction so customer ID can be repeated.
I am pretty sure that the question NBVC was asking is why do you have fields in your header (Row 2 - the horizontal line #2) repeated. For example, you have "paper cases" in the header row 3 times...why?
So you could have in a single transaction somebody buy 3 "paper cases" and 5 "paper cases" and another 2 "paper cases"?? Wouldn't that then be 10 "paper cases"?
I think this needs to be explained first before this can progress because it makes no sense
....
What is sheet 1? I assume this is a database of the items that you sell and the costs from your suppliers? and subsequent profit/loss calcs?
Last edited by Cookstein2; 07-26-2011 at 05:46 AM.
is this anything close to what you want?
btw, if any body can figure a way of not displaying the #NUM! error in column B on sheet ("Sales Receipt") then go ahead. I can't do it.
I think it's because it exceeds the nested functions limit if you add an if(iserror(),,) statement in there as well as the rest of it....
...also, i havent done anything with your discounts
Last edited by Cookstein2; 07-26-2011 at 07:33 AM.
I have moved this problem to
http://www.excelforum.com/excel-work...ml#post2572968
Thanks for all of the help so far
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks