I have page 1 that has a list of products in column A and in columns X,Y,Z,AA I have transfers to and from 2 different locations. on page 2 I have a dropdown in column A of the products from page 1. in column B I have a drop down with to and from the two locations. column C is the quantity. how do I get the qty from page two to transfer to the proper cell on page 1 after using the drop downs on page two

Attached Images

Page 1.jpg (112.3 KB, 19 views)

Take a look at the attached.

Attached Files

ProductName.xlsx (10.6 KB, 13 views)

Sorry I wasn't clear. The to and from column on page 2 is also a drop down

Please upload an Excel file with your data and drop downs.

I would like for each entry on the second sheet to transfer to the appropriate to and from column on the first sheet

Attached Files

Transfer test.xlsx (27.5 KB, 9 views)

This seems to me to be quite different from what you asked in your original post (many non-contiguous ranges).

First of all I find the design of sheet Transfers to be rather inappropriate from a formula building perspective. Do you absolutely want to stick to that design or would you be prepared to consider another (database) design?

Furthermore, if I shall go on, please upload a new file in which you make some entries and show manually the expected results in the other sheet.

I do not have a problem with changing the design. on the July sheet the product name has to stay where it is but that is all that must remain. As long as all of the info is there I'm open to any suggestions. Also I do need 6 transfer sheets per location. Any help you can give me is much appreciated

Attached Files

Transfer test.xlsx (28.5 KB, 15 views)

My apologies but I want to withdraw from this thread as I don't have the time to propose another set-up.

I completely understand. Thanks for the insight you have given me. I may rethink the entire setup

On second thought I find it somewhat unfair to back out of a thread I have already involved myself in, so I decided that I will try to help you out in spite of what I wrote in #8.

Questions/comments

1. Am I right that you enter your data into Sheet Transfers, and that you want formulas to complete sheet July 17 (just to be completely sure).

2. When you name a sheet July 17 does that mean that you have/want one such sheet per day.

3. Please tell me detailed about sheet Transfers (for example):
B1:E29 - is that to be understood as one invoice.
Shoudn't there be entered a date.
Should it be possible to distinguish between Admiral Transfer 1, Admiral Transfer 2 etc.
Would it be acceptable to have all info in B2:E(x) that is in one contiguous range instead of in 18 non-contiguous ranges maybe with extra columns for invoice number, (date perhaps) and the present text in the yellow areas.

Furthermore - regarding the last file you uploaded I understand all the numbers in sheet July 17 from B3 and diagonally down to M14, but I don't understand from where you get the numbers above and below that diagonal. (For example B8, I don't see AYYY TONNNY!!! To Nelson (only from Admiral).

The better you explain the greater the chance that I will be able to help you.

1. That is correct
2. No I will have one for July and one for December and that will complete my year
3. That would be ok to have an additional column for invoice number and make all of admiral one contiguous range


your example of B8 AYYY TONNNY!!! if you look on transfer sheet J5 shows 6 from Admiral but since it is on Nelsons sheet that shows that it went to Nelson. I've thought about it some more and I think we can eliminate the choices of "from" in the drop down menus. the reason being is that the location that is highlighted in yellow at the top of each transfer is the location that it is coming from therefore there is no reason to have a choice of "from" in the dropdown.


My overall goal is to fill out the transfer sheet as items are transferred and which location they are being transferred to and from and in the end have a total of how many of which item went to which location and from which location in order for me to better maintain my inventory.


I really appreciate you giving this another try and I also appreciate all of the input you have given me. its really helping me to see what information I really need and don't need. Any way that it can be simplified and still have the same ending result would be fine by me. Let me know if you have any more questions.


Thanks again

Yes I have some more questions:

1. Do you want one Transfers sheet and one product overview sheet for each six-month period or only one of each sheet for a full year.

2. Do you want to have data/report for more than one year in the same file.

3. B1:E29 in sheet Transfers - is that to be understood as one invoice. Similar question for ranges starting at B32, B63, B94, B125 etc. up to the one starting at N156.

4. You wrote:
That would be ok to have an additional column for invoice number and make all of admiral one contiguous range.

What about having all invoices (up to CHURCH TRANSFER 6) in one contiguous range?
Can you accept having 'TO' in one column (as for example column C) and 'FROM' in the column just to the right instead of having just one 'FROM' as heading?

5. Is invoice date irrelevant (in this context)?

More questions may appear later, but if you answer the above I will be able to start.

I appreciate the effort but I think with everything that I need done it would be easier and less time consuming to go with an inventory control program. Thanks again