FIFO-LIFO UDF function not working if DataRange are apart from each other

Hi
I found the FIFO-LIFO UDF Function code incorporated into the attached workbook on this forum here: http://www.mrexcel.com/forum/excel-q...tion-help.html . It was written by someone referred to as “al_b_cnu”. I must say it is one of the best FIFO-LIFO function I’ve evaluated and can adapt for my use since you can use it in a Trading Journal for buying/selling Stocks and Mutual Funds alongside (see Column H on Sheet2) each sale transaction.

I’ve shown the original structure and use of the FIFO-LIFO function in Sheet1 of the attached Workbook. On Sheet2, I’ve shown my adaptation in a slightly different structure; but limited to how the function works now. The function works as follows:

=FIFO("A",100,B2:C10) produces, FIFO(A,100,B2:C10); 10 @ $10; 40 @ $11; 50 @ $12; Total=$1140 as the result for FIFO
Where “A” is the Product, 100 is the quantity sold, and B2:C10 is the range in which the Product and Cost are listed.

=LIFO("A",100,B2:C10) produces, LIFO(A,100,B2:C10); 80 @ $12; 20 @ $11; Total=$1180 as the result for LIFO

NOTE: The parameters for both the FIFO and LIFO function can be cell referenced. I’ve shown cell references in Sheet2.

My ask is as follow in order of priority:

1. Firstly and ideally, I’d like to use the structure shown in Sheet3 (..this structure represents a typical journal/log of trading transactions) where I can use the FIFO and LIFO functions in Column N under the heading, “Cost of Shares Sold (FIFO/LIFO)”. Note: the original structure (Sheet1) requires that “Product” and “Unit Bought” are two Columns next to each other. In Sheet3, I’m showing “Product” or “Ticker Symbol” in Column E and “Unit Bought” or “No. Of Shares Purchased” in Column K. These two columns can be variable, meaning, there can be any two columns; but if not possible, Column E and Colum K are OK with me.
2. Secondly, as an option in the call to the function, I’d like the “Total” cost of shares to be listed as “FIFO Total = $1140” as an example; or “LIFO Total = $1180” as examples. Both without the quotes. So, in the case of the original call to the function, we could have =FIFO("A",100,B2:C10,”Spellout”) to produce, FIFO(A,100,B2:C10); 10 @ $10; 40 @ $11; 50 @ $12; Total=$1140 and, =FIFO("A",100,B2:C10,”Total Only”) to produce, FIFO Total = $1140. The same for the LIFO function. AND, =LIFO("A",100,B2:C10, Spellout) to produce, LIFO(A,100,B2:C10); 80 @ $12; 20 @ $11; Total=$1180 and, =LIFO("A",100,B2:C10, Total Only) to produce LIFO Total = 1180

Can someone help me with request 1 and 2 above? I would really appreciate it and I think a “thousands & more” people will as well since it will be an excellent/useful UDF function presented by this forum.

Please make changes to the workbook UDF code as needed and re-post so I can download it to obtain the code for testing and use. Any questions, please let me know.

Many thanks.

Cheers!

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)