Solved: Adding features to an excel worksheet

[angelikv]

Hi guys!!!
The following are some of the difficulties that I am having working on excel:

1. I am trying to transfer the information recorded on ?Report? to ?Last Year Sales? in an easier way than connecting cell by cell (as I did on ?Last Year Sales ? PIZZA?).
The purpose of recording that information is to compare last year?s sales to the sales from 2007.

2. Also, few weeks ago I was working on a macro that would record the information from entered under Sales on ?Receipt? and I am trying to have a smiling face if the sales from 2007 are greater than 2006.
Using the function if(b4>100, ?.and so on) will work, but I don?t know how to identify when to put a smiling/sad face based on the date, and name on the stand.

I?m sorry guys if I am not explicit enough, but is very hard to explain . Please, let me know if you have more questions and I will appreciate all your help. Thank you so much for your time.

Angelica

Ps. Please see attachment for a better understanding.

[XLGibbs]

Sounds interesing. Let me take a peek at the sample file...and back in a few with some input or an answer!

[XLGibbs]

In the LastYearSales tab, CELL D5, place this Formula. Exactly this formula.

You can then Copy and paste this formula down column D, G,J etc as I have done on the attached. I threw some sales figures in to test...



[vba]
=OFFSET(Report!$A$8,MATCH(C$1,Report!$A$8:$A$41,0)-1,ROW())
[/vba]
Note that for Pizza I changed it to this:

[vba]
=OFFSET(Report!$A$8,MATCH(L$1,Report!$A$8:$A$41,0)-1,ROW())+OFFSET(Report!$A$8,MATCH(M$1,Report!$A$8:$A$41,0)-1,ROW())

[/vba]
to account for both types of Pizza names, and in L1 and M1 of LastYearSales I changed the heading to N. Pizza in L1 and S. Pizza in M1

Sink your teeth onthat for a bit, then we can discuss the smiley face bit

For the formula to work, the values in Column A of the Report tab must be identical to the headers on the last years sales tab. SO if you get an #N/A, that would be the reason ...

let me know if this helps.

[angelikv]

Thank you very much XLGibbs!!!
I made the changes that you suggested and it works!! . Please let me know if you have an idea about the faces.
Thank you again

Ps. See the attachment with the changes.

[Bob Phillips]

Just set the font in F19 to Windings, and add a formula of the like

=IF(sales_2007>sales_2006,"J","L")

[XLGibbs]

Just set the font in F19 to Windings, and add a formula of the like

=IF(sales_2007>sales_2006,"J","L")

yeah, what he said

Thank you very much XLGibbs!!!

You are very welcome.

[angelikv]

Hi guys!
I copied the formula but it gives me an error, I believe that it doesn't recognize the name of the stand and the date. The face should appear only for those stands which I have the data from last year (LastYearSales) based on the correct date and name. Please let me know if I am doing something wrong. Thanks again!!!

[XLGibbs]

Hi guys!
I copied the formula but it gives me an error, I believe that it doesn't recognize the name of the stand and the date. The face should appear only for those stands which I have the data from last year (LastYearSales) based on the correct date and name. Please let me know if I am doing something wrong. Thanks again!!!

Where are you putting the formula ...I assume you mean the sample format XLD provided..

You would have to replace with correct cell references..

As I recall, you had the tab last years sales which had them side by side..

SO it would be something like =IF(LastYearSales!$D8>LastYearSales!$E8,"J","L")

Assuming columns D was 2007 and Column E was 2006

If you are trying to do matching/lookup using date and names like in my original formula (or a modified version of it)..that would be slightly different.

If you upload another attachment with a sample result...I will take a look for you....besure to highlight your example/sample formula in bright pink background or something so I can see it clearly.

[vzachin]

hi,

this is off topic, but can someone explain how the calendar was created?

trying to learn
thanks
zach

[Bob Phillips]

A Pop-up Calendar for Excel http://www.fontstuff.com/vba/vbatut07.htm

[angelikv]

The face is not recognizing the name nor the date from the receipt. I guess I am trying to do the Vlookup or something similar. I have change the color of the table from where I get all the information. Also, I highlight the cell where the face should recognize if sales are greater than 2006 (smiling face) or if they are less (sad face) based on the name and date.

On LastYearSale I have made a column for the percent change that might be helpful because if is positive it means the sales were greater or negative the sales were less than 2006.

I hope you understand what I am trying to accomplish here, is hard to explain but it seems that you guys follow what I am saying. THANK YOU!!!!

Ps. Sample has been attached

[Bob Phillips]

You are comparing the 2007 value against the percentage change. And why row 8.

What exactly do you want compared here?

[XLGibbs]

I see exactly what she is trying to do on the receipt tab....It wasn't what I thought she meant to do from the earlier description. I will post a solution for her shortly...

[XLGibbs]

The face is not recognizing the name nor the date from the receipt. I guess I am trying to do the Vlookup or something similar. I have change the color of the table from where I get all the information. Also, I highlight the cell where the face should recognize if sales are greater than 2006 (smiling face) or if they are less (sad face) based on the name and date.

On LastYearSale I have made a column for the percent change that might be helpful because if is positive it means the sales were greater or negative the sales were less than 2006.

I hope you understand what I am trying to accomplish here, is hard to explain but it seems that you guys follow what I am saying. THANK YOU!!!!

Ps. Sample has been attached

The formula xld and I provided was essential a comparison of one cell to another...it had no lookup or matching options inside it...as above, I see from your example what your intent is...will post back shortly with a solution for you.

[JimmyTheHand]

I wonder if this formula in F19 would do

 =IF(OFFSET(LastYearSales!$A$1,MATCH($D$4,LastYearSales!A:A,1)-1,MATCH($E$6,LastYearSales!1:1,0)+1,1,1)>=0,"J","L")

[angelikv]

Hi Jimmy,
I tried your formula but it gives me an erros.

[XLGibbs]

Hi Jimmy,
I tried your formula but it gives me an erros.

The formula works, but if there is no sales data yet, you will get an #N/A which looks like scissors.

This worked for me, adjusting the column A:A to be A5:A56

[VBA]
=IF(OFFSET(LastYearSales!$A$1,MATCH($D$4,LastYearSales!A5:A56,1)-1,MATCH($E$6,LastYearSales!1:1,0)+1)>=0,"J","K")
[/VBA]

Make sure you have values, and make sure the date in D4 on the receipt tab is available in the list of dates in column A of the LastYearSales tab.

[angelikv]

I'm probably doing something wrong because I only see the scissors even after inserting the values and pressing print receipt.
Can you please upload the file with the formula in it?
Thank you all for your help!!

[XLGibbs]

[vba]=IF(OFFSET(LastYearSales!$A$1,MATCH(DATEVALUE($D$4),LastYearSales!$A$5:$A$5 6,1)-1,MATCH($E$6,LastYearSales!1:1,0)+1)>=0,"J","L")
[/vba]
Looks like it doesn't like the date in $D$4 of Receipts. It was entered as text. Wrapping $D$4 inside DateValue() like above, it resolves the error.

Attached...

Also, make sure you remove all the "dead" Defined Range names as I have done in the attached.

Go to Insert>Name>Define and review the range names. Most have #REF errors because wherever they were pointed to has been removed...

EDIT:It appears my attachment here didn't work. Thanks for pointing that out JamestheHand...i didn't realize it was too big! LOL

Subsequent post with corrections anyway..


Also, it appears there are links to the Comparisons06 file still in there somewhere..you probably want to replace those with values (Copy/PasteSpecial Values) to remove the link...

[angelikv]

Ups, I still have problems with it. Now I just get a sad face and it doesn't change when the amount from 2007 is greater than 2006.
Can you send me the attachment to see if it works for you?

Thanks you!