Searching a row and displaying

Discuss the spreadsheet application

Searching a row and displaying

Postby icyblue » Thu Jan 24, 2008 7:08 pm

I'm relatively new to OO
Problem statement:-
In a Spreadsheets for student details, When I enter a student ID, it must search for that ID and display that particular row in some other highlightin color.

Can anyone help me Plz? should I write a macro for that?
icyblue
 
Posts: 3
Joined: Thu Jan 24, 2008 6:35 pm

Re: Searching a row and displaying

Postby TerryE » Thu Jan 24, 2008 8:26 pm

Iceblue, please don't ask the same Q twice. I've deleted the second copy of this Q in Macros. Please read the Survival Guide for the forum. This give basic guidance on how to look for answers, and how to frame you questions to help us to help you.

Now to your requirement. The functionality that you seem to be looking for is implemented as standard through Standard Filtering. Read up the help and examples on this and try it out. If this isn't what you are looking for then come back with a more focused Q on why it doesn't do what you want and what is it that you want that is different.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
Volunteer
 
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: Searching a row and displaying

Postby Dave » Fri Jan 25, 2008 5:13 am

You might look up something on Conditional Formatting for highlighting cells under the given condition. It is much discussed in the old forum, and a search might reveal some discussion here as well. You don't need a macro, you just need to match the record ID with the one you might indicate in some distant cell.

David.
Dave
 
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: Searching a row and displaying

Postby kingfisher » Fri Jan 25, 2008 7:02 am

Are the IDs entered in the rows you wish to highlight? If so, you could use Menu: Format >Conditional formatting. Instead of using "Cell value is", use "Formula is". You need to provide more details. It might help if you upload a sample of your spreadsheet.
Apache OpenOffice 4.1.6 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am

Re: Searching a row and displaying

Postby icyblue » Sat Jan 26, 2008 8:17 am

hi, Searching a student id should be made only to specific columns and the result should be in a different font colour. Contional formatting can only search for three id's at a time and search cannot be done for specific columns. And also the search should be made at the same time in many sheets and particular column in each sheet. So can anyone help me in solving this. Can this be solved with a macro.
icyblue
 
Posts: 3
Joined: Thu Jan 24, 2008 6:35 pm

Re: Searching a row and displaying

Postby kingfisher » Sat Jan 26, 2008 9:20 am

What I had in mind was formatting of the row to be highlighted. Suppose it contains the student id in column A and your rows begin with row 3. You want the row highlighted when the number is entered.

You would format row 3 with the condition : formula is $A3=$A$1. If you select all rows before entering the conditional format, the row automatically changes for subsequent rows.
Apache OpenOffice 4.1.6 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am

Re: Searching a row and displaying

Postby Dave » Sun Jan 27, 2008 2:18 am

I tried what I suggested, and it works just fine so far as I can see. Conditionally formatting the entire dataset could be made a little less tedious [unless I'm missing something], but it does work.

David.
Dave
 
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: Searching a row and displaying

Postby kingfisher » Sun Jan 27, 2008 3:38 am

If you select the whole data range first, you can conditionally format it with one operation. You just have to use an absolute address for the remote cell. I would also use an absolute column label for the column containing the ids but that is not strictly necessary. Since the row number is relative, it changes for each row in the range.
Apache OpenOffice 4.1.6 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am

Re: Searching a row and displaying

Postby Dave » Sun Jan 27, 2008 4:44 pm

I did that, and still got relative address errors in the individual cell references. It is likely I did something careless. In any event, the point is that conditional formatting does the job requested. Each cell must be so conditionally formatted for any entire row to have the necessary background when numbers in the first column match the one typed into the distant reference cell.

David.
Dave
 
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: Searching a row and displaying

Postby icyblue » Sun Jan 27, 2008 8:27 pm

hi, I would also like to know if a macro can be run twice in different files at the same time. I tried it but it doesn't work. So is there a way to run the same macro in two different open office files at the same time.
icyblue
 
Posts: 3
Joined: Thu Jan 24, 2008 6:35 pm

Re: Searching a row and displaying

Postby kingfisher » Sun Jan 27, 2008 9:38 pm

What is the macro you are trying to run? When a macro is run, one line of code is executed at a time. The code can relate to different files. The lines code would not be run simultaneously.
Apache OpenOffice 4.1.6 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 25 guests