Detailed set of formulas based on imput from one sheet 1 and data from another

This is not as complicated as it sounds but I am trying to be as specific as possible


On "calculator" sheet I am selecting the client from a drop down in G8 and Printed Matter from a drop down in N9.

Based on these 2 selections I want to populate one of the 2 groups on the calculator sheet (E14,E15,F15,E22) (I14,I15,J15,I22) (M14,M15,N15,M22) from the data on the Input Summary sheet.

Which of the 3 groups gets populated will be based on the total going into E14, I14, M14 all being CF. If the CF is = to 0-10 then all totals go to the first group, If the CF is = to 11-50 then all totals go to the second group, If the CF is + to 51+ then all totals go to the third group.

Where G8 and N9 on the calculator match Col A and Col I on Input summary then the following will happen.

The first associated DR will be posted to C12
The next associated DR will be posted to the next open cell E10, G10, J10, M10, C11, E11, G11, J11, M11

The total associated "CF" (in the sample =4) will post to E14 (or I14, M14)

The same will be true for BX, Total Value and Customs Duty

I am hoping someone can have a look at this for me and help me work through a solution

Would someone be able to look at this and if nothing else advise me if what I am asking for can be done and if so what would be a reasonable time frame of work would it be?

Hi mikerules

As a concept, I am sure that it can be done.

There are several issues preventing me from understanding exactly what is required.

" one of the 2 groups " you then go on to list 3 groups. Presumably you do mean "3 groups"

" Where G8 and N9 on the calculator match Col A and Col I on Input summary" under what circumstances would G8 and N9 not match the last entry on Cols A & I ?

"The first associated DR will be posted to C12 " do you mean C10 ? What is an " associated DR" ?

"The total associated CF (in the sample =4)" from where is this figure obtained?

I am sure if I get to understand more, there will be more questions!

Despite your "This is not as complicated as it sounds", I am now beginning to see why no one else has picked up this one!

I look forward to you comments. Feel free to add anything else that will aid my understanding.

Regards
Alastair

Hi Alastair

Point 1: Yes that was a typo it is 3 groups

Point 2: It is not the "last entry" The first thing we do in the process is complete line by line on the Input Summary and when we are
done one customer may have one two or more line entries. We need to combine the total entries in each column for that
customer but only if the name (A) and answer in (I) match the selection made in G8 and N9 on the calculator. For example if on
the calculator we choose "Jo Smith" and "Yes" then the totals in each column on the input sheet where those entries are
matching would be carried to the applicable cells on the calculator sheet.

Point 3: Yes C10 is correct then the second to C11 etc. By associated I mean the DR on the line beside the customer name on the input
sheet. So if we are choosing John Smith and No on the calculator it would look to the lines on the input summary that list John
Smith and NO and the DR numbers on those lines would be brought to the calculator. In the sample given there are two. They
just come over and populate the next open cell of the group. A DR is a customs tracking number specific to a parcel that was
accepted to the warehouse and a means to track the item through the process.

Point 4: This comes from column E on the input sheet. You can see on the calculator we have chosen for the example John Smith and
NO as printed matter. If you go back to the input form John Smith is listed 3 times but on twice with No in column I. With it
only 2 of the 3 lines it then looks to bring only the totals of those 2 lines over. In the case of CF the total 4 is coming from E18
and E20

If I can provide any more answers or samples please let me know and wow thank-you so so much for looking at it.

Mike

I thought some understanding of what this is used for may help. Customers order merchandise and we are a small business that clears product through customs for the orders.

If a product is printed matter there is no customs duty vs if it is not printed matter there is 22% duty. This is the reason we can't bring the duty "yes" and "no" totals together to the calculator as the calculator calculates the duty and other charges and the two have to be done as separate entries.

The other entries that are being brought across are just adding the totals of all the customer entries together so we can calculate total charges for the customer in one entry. So if a customer orders 3 items in Amazon and ships them this would create three lines on the input sheet. We want to add the CF (cubic feet), total boxes value and duty and bring each to one total entry on the calculator. So 3 "no" printed matter pieces of clothing each with a value (col H) of 100.00 would then populate the value area (F15) as 300.00.

Hi Mike

I think that I have interpreted your requirements correctly. Let me know how you get on.

Regards
Alastair

Hi Alastair,

First off I want to thank you so very much for not only looking at this for me but taking it on. I am sure you have many things to do with your time and I truly appreciate your time on this.

The original sheet has been enhanced since the posting although it does not effect too much what you have done other than one cell. That being the name on the calculator sheet has moved from G8 to A8. From looking at the macro since it is still in col 8 I don't think the macro needs to change. Is this accurate?

The "total value" which is in Input Col H needs to come to either F15, J15, N15
The "customs duty" which is in Input Col O need to come to either E22, I22, M22

The name of the sheet "Ship$mart Caculator" has changed to "Calculator - Linzy Allen" as you can see. I can rename it back to the original but that presents a problem and I am wondering if what I want can be done. For each customer on the input summary we will need to make a calculator sheet and the names can't be duplicated and each one should be named for the customer for whom the calculations are made. From the standpoint of the macro can the name of this sheet be changed for each customer and still have the macro work? How can I handle this?

This is new: Originally I had asked that if the "Name" and the "yes or no" matched the appropriate details would be brought to the calculator. Can I have you add a 3rd factor and that is where "consolidation" on the input calculator in column AA "yes" or "no" also matches the calculator cell F9 dropdown of yes or no.

I have attached a new copy of the sheet.

Hi Mike

Why waste time on easy, static problems? Much more challenging to hit a moving target!

I spotted the first trap easily "Imput Summary" (easy because it's one of my more common typos )

Not so much a trap as an inconvenience - protecting the sheet with a password.

I think that I have covered all of your points, but let me know if I have missed anything.

I have taken the opportunity to suppress the Div/0 messages by wrapping them in "Iferror" statements as in iferror({original formula],0).

Have fun trying it out.

Regards
Alastair

Hey Alastair,

You are so funny but you know I went to bed thinking "I unprotected that copy before I saved it right? Yup I did!!" Well I unprotected half of what u needed!!

I have copied the macro back to the original and I am getting an error. It does work on the copy you sent back but not on the original so either I made some change to the original in the mean time that is effecting it and of course I would never do that! or you have inserted a bug to challenge me and it's working!!

Can you check it out...new attachment

So Alastair,

With the last change not working I was wondering if that had anything to do with the sheet names? Am I able to name them at will?

I think back in the day the British Gov. made IMPUT a word by using it for one of their taxes. Now were all confused.

You like moving targets but can you hit them moving backwards?

So to change direction...literally. I need to move the totals back to the "INPUT" summary from each completed "Invoice"

Using the same criteria "Name" "Yes No" for printed matter and "Yes No" for consolidation. Where these match on the invoice and the input form then the following would happen with the click of a magic button:

The total populated in row 20 of the invoice would go to the appropriate row and column J of the summary
The total populated in row 21 of the invoice would go to the appropriate row and column L of the summary
The total populated in row 22 of the invoice would go to the appropriate row and column M of the summary
The total populated in row 28 of the invoice would go to the appropriate row and column P of the summary
The total populated in row 31 of the invoice would go to the appropriate row and column R of the summary
The total populated in row 51 of the invoice would go to the appropriate row and column W of the summary

Hi Mike

you have inserted a bug to challenge me and it's working

I do not have that sense of humour

Version 4 works fine as long as the sheet is renamed "Input Summary"

You can call the sheets anything you like. However, I have put in a check that makes the macro stop if the sheet does not begin with "Invoice -" (case sensitive). This is to prevent the macro being started when on the Input sheet (yes I know it's started from the button, but it can be started from the keyboard). I suggest that the names begin with "Invoice -".

The writing back can be done. However, you need to define "appropriate". If (say) there are 9 occurrences of the chosen item, on which line(s) should the data appear? 1st, last, or all ? Assuming it is either the 1st or last, what should appear on the other rows? (If nothing, then when you look at the sheet you will not know whether there is nothing to show, or whether the "magic button" has not been pressed)

Over to you

Alastair

Hi Alastair,

After I sent the last message I went into my copy and changed the spelling to Input but didn't try it at that point but yes it does work great.

On the other matter:

I guess Appropriate doesn't work well in a macro haha!

As an example and based on the first and second invoice and input attached and completed. Not every line will have an entry as they are grouped on the invoice so the totals coming back to the summary will be totals and probably would make most logic to put the entries on the first of the lines as I have shown. I don't know if its possible but if the other related row cells could be populated with a zero we would then know that the entry was added in another cell

Where on the Invoice the "Name" in A8 and the "Yes No" in F9 & N9 matches "A", "I" and "AA" on the input sheet then the following totals would populate.


The total populated in row 20 of the invoice would go to column J of the summary
The total populated in row 21 of the invoice would go to column L of the summary
The total populated in row 22 of the invoice would go to column M of the summary
The total populated in row 28 of the invoice would go to column P of the summary
The total populated in row 31 of the invoice would go to column R of the summary
The total populated in row 51 of the invoice would go to column W of the summary


I have attached an unlocked version and thanks again for your help

Mike

Hi Mike

I am going to take some time out on this. I am working 12 hours a day for the next 3 days and then I'm on holiday for a week. I may have a little time before the holiday, but during the holiday I have a 24/7 responsibility for a unruly tribe of small people (the oldest is 3 !) and have been banned from my computer.

The concept of writing to the summary sheet is quite straight forward, but putting in error traps (for if something should go wrong) will take the time.

Regards
Alastair

Hi Mike

I had some spare time - so managed to get the write-back completed. Let me know if it needs amending.

I needed to have no #Value! or #DIV/0s, so I put in a few Iferror statements (Ctrl+Shift+Q when on a cell that needs an iferror will do it).

I also took the opportunity to write a routine that looks at each line on the Input Summary and checks that a sheet has been opened for that customer. If no customer exists - it will open a sheet. Ctrl+Shift+N will do this.

Regards
Alastair

Hi Alastair,

Thank-you very much. You're amazing.

I need to clarify a few things to understand the functionality.

I tried the Ctrl Shift N and see that this adds the missing tabs. Within each of these new tabs there is content from one single customer. I am not sure how it is choosing this content? I assume we would just need to go into each sheet and delete the content and bring across the correct content. Is this correct? As I wasn't aware you could do this it begs the question. In the future could this be set up so that we could complete the basic info on the summary and then with a macro have the tabs created based on the commonalities of name and the yes no answers and then auto populate the details that we currently do by use of the macro on the invoice. Basically automate the whole setup of the customer invoices?

From what I can understand in order to populate the input summary this needs to be done from each invoice independently. Would this be correct? I have put a button on the invoice for the macro to populate the input summary however when I ctrl shift n and create the missing tabs this button doesn't populate to each of the new tabs. Can I change this?

I notice when I assigned the macro "post2summ" to the new button that there are several other new macros showing on the list Iferr, macro 2, macro 3, newcus. Will I have to use these? When I alt F11 I don't see them in the macro list so I was just wondering what there use was for?

Regards,

Mike

I needed to have no #Value! or #DIV/0s, so I put in a few Iferror statements (Ctrl+Shift+Q when on a cell that needs an iferror will do it).
Could you explain a bit further what you mean by this?

Hi Mike

Within each of these new tabs there is content from one single customer. I am not sure how it is choosing this content?

The Ctrl Shift N calls the NewCus routine. This goes through each Customer on the Input sheet and attempts to open the relevant sheet. If it fails, then it unhides the "Invoice - Template" sheet, copies it, renames the copy to the new Customer and hides the template sheet.

I assume we would just need to go into each sheet and delete the content and bring across the correct content

It depends on how the workbook is to be used. If you are going to keep it running for every day, then yes. The New Customer routine will deal with any newcomers. Alternatively you could start a new workbook each day (or whatever) and just use the New Customer routine.

in order to populate the input summary this needs to be done from each invoice independently. Would this be correct?

Yes

when I ctrl shift n and create the missing tabs this button doesn't populate to each of the new tabs. Can I change this?

Yes. You need to put this button on the "Invoice - Template" sheet. (Right click any tab and Unhide)

there are several other new macros showing on the list Iferr, macro 2, macro 3, newcus. Will I have to use these? When I alt F11 I don't see them

Iferr is to wrap a formula in an "iferror", so that it shows 0 rather than and error message. You will not nee to use it.
macro2 and macro3 were left there by mistake. If I am uncertain as to the exact syntax of a command, I record the maco and use the results.
newcus - discussed above.
Alt+F8 will bring up all the macros

I needed to have no #Value! or #DIV/0s, so I put in a few Iferror statements (Ctrl+Shift+Q when on a cell that needs an iferror will do it).

When setting the column based on the CF value, I tested the values in certain cells in columns E,I and M. If these had an error value (eg trying to divide something by 0) then I had to handle it. I could choose to handle it within the macro, or better still, deal with it at source and change the formula in the spreadsheet. I wrote the macro called iferr and set it to run with a Ctrl+Shift+Q keystroke combination (just coz I'm lazy). Any cell with an error that needed changing I would go on to it and press the aforementioned Ctrl+Shift+Q and the cell now reads 0

Let me know if anything else needs explaining.

Regards
Alastair

Hi Alastair,

It has been a month in use and although we need to continue adding new functionality it works perfectly and has saved us a mountain of time. Thank-you and I hope your vacation was great!

Mike

Hi Mike

Glad it works OK and yes the vacation was exhausting, but great

Reference your PM, it might be better to start a new thread (possibly with a link to this, if appropriate)

Regards
Alastair