Transfer data in cells from one tab to another tab

[Robbyvictoryop]

Ok first if I do not use the right words in asking for help I am very sorry. I am trying to learn excel on my own and I am not sure what everything is called. Also I have NO clue on what you call what I am trying to do. So the title may not match what I am trying to do. All I know is I want to bring over data from one tab to another.

I have a new spreadsheet that I made to give us the break down of each invoice we create. what we want is to see each job that is done (by code), the amount we charge per each, the cost of goods per each, and the profit margin. In this spreadsheet I have two tabs. in tab one it has the form I made that has a place to input the data that will change with each invoice as well as a section that is broken down into columns. In these columns I have it listed this way. CA=Job code, CB=Job Description, CC=how many, CD=Charge ea., CE=rate total, CF=COGS ea., CG= COGS total, CH=profit

I have the formula done for all of the math the get the totals but what I need help with is to make it quick and simple. My idea was to make a second tab. In tab 2 I have everything listed in columns that will go into tab one. It is broken down like this. CA=Job Code list, CB=Job Description list, CC= charge ea. CD= COGS ea.

What I want to be able to do is when I type in the job code in the first cell under the column A on tab one, it will bring over all of the info from tab 2 and put it in the right spot for that code. So if I typed in say TT001 in that cell it would bring over the description matching that code and put it in the cell under B, along with the charge in column D, and the COGS under column F. And I want it to be that way for each code I type in and it will be different each time I fill this out. How do I explain this part? Ok so I a new invoice made its # 22. I take this spread sheet and do what I want and get all the totals so I print it out. I then want to be able to clear all the info and or just type in a new set of codes that will be on invoice # 23 and then #24 etc.

So what I am saying is the codes will be different each time I fill it out. that's why I have them listed on a different tab and will just put them in tab one long enough to get the total and print it out. Is that clear as mud? I will stop here and wait and see what questions I get back. thank you!!!! Ok I will just add the attachment to this so you can see what I am trying to do.

[Robbyvictoryop]

new title made. thank you!

[mmanning]

I was looking at this and it looks like you don't have enough cells in the Margin Tab, between row 15 and 35, you have 253 Items, my only thought would be to delete the formula for Total Charges and Total Profit and move them below the area your Item List is going to take up, and of course adjust your formulas to reflect the area that needs solved for, let me know if I missed somthing.

[Robbyvictoryop]

I saw in a youtube video where you can like click in one cell in the margin tab and hit I think = and then click a cell in the other list tab and click enter or maybe something else and click enter and it would bring that data over to that cell. This is sort of what I want however I need it to be tied to the item code and bring over all of the data from the list tab and put it in the right spot. I am starting to think that this is not something excel can do. or I am making it harder then it has to be. I have also saw something about a pivot table. is that how you do it?

[Robbyvictoryop]

I had a feeling that I would not be able to explain this right. I don't want them to show in the Margin tab all the time. I want to keep this sheet clean and enter all the new data each time we do a new invoice. I would like to enter in the item code onto the Margin tab sheet under that column and have it pull over the info from the list on the other tab. was that any help? We want to print on of these out for each invoice for our copy. The invoice gets mailed out and this margin sheet gets filled out and printed and kept for our records. I know that I could list everything and then just ad a number and have it do the math but then it becomes a huge form. Not sure I have helped you understand or not.

[newdoverman]

I think that this is what you are looking for. The column for description was formatted as text. This had to be changed to General so that formulae would work. I also took a guess that the Rate column was the Charge column on the Item List worksheet.

[Robbyvictoryop]

Mr. Newdoverman, First thank you! I think that has it. I tried it and it seemed to be working just fine. I was wondering if you could explain to me how you did it and then how I can save it all just in case I screw something up or make a change to it. And last if we add new items in the item list do I have to do that in any certain way?

[newdoverman]

There are only 3 functions being used as you can see. The important ones are INDEX and MATCH.

INDEX: returns a value at the intersection of rows and columns. In your file, you can see that the INDEX is looking at ranges on the Item List worksheet. The arguments required are the ARRAY or range of cells from which you want to get a value, ROW and COLUMN this will give an intersection point to identify a value to be returned.

MATCH: looks for a value within an ARRAY (range of cells) that matches a value that you specify. A match type is required. A match type of 0 looks for an exact match -1 finds a match that is the smallest value greater than the value being looked up. 1 finds a match that is the largest value that is less than or equal to the lookup value.

The MATCH function used with INDEX in the following formulae is looking to find the ROW where the match will occur and the 0 means that the match must be exact.

The IFERROR is included to take care of errors. In this case a blank cell will be returned.


Description:
=IFERROR(INDEX('Item List'!$C$3:$C$268,MATCH(A16,'Item List'!$B$3:$B$268,0)),"")

Rate:
=IFERROR(INDEX('Item List'!$E$3:$E$268,MATCH(A16,'Item List'!$B$3:$B$268,0)),"$0.00")

COGS:
=IFERROR(INDEX('Item List'!$D$3:$D$268,MATCH(A16,'Item List'!$B$3:$B$268,0)),"$0.00")

The ranges covered by the formulae above are from row 3 to row 268. Before implementing your "working version" of this workbook, increase the range values to something that will not be reached for quite a while like row 3 to maybe something like row 5000. Leave the 3 alone and change the 268 to an appropriate value for expansion. Be careful to keep the $ signs as they are.

You have named ranges in your workbook. You can use them in the formulae like this =IFERROR(INDEX(DESCRIPTON,MATCH(A16,ITEMLIST,0)),"")

You are only missing a named range for CHARGE. Create a named list for CHARGE just like the other named ranges and plug it into the formula for RATE.

The formulae using names would be:

=IFERROR(INDEX(DESCRIPTON,MATCH(A16,ITEMLIST,0)),"")

=IFERROR(INDEX(CHARGE,MATCH(A16,ITEMLIST,0)),"$0.00")

=IFERROR(INDEX(COGS,MATCH(A16,ITEMLIST,0)),"$0.00")

If you use the formulae with the named ranges then you won't have to worry about expansion as the named ranges cover the whole columns.



In case of disaster, make a copy of both the example workbook and the actual workbook BEFORE you enter real data.