Automatically search the specific parts and paste its in a different column

**PRADEEPB270** · 05-05-2010, 07:15 AM

I have a file with 45 sheets related to different model.I want to know an excel formula which I want to apply on any one sheets and copy of that formula on all the sheets automatically in the same time.I want to know the specific parts end with "C" and want to apply an excel formula in a sheet.It should be copy automatically on all the sheets within the same time.

Please refer the attached file for more clarification.

In which,I have taken 4 sheets only and highlighted the parts of my expected result.Further,I have copy of these in column I.

Regards

Pradeep Kumar Gupta

**DP978** · 05-05-2010, 09:30 AM

The simplest way would be a Macro.

You will need to write one that searches the B column using the Right() function to find the 'C's and then a counter to tell it where to paste. Use the 'For Each Sheet' function to have it scroll through all 45 sheets.

Edit: If possible you are going to want to cleanse your data and remove gaps in rows, this will allow the macro to run more smoothly.

**PRADEEPB270** · 05-05-2010, 11:00 AM

This is not my required solution.Thanks for your efforts.If any one can help me,I will be glad.

Regards

Pradeep Kumar Gupta

**JBeaucaire** · 05-05-2010, 09:15 PM

1) Click on the first sheet
2) Shift-click on the last sheet (all sheets are now selected)
3) In J1 enter the following formula:

=IF(I1="", "", INDEX(F:F, MATCH(I1,B:B, 0)))

...and copy down a ways in that column.

4) Right-click a sheet and select Ungroup Sheets

Now all sheets have a formula in column A to lookup the prices of any code entered into column I.