Ad Free CAclubindia and Daily Updates on your Mobile. Subscribe Now!

Excel Formulas

(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

Reply
Follow
Share

10 Replies

Vinod (Assistant Audit Officer) (1675 Points)
Replied 10 July 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.

Upgrade to CAclubindia Pro. Subscribe Now!

Divya sharma (CHARTERED ACCOUNTANT) (250 Points)
Replied 10 July 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) (14487 Points)
Replied 10 July 2008

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

Attached File : 8 book1.xls downloaded: 263 times

Vinod (Assistant Audit Officer) (1675 Points)
Replied 11 July 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.

Upgrade to CAclubindia Pro. Subscribe Now!

Ananthachari SD (Chartered Accountant) (21 Points)
Replied 11 July 2008

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)
Replied 14 July 2008

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

Sekhar Dasari (Accounts Officer) (159 Points)
Replied 22 November 2008

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

Attached File : 1 8 book1.xls downloaded: 169 times