Excel Formulas

Knowledge resource 2099 views 10 replies

Guest

Can any one tell me that what formula has been applied at excel sheet in following condition:

Place	Name	Roll No	Marks
Jaipur	Madan	154265	250
Jodhpur	Mohan	154266	265
Delhi	Ganpat	154267	245
Jaipur	Amit	154268	235
Jaipur	Anil	154269	275
Jodhpur	Nilesh	154270	230
Delhi	Tarun	154271	254
Jodhpur	Abhinav	154272	256
Jodhpur	Pawan	154273	287
Jodhpur	Saurav	154274	200
Delhi	Lata	154275	305
Delhi	Ramesh	154276	255
Delhi	Govind	154277	265

Now I need the follwing in other sheet for Jaipur /Jodhpur/ delhi on seperate sheet

Name

Roll No

Marks

How can we took data from other sheet to this sheet as I have used 'Vlookup' 'IF' etc. but all of them pick only one data for all raws. For Example If i use these formulas in raws 1 to 10 it will display one result for all

please advice

Thanks

Rajesh

Replies (10)

Vinod

Assistant Audit Officer

1675 Points Joined March 2008

1.Go to Data >Filter > Autofilter.

2. Click on Place.

3. Select the required Place. It will sort the list as per place.

4. Now copy it and paste it in separate sheet.

Repeat steps 2 - 4 for other places.

Hope it works.

Divya sharma

CHARTERED ACCOUNTANT

250 Points Joined January 2008

well i don't know exact solution of your answer but still i can give u hint .

Mail merge is the function which can help you .

Guest

Thanks for Reply

1. The filter does not allowed two different situation at a time and the fist sheet i have mentioned is a data sheet which will required to fill on daily basis. Purpose of using excel formula is only that when will the data sheet updated the report sheet will updataed automatically.

2 Mail merge is solution for use one time report creation i need a solution on running basis updation.

Regards

Rajesh

Ram Avtar Singh

Nagari Sultanpur U.P.Delhi

15155 Points Joined March 2007

Hi I am attaching your file as data required by u.

thanks



Place	Name	Roll No	Marks
Delhi	Ganpat	154267	245
	Govind	154277	265
	Lata	154275	305
	Ramesh	154276	255
	Tarun	154271	254
Jaipur	Amit	154268	235
	Anil	154269	275
	Madan	154265	250
Jodhpur	Abhinav	154272	256
	Mohan	154266	265
	Nilesh	154270	230
	Pawan	154273	287
	Saurav	154274	200

Vinod

Assistant Audit Officer

1675 Points Joined March 2008

Well dear then, u have to use macros.

I don't know much about it but succeeded in creating one yesterday.

I m writing steps.

1. After opening excel sheet containing data, go to tools > Macros > Record New Macro.

2. After this whatever you do it should be standard (For all sheets further to be copied upon).

3. When finished, stop the macro with stop button on macro toolbar.

Hope it works.

Ananthachari SD

Chartered Accountant

21 Points Joined December 2007

Can u please explain how to create a macro

Regards

Ananthachari SD

Guest

I need a formula which can apply on excel sheet.

thanks for your suggestion but I know all above, i need a formula or combination of formulas from implemeting that I can able to see all report required.

I strongly believe that if we use a formula on the second sheet which consider the two different value together and go for result

please help

Ranjit Bisoy

HCL Employee

0 Points Joined July 2008

Its very Simple. Use Pivot table dude. One click thats it...

Sekhar Dasari

Accounts Officer

164 Points Joined October 2007

Sekhar Dasari

Accounts Officer

164 Points Joined October 2007

Rejesh,

See the attached file and it will be the solution to your problem. It is based on Pivot Table and Just you have to refresh when you entered data in the masterdata sheet.

go to data>refresh (Note that your curser must be on the pivot table in any sheet.

contact me any doubts on dvsdasari @ gmail.com

Thanks- Sekhar Dasari