Hello, I am not great with excel I have only started getting better since i have gotten my new job. I've tried looking around for this sort of thing but have not been able to find a suitable answer for my situation. Also if you can be as plain and obvious in your answer as possible 'cause i have found some answers around to other questions i have eventually managed to solve that have confused me as they've been aimed at someone who knows more what theyre talking about.
Also i cannot be extremely specific as to what i want it for as it's for work and i don't want to break DPA etc.
Basically, the company recieves orders from 2 different companies each month both making offers to what they will pay for our items. This data is recorded in two different sheets, one for each company like so. I'll seperate the cells with comma's as the formatting when i submit this might change. Treat Product name as A1, Oct as B1, "a" as A2 etc
Product name, Oct, Nov, Dec, . . . . . . . ,Average
a, 12, 12, 12,..., 12
b 0, 6, 0,....,2
c 0, 0, 3,....1
.
.
z 12 ,13, 11, ...., 12
The other sheet for the other company works exactly the same.
These sheets are sheet 2 and sheet 3
Sheet 1:
Sheet 1 compares the two prices of the last entry in both sheet 2 and 3. So the last entry in a is 12, in b is 0, in c is 3 etc. The average column is not required here it is simply for the other sheets.
Then it picks the highest one and says what it is. So eg. Company 1 is 12 for product a and company 2 might say 12.10. Thus if we have stock we'll sell it to company 2. Therefore for this data set sheet 1 looks like this. (you will see sheet 3's data from this for the bit we need it for)
1
Product Name, Stock, Company 1 Price, Company 2 Price, BEST PRICE, PRICE, Total Price.)
2:a, 5, 12, 12.10, Company 2, 12.10, 60.50
3:b, 2, 0, 0, Neither, 0, 0
4:c, 4, 3, 3, Either, 3, 12
.
.
.
27:z, 2, 11, 10, Company 1, 11, 22
Also if a company want it 99% of the time theyll take all we have so this isnt an issue and if not we can adjust that one without a headache.
Now in order for the spreadsheet to work Column A must match in all 3 sheets. That is that A2 on sheet 1 is the same as A2 on sheets 2 and 3 so that it can pull the prices from them to sheet 1. This works.
The problem comes when i want to add a new item. So say Company 1 ask for product aa then i'd add it at the bottom of each sheet, fill in the data on sheets 2 and 3 and the stock on sheet 1 (if we have it otherwise i'd still do it and put 0), and the rest on sheet 1 is done by formulae automatically.
But now i want to put it in alphebetical order. So i highlight top right to bottom left all the data in all sheets and alphebeticise it using excels sort ascending button. But this copys the formula as it moves aa and so the formula has to be changed.
Alternatively, if we choose to insert a cell into the location, the formulas dont exist as its a new row and they have to all be copied down again.
This isn't so bad, but i'm not the only one who will use this spreadsheet and so it's complicated explaining that to people and if someone forgets it will mess all the data up.
So what i want is to add the product name to the bottom of sheets 1,2,3 and then for it to automatically put itself in the right place and alter the formulas to fit in its new place. Or a "click a button" to make it happen or something.
I don't know if i'm missing something and this is really easy or what but like i said i'm no excel genius.
Appreciate any help, hope i explained everything fully.
Also i cannot be extremely specific as to what i want it for as it's for work and i don't want to break DPA etc.
Basically, the company recieves orders from 2 different companies each month both making offers to what they will pay for our items. This data is recorded in two different sheets, one for each company like so. I'll seperate the cells with comma's as the formatting when i submit this might change. Treat Product name as A1, Oct as B1, "a" as A2 etc
Product name, Oct, Nov, Dec, . . . . . . . ,Average
a, 12, 12, 12,..., 12
b 0, 6, 0,....,2
c 0, 0, 3,....1
.
.
z 12 ,13, 11, ...., 12
The other sheet for the other company works exactly the same.
These sheets are sheet 2 and sheet 3
Sheet 1:
Sheet 1 compares the two prices of the last entry in both sheet 2 and 3. So the last entry in a is 12, in b is 0, in c is 3 etc. The average column is not required here it is simply for the other sheets.
Then it picks the highest one and says what it is. So eg. Company 1 is 12 for product a and company 2 might say 12.10. Thus if we have stock we'll sell it to company 2. Therefore for this data set sheet 1 looks like this. (you will see sheet 3's data from this for the bit we need it for)
1
Product Name, Stock, Company 1 Price, Company 2 Price, BEST PRICE, PRICE, Total Price.)2:a, 5, 12, 12.10, Company 2, 12.10, 60.50
3:b, 2, 0, 0, Neither, 0, 0
4:c, 4, 3, 3, Either, 3, 12
.
.
.
27:z, 2, 11, 10, Company 1, 11, 22
Also if a company want it 99% of the time theyll take all we have so this isnt an issue and if not we can adjust that one without a headache.
Now in order for the spreadsheet to work Column A must match in all 3 sheets. That is that A2 on sheet 1 is the same as A2 on sheets 2 and 3 so that it can pull the prices from them to sheet 1. This works.
The problem comes when i want to add a new item. So say Company 1 ask for product aa then i'd add it at the bottom of each sheet, fill in the data on sheets 2 and 3 and the stock on sheet 1 (if we have it otherwise i'd still do it and put 0), and the rest on sheet 1 is done by formulae automatically.
But now i want to put it in alphebetical order. So i highlight top right to bottom left all the data in all sheets and alphebeticise it using excels sort ascending button. But this copys the formula as it moves aa and so the formula has to be changed.
Alternatively, if we choose to insert a cell into the location, the formulas dont exist as its a new row and they have to all be copied down again.
This isn't so bad, but i'm not the only one who will use this spreadsheet and so it's complicated explaining that to people and if someone forgets it will mess all the data up.
So what i want is to add the product name to the bottom of sheets 1,2,3 and then for it to automatically put itself in the right place and alter the formulas to fit in its new place. Or a "click a button" to make it happen or something.
I don't know if i'm missing something and this is really easy or what but like i said i'm no excel genius.
Appreciate any help, hope i explained everything fully.
