PDA

View Full Version : Solved: Adding features to an excel worksheet



angelikv
12-27-2006, 06:59 PM
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. :friends:

Angelica

Ps. Please see attachment for a better understanding.

XLGibbs
12-27-2006, 07:18 PM
Sounds interesing. Let me take a peek at the sample file...and back in a few with some input or an answer!

XLGibbs
12-27-2006, 07:46 PM
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...




=OFFSET(Report!$A$8,MATCH(C$1,Report!$A$8:$A$41,0)-1,ROW())

Note that for Pizza I changed it to this:


=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())


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
12-28-2006, 12:18 AM
Thank you very much XLGibbs!!!
I made the changes that you suggested and it works!!:thumb . Please let me know if you have an idea about the faces.
Thank you again:yes

Ps. See the attachment with the changes.

Bob Phillips
12-28-2006, 02:57 AM
Just set the font in F19 to Windings, and add a formula of the like

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

XLGibbs
12-28-2006, 09:34 AM
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 :friends:


Thank you very much XLGibbs!!!

You are very welcome.

angelikv
12-28-2006, 10:42 AM
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!!!

:wavey:

XLGibbs
12-28-2006, 10:48 AM
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!!!

:wavey:

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
12-28-2006, 10:57 AM
hi,

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

trying to learn
thanks
zach

Bob Phillips
12-28-2006, 11:00 AM
A Pop-up Calendar for Excel http://www.fontstuff.com/vba/vbatut07.htm

angelikv
12-28-2006, 11:44 AM
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!!!!:cloud9:

Ps. Sample has been attached

Bob Phillips
12-28-2006, 12:17 PM
You are comparing the 2007 value against the percentage change. And why row 8.

What exactly do you want compared here?

XLGibbs
12-28-2006, 12:28 PM
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
12-28-2006, 12:29 PM
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!!!!:cloud9:

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
12-28-2006, 01:45 PM
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
12-28-2006, 03:45 PM
Hi Jimmy,
I tried your formula but it gives me an erros. :whyme:

XLGibbs
12-28-2006, 04:56 PM
Hi Jimmy,
I tried your formula but it gives me an erros. :whyme:

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


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


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
12-28-2006, 05:52 PM
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
12-28-2006, 06:01 PM
=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")

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
12-28-2006, 08:54 PM
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.:help
Can you send me the attachment to see if it works for you?

Thanks you!:rotlaugh:

JimmyTheHand
12-28-2006, 08:54 PM
Hi Angelica :hi:

Actually, I used the uploaded file "Comparison07.xls" in the process. The decision between smile and long face is based on the percent values in the "Increase" columns, on sheet "LastYearSales".

Also, I had that problem that XLGibbs mentioned, with the dates. I had to reformat column A on "LastYearSales" to something that's recognized locally here, and also cell D4 on "Receipt". I didn't know if this would be a problem for you, too, because in Hungary, it would seem, dates are written/formatted differently than anywhere else in the world, and I'm always :banghead: (i.e. having troubles) with them when everyone else is :) (i.e. happy smiling).

Hi XLGibbs :hi:

Thanks for further developing the formula while I was :sleeping:
Umm...You forgot the attachment ;)


EDIT
I'll attach my version.
I've put a $2500 into LastYearSales!D16, to give the face some reason to smile :)

XLGibbs
12-28-2006, 09:21 PM
Okay, it appears when the attached works..


Again, it is an issue of matching dates to dates. With the sample you had of 3/25/07, it was comparing text to date which didnt work. When I entered 1/14/07, it was a date, and did not need the DateValue() wrapper.
Change the formula to THIS:

Make sure you enter an actual date, and not text into the cell D4 of the receipt tab.
=IF(OFFSET(LastYearSales!$A$1,MATCH($D$4,LastYearSales!$A$1:$A$56,0)-1,MATCH($E$6,LastYearSales!1:1,0)+1)>=0,"J","L")

Check the attached. When I put in 2007 Baker Sales higher than 2006 I get a smiley face. Lower, I get a frown face.

Change the value in the 1/14/07 cell for Bakery from 200 to 2000 to see the smiley face work.(On the Report tab)


Read on...part of the problem is you may need to understand what the OFFSET and MATCH functions are doing so I will explain as it may help make things easier


Now the offset function does this:

OFFSET(Reference,Rows,Columns,Height, Width)

or clearly OFFSET( From where, how many rows do I move, how many columns do I move) (The height and width would create a RANGE of cells to those dimensions from the position). Offset basically means. From Point A, go to point B but with specific directions to get there.


OFFSET(LastYearSales!$A$1,{rows},{Columns})

English: Go to the LastYearsSales tab cell A1 and got {this many rows down/up} and {This many columns down/up}. the rows/columns up/down would be numbers, or formula results. Positive numbers mean down/right, negative mean up/left from the reference position.


MATCH(Lookup_Value,Lookup_Array,Match_Type)
Which says basically, Match (Match What, Where do I match it?, And is it an exact, or close match i am looking for)

What it returns is a position, in the Lookup_Array, as a number where it finds a match. (So since the array is A1:A56, the number would represent the position, 1 being A1, 56 being A56)

In this case:
MATCH($D$4,LastYearSales!$A$1:$A$56,0)-1
English: Match the date in D4, to a date in column A of the LastYearsSales tab. The "0" at the end says "Show me only an exact match". The -1 at the end, is to relate to the correct row number. For the offset to work, it essentially needs to represent the number of rows that we need to go down from A1 to get the date we matched to (based on it's position).

Row 6 is an offset of 5 rows from A1. But row 6 is the sixth position, so we need to subtract 1.


MATCH($E$6,LastYearSales!1:1,0)+1)

In this case, the same thing applies, but we are saying :
Match the name in E6, and return it's position in that row on an exact match. Since the offset logic is being used, and we know the Name on that tab is only in the left of the two columns for each "name", we must add 1 to the column returned. We want to go from Column D + 1 to Column E for Bakery, for example.

The IF statement is self explanatory..

So we can abbreviate our formula as follows:

IF The value for Date in D4 and the name in E6 is greater than the value to the right of it then "J". Otherwise return "L"

Now the other issue you will run into is with Pizza, so instead of Row 1:1 for the formula, I made a change and used Row 2:2 in my version attached.

I hope it helps...

XLGibbs
12-28-2006, 09:26 PM
Hi XLGibbs :hi:

Thanks for further developing the formula while I was :sleeping:
Umm...You forgot the attachment ;)


Ooops! You see, I was telepathically sending it to her! :think:

The date issue was part of it for sure. Otherwise, it seems between the two of us and Angelika we can etiher solve the problem:friends:, or cause her mass confusion!:banghead:

angelikv
12-28-2006, 09:50 PM
:think: :think: :think: mmmmmmmmmmmm I think I get it now!!!
Thank you for taking time and explain me the formula, it can get confusing if you don't know it. :yes

If I manually input the date it works perfectly; however, my goal is to save time and just clic on the calendar. :doh:
Is there any way that it would recognize the date by just finding it on the calendar instead of manually put it in?

The attachment shows what I have so far thanks to you help!!!:beerchug:

Angelik

XLGibbs
12-28-2006, 10:23 PM
Piece of cake Angelika

In the attached I have done two things.

One, it appears my adjustment to the formula was 1 row off in the Offset refrencing, so I changed the -1 to -2.

Also, I made sure that the linked cell with the calendar (in the Calendar1_Click event in the code) changes it to a date value and the cell is formatted in m/d/yyyy format as is column A of LastYearSales tab.

Additionally, as another option. Cell E4 of Reciept contains a data validation Drop down box which lists the dates from your list in Column A. (Data validation lists need to be on the same page, so i just put the list next to the calendar)

Since the calendar click only works for sundays to generate a valid result, i thought the drop down box might be a decent option.

It is possible to make that list "dynamic" to only show from say 4 weeks ago to 4 weeks from now or any other range of dates you wish...just let me know...tomorrow. I am done for tonight.

I also threw in a formula to pull in the "reported sales" calculation that you needed in the receipt tab. Hope it helps!:friends:

angelikv
12-28-2006, 10:40 PM
The face is not working:banghead: .......but I am also done for today!!

THANK YOU GUYS!!! for all your help....so far the project looks really good. :thumb

Good night!!:cloud9:

JimmyTheHand
12-28-2006, 11:50 PM
Hi XLGibbs :hi:


Ooops! You see, I was telepathically sending it to her! :think: Now, this is intriguing! You must teach me this technique someday : pray2:


One, it appears my adjustment to the formula was 1 row off in the Offset refrencing, so I changed the -1 to -2. :think: For me, it appears that it's 1 row off now, with -2. I think -1 was better.


Since the calendar click only works for sundays to generate a valid result That's why I suggested using 1 as comparison mode in MATCH, and Column "A" as range, so that it works for any date.

MATCH($D$4,LastYearSales!A:A,1) Of course, one must decide what he wants to see on, for example, a Monday. A face based on last week's (total) result, or a face based on the current week's (partial) result. It might alter the formula a bit.

XLGibbs
12-29-2006, 06:38 AM
Makes sense...it occurred to me that the >=0 2nd Match statement for the column should be a +2 and not a +1. As it stands I think it is looking at only the 2006 column, not the % change...I might be wrong though...

If it matches in column D, and offsets 1 it would be column, E, we want column F--for example...

Of course Aladin or someone is probably laughing at us right now, because this formula is fairly simple and we are making quite a mess of things...

LOL

JTH, I will teach the telepathy method another time!

angelikv
12-29-2006, 08:25 AM
:hi: Hi XLG and Jimmy,
Please let me know wich formula I should apply in order to get the calendar to work. I am in the process of learning more about excel :whistle: ....

Also, I forgot to mention a very important fact :doh: I only need Sundays. Sorry about the confusion. Thanks for your help and just remember that I am a beginer on this. :cloud9:

JimmyTheHand
12-29-2006, 08:26 AM
Hi XLGibbs :hi:


Makes sense...it occurred to me that the >=0 2nd Match statement for the column should be a +2 and not a +1. As it stands I think it is looking at only the 2006 column, not the % change...I might be wrong though...

If it matches in column D, and offsets 1 it would be column, E, we want column F--for example...

Of course Aladin or someone is probably laughing at us right now, because this formula is fairly simple and we are making quite a mess of things...
Why Aladin, I wonder :think: Well, nevermind.
It's good to make people laugh, I suppose, but let's try to clean up, nevertheless. Here's my thinking.

1st MATCH computes the row (Date), 2nd MATCH computes the column (Stand). 2nd MATCH is offseted by +2, from column "Stand" (also "2006") to column "Increase". These are absolute coordinates of the cell we seek. Starting from Cells(0, 0) they would be perfect.

But. Offset reference "A1" is Cells(1 ,1). If we use the values returned by the MATCH-es as Offset indices, we'll shoot by 1 row and 1 column too far. So, both indices must be decreased by 1. Hence the 1st MATCH is offseted by -1, the 2nd MATCH is offseted by +2-1 = +1.

If you check your posted version, you'll see that you have to modify sales on 1/7/2007 to change face on 1/14/2007.

I was also wondering if comparison ">=0" is okay. I mean, perhaps it would be better off with ">0". But this is Angelica's decision. She might not be happy with zero increase.



JTH, I will teach the telepathy method another time! :boohoo:mkay

XLGibbs
12-29-2006, 08:26 AM
The last file I uploaded has the "Sunday" only version, and includes the calendar fix.

JTH is correct on his above interpretation of the Matchola....

If it doesn't work the way you need it to, just let me know, I will tweak it. Or JTH will...who knows!

We are all beginners aren't we? I just know more about what I don't know these days.

XLGibbs
12-29-2006, 08:45 AM
Okay, Angelik!

Here is an edited, cleaned up version which compares the proper dates and such. I have linked a drop down selection box to the calendar. You can now click a date on the calendar or select a Sunday date from the drop down in Cell D4 (and both will change regardless of which is selected). Please note that Mon-Sat will not work as it is now sitting...

angelikv
12-29-2006, 08:47 AM
Can you send me the attachment?
Why does the receipt have two diffeent dates?:dunno

Is fine if Mon-sat don't work, I only need Sunday :)

XLGibbs
12-29-2006, 08:53 AM
We posted concurrently, see the new attachment in my last post.

I had two dates in the prior so you could see the difference. One was linked to calendar, the other was a drop down validation list.

The new version has both validation and calendar link in cell D4 as was your original.

For reference purposes (you can remove them) I added a Last Year $ and % +/- columns to the right of the Reported Sales line...

I did this to make confirming the "right" comparisons were being done and proper matching was occurring. Easier to "See" when each part of the equation is visible (so to speak)

angelikv
12-29-2006, 09:11 AM
PERFECTTTTTTTTTTTTTTTTTTTTTTTTTTTT:friends:


Now is working and it does everything I needed!!!

THANK YOU SO MUCH!!!!! I will try to learn more about excel ...do you guys suggest anything? maybe a book?

Happy holidays!!! :hi:

:thumb Excellent!

XLGibbs
12-29-2006, 09:18 AM
Your welcome.

A lot of us learned just by trying to figure out problems and reading the forums.

The only way to really learn is to practice by "doing it". Books are helpful.

There are also instructional classes, tutorials and such available in some books. This site here has VBA classes, and Mr.Excel has good listings of books.

If you are all set with this, mark the thread solved please ...

JimmyTheHand
12-29-2006, 10:21 AM
Nice to meet you both :)