need help for excel!!!

S

sanan

New Member
well this is the deal
there are 2 sheets in one excel file...
first one called *price before*...the other called *price now*

they look like this:-

sheet-1-price before

product name..(some unimportant things here)....price


sheet-2-price now

(same as sheet 1).......difference


*difference* should do the following:-
1- find the name of the product and it's price (in sheet 1)(product names in 2 sheets should match)
2- substract the price *before* from *now*
3-if the answer at point 2 is lower than zero...it should say "cheaper"...and if higher "more expensive"

I need help to make the formel for *difference*
anyone who knows how this formel should look like?
 
Last edited:
I set up a sample workbook with your descriptions and in the "difference" column I used the following formula:
=B2-[sheet1]before!B2
This means: take B2 from the current sheet and from that subtract B2 from another sheet called "before".

Here is some info on my set up so the formula above makes sense.
My two sheets are called "before" and "now".
A contains product names
B corresponds to the column with prices
C is the column with the difference
D has the text "cheaper, expensive, no change"

Row 1 has the titles for the categories
Starting with row 2 are the data

Here is a tip for it all go smoother. When you type in the formula in C2 of sheet "now", type =B2-sheet1!B2 and press enter.
Excel realizes you are referencing to another sheet and opens up a window for you to find the workbook where that sheet is located.
In your case, just find the workbook that you are working on and click open.
  • A window pops up with the available sheets in the selected workbook.
  • In my example I selected the "before" sheet.
  • Now the difference in price appears in C2
And now for the finale....
Ok, now let's say there are a total of 10 products listed (last cell would be C11 in this case).
  • You would first select C2, then hold down Shift and click on C11 to select that range in between.
  • Now press Ctrl.+D in order to carry out the formula from the first cell selected (C2) all the way to the last cell selected (C11).
  • Once you do this, the same scenario occurs with the pop up window. The good thing is that you only have to follow the steps once and it will all apply it to the selected range C2 through C11.
This might seem long-winded, but it really is fast and easy to do. I'll upload my Excel file in a minute so you can see it there.

UPDATE: Here is the xls file I did. http://www.savefile.com/files/468284
 
Last edited:
Hm, I thought you had it set up so that the list of products was identical in both sheets. If the lists are not identical, then - yeah, it won't work the way I told you earlier. Is it possible for you to get those lists identical? If not, I'll have to think about the best way to handle the data. Let me know.
 
PC Hobbyist said:
Hm, I thought you had it set up so that the list of products was identical in both sheets. If the lists are not identical, then - yeah, it won't work the way I told you earlier. Is it possible for you to get those lists identical? If not, I'll have to think about the best way to handle the data. Let me know.
Click to expand...
I use to copy/paste product lists into excel...and they're not at the same order whole time...
 
sanan said:
I use to copy/paste product lists into excel...and they're not at the same order whole time...
Click to expand...


So, you copied and pasted the list from one sheet to the other? That should make the lists identical unless you sorted one of the lists alphabetically after pasting. Can you get the two lists identical?
 
PC Hobbyist said:
So, you copied and pasted the list from one sheet to the other? That should make the lists identical unless you sorted one of the lists alphabetically after pasting. Can you get the two lists identical?
Click to expand...

I use this list to track products and their prices...I have no problem sorting those.. but I thought "if there is a finding and matching formula (which I know there is),it would be easier..and I don't need to sort whole the list everytime I paste a new list.
 
sanan said:
I use this list to track products and their prices...I have no problem sorting those.. but I thought "if there is a finding and matching formula (which I know there is),it would be easier..and I don't need to sort whole the list everytime I paste a new list.
Click to expand...


Hm, I see what you mean, but unfortunately I don't know how to help you on that. However, try this newsgroup from microsoft. I have had a lot of specific questions answered there by MS MVP people. Of course it's all free. The following link is specifically for excel questions. Good luck, and when you figure it out, it would be great if you post the answer back here so we can all learn.

Here is the link to the online newsgroup for excel.
 
You must log in or register to reply here.
Back
Top