Solved

How to create a Macro in Excel 2003 to take values from one sheet to the next sheet

Posted on 2011-02-15
16
271 Views
Last Modified: 2012-06-27
Dear Friends

I have attached an excel workbook with two sheets namely JER and CBPL. What I am hoping to get your help are:

1) A Macro button which when pressed pops up a box asking "Which Section do you require" and then " Please mention Section Code. (The section - A,B,C,D,E and Section code eg 1.0, 1.01, 1.02 etc are found on the second sheet CBPL)

2) Once we mention the code, the revelant description found in column C of CBPL(Sheet 2) gets automatically copied onto the D25 cell of JER(Sheet 1) and the relevant price in coloumn G of CBPL (Sheet 2) gets copied onto I25 cell of JER (Sheet1).

If we press the macro button again, and enter the section and code, the next description should fit in D26 and price should go into I26.

Is Macro the best way around for this? If so could you please help me with this?
JER-NEW-FORMAT.xls
0
Comment
Question by:jimjoseph
  • 8
  • 7
16 Comments
 
LVL 16

Expert Comment

by:Kalpesh Chhatrala
ID: 34896059
0
 
LVL 1

Author Comment

by:jimjoseph
ID: 34896188
Hi Kalpesh

This just copies line by line to another sheet. I was looking for something more advanced in selecting and copying from one sheet to another sheet.
0
 
LVL 5

Expert Comment

by:wellous
ID: 34899622
Hi jimjoseph,
Pls find the attachment , i modified a little bit your database and put it in same sheet (JER) starting row 100 ... ( down) after the print area ... also modified the codes to be (section with code) instead of having sections in column and codes in different column to simplify it...

I hope you like it.. it's not Macro it's LookUP condition..
Also i added another column in JER table called [Code ] with dropdown list to select the code you want then the discription and price will be filled automatically in Sale colum.

Good luck , Wellous
0
 
LVL 5

Expert Comment

by:wellous
ID: 34899640
Sorry , forgot the file ;-)
modified-JER-NEW-FORMAT.xls
0
 
LVL 1

Author Comment

by:jimjoseph
ID: 34906608
Hi Wellous

This is absolutely absolutely amazing. You are a star. I did not think about V Lookup. I love it!

I do have a small query though. In the JER Sheet, if we do not enter the code, the column J shows #N/A and when the code is entered, the price automatically comes in correctly. However, I would want the SUM of the column J at the end and if I set the formula =SUM(J25:J60) it comes as #NAME because of the #NA. Do you know anyway I could avoid that?
0
 
LVL 5

Accepted Solution

by:
wellous earned 500 total points
ID: 34932738
Hi jimjoseph,
Thank you for your encourging words , really appreciated !
It's very easy to avoid this from happening , the easiest way is to select the whole row then delete it :) .. see very easy ..
OR
A little bit of complicating the current Lookup is to add IF before it like how i made in yr sheet.

Cheers,
Wellous


modified-JER-NEW-FORMAT.xls
0
 
LVL 1

Author Comment

by:jimjoseph
ID: 34942513
Hi Wellous

Sorry for the delay as I was a bit unwell. I would have to go for the complicated IF function as this sheet is to be used by other and they might just enter proably the first three column and when the delete the rows the formula disappears. I however did something else, I added and Extra code which shows the price zero. So the default excel sheet contains the code for Zero price and as and when users enter codes the other rows remains as num and we can get a total.

I am not sure if I am streching your patience. I would require one more help. I am not sure of it is possible. There are some codes such as D 3.01 or E 4.0 which has price of 15%. Which means if we select D 3.01 in C25, then J 25 should be 15% of H 25. Can this be done on the same cell as it already contains a look up formula?
0
 
LVL 5

Expert Comment

by:wellous
ID: 34950845
Hi jimjoseph,

I suggest to place the actual price instead of 15% of the( Documentation cost+) ... just to simplify the formula ..
Remember that H25 column is empty , we didn't set any formula on it or even to look into it.


Brgds
Walid OMIRA


0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:jimjoseph
ID: 34951379
Hi Walid

Sorry. I did not quite understand.J 25 has the lookup fromula. If we select D 3.01, then J25 = 15% of whatever the is amount going to be typed in H25. Are you saying that incase D 3.01 comes up, we have to manually calculate 15% and typeit in?
0
 
LVL 5

Expert Comment

by:wellous
ID: 34951972
Hi Jimjoseph,

Yes , this is what i wanted to say... whenever D3.01 is set then calculate it manullay , to avoid complicating the formula with another IF , just to make it working.

Good luck.
Walid
0
 
LVL 1

Author Closing Comment

by:jimjoseph
ID: 34958598
Thanks Walid. You have been immensly helpful.
0
 
LVL 5

Expert Comment

by:wellous
ID: 34961029
Thank you Jim.
Wish you the best,
Walid
0
 
LVL 1

Author Comment

by:jimjoseph
ID: 35464962
Hi Walid

I am so sorry for coming back late on this. While the users were happily working on the file, there is an apparent bug in the VLOOKUP. I am attaching the Page with the VLOOKUP you had created. Everything works fine till we use the drop down to select E9.0 and below the the value it is supposed to look up is incorrect. I recreated the table on another sheet and the problem persists. Could you kindly help?
JERRR.xls
0
 
LVL 5

Expert Comment

by:wellous
ID: 35497770
Hi Jim,

No problem,
I corrected it,
Pls from now on if you want to put additional codes then select the source data and sort them according to the first row (A).

Good luck.
Walid,
JERRR.xls
0
 
LVL 1

Author Comment

by:jimjoseph
ID: 35500266
Super! I would have doubled the points if I could.
0
 
LVL 5

Expert Comment

by:wellous
ID: 35500638
you welcome dear,
the question is closed already.. :)

Are you going to add more codes and details ( discriptions ) in the code source part in yr database?
if yes , then we can create new worksheet beside the existing one and mark it for lookup function.

pls tell me if you need any improvement ..

Good luck,
Walid
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now