Forum Statistics
- Forum Members:
- Total Threads:
- Total Posts: 13
There are 1 users currently browsing forums.
|
 |
06-23-2009, 04:36 AM
|
|
Registered User
|
|
Join Date: 23 Jun 2009
Location: saudi
MS Office Version:Excel 2003
Posts: 15
|
|
|
hyperlink or formula
Please Register to Remove these Ads
I have a spreadsheet that requires me to see the status of every employee. What I want to do is just to type their employee number and all information and status regarding with this employee should appear next to the number i type. My database is on other sheet while I want to work on the other sheet. A formula or hyperlink can do this job? Any help will be appreciated. Thanks.
Last edited by bokals; 06-23-2009 at 05:52 AM.
Reason: wrong post title
|
06-23-2009, 05:56 AM
|
 |
Forum Guru
|
|
Join Date: 22 Oct 2008
Location: Suffolk, UK
MS Office Version:2002 & 2007
Posts: 13,667
|
|
|
Re: hyperlink or formula
Thank you for altering your title - both mdw & squiggler have solutions for you I believe so I will leave to them...
|
06-23-2009, 06:01 AM
|
 |
Valued Forum Contributor
|
|
Join Date: 17 Feb 2009
Location: Littleborough, UK
MS Office Version:Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
Posts: 457
|
|
|
Re: hyperlink or formula
This should work for you
__________________
 No longer looking for work (still if you want to pay me lots of money lets talk!!!)
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare............
Now, thanks to the Internet, we know this is not true.
Robert Wilenski (since there is some confusion I am not Robert he wrote the quote!)
|
06-23-2009, 06:17 AM
|
|
Registered User
|
|
Join Date: 23 Jun 2009
Location: saudi
MS Office Version:Excel 2003
Posts: 15
|
|
|
Re: hyperlink or formula
Thank you squiggler47 your formula solved my problem. Thanks also to the moderator for the corrections of my mistakes. Your help is very much appreciated. God Bless all
|
06-23-2009, 08:54 AM
|
|
Registered User
|
|
Join Date: 23 Jun 2009
Location: saudi
MS Office Version:Excel 2003
Posts: 15
|
|
|
Re: hyperlink or formula
Quote:
Originally Posted by squiggler47
This should work for you
|
Greetings,
Your formula did great on my work, however i forgot to include 1 column in the worksheet. could you pls. help me again. also how can I add the number under the employee no? it seems that it is only limited to 21. If I put additional employee I cannot find their number
thanks in advance
|
06-23-2009, 09:09 AM
|
|
Valued Forum Contributor
|
|
Join Date: 17 Feb 2009
Location: Littleborough, UK
MS Office Version:Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
Posts: 457
|
|
|
Re: hyperlink or formula
I made the changes, the Employee number Take into account up to 65000 items! Just add below!
__________________
No longer looking for work (still if you want to pay me lots of money lets talk!!!)
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare............
Now, thanks to the Internet, we know this is not true.
Robert Wilenski (since there is some confusion I am not Robert he wrote the quote!)
|
06-24-2009, 01:07 AM
|
|
Registered User
|
|
Join Date: 23 Jun 2009
Location: saudi
MS Office Version:Excel 2003
Posts: 15
|
|
|
Re: hyperlink or formula
Quote:
Originally Posted by squiggler47
I made the changes, the Employee number Take into account up to 65000 items! Just add below!
|
hi.
The changes was great I was able to put additional rows on my worksheet but the only problem I have is with in the column. I added two columns and I tried to copy your first formula and paste it to the new columns, but it doesnt work, How am i going to do it? I uploaded the new worksheet. Thanks again for your help
|
06-24-2009, 02:51 AM
|
|
Valued Forum Contributor
|
|
Join Date: 17 Feb 2009
Location: Littleborough, UK
MS Office Version:Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
Posts: 457
|
|
|
Re: hyperlink or formula
I made a change which allows the formula to be copied, but oly if the columns on your employees sheet are in the same order as the Status sheet!
the formula is
=IF(INDEX(' Employees'!$A$5:$G$65000,MATCH($B7,EmployeeNumber,0),COLUMN())<>"",INDEX(' Employees'!$A$5:$G$65000,MATCH($B7,EmployeeNumber,0),COLUMN()),"")
If you want to change it to refer to columns out of order change both column() in red to the number of the column you want A=1 etc!
Hope this helps
Regards
Darren
__________________
No longer looking for work (still if you want to pay me lots of money lets talk!!!)
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
We've all heard that a million monkeys banging on a million typewriters will eventually reproduce the entire works of Shakespeare............
Now, thanks to the Internet, we know this is not true.
Robert Wilenski (since there is some confusion I am not Robert he wrote the quote!)
|
06-24-2009, 04:10 AM
|
|
Registered User
|
|
Join Date: 23 Jun 2009
Location: saudi
MS Office Version:Excel 2003
Posts: 15
|
|
|
Re: hyperlink or formula
Quote:
Originally Posted by squiggler47
I made a change which allows the formula to be copied, but oly if the columns on your employees sheet are in the same order as the Status sheet!
the formula is
=IF(INDEX(' Employees'!$A$5:$G$65000,MATCH($B7,EmployeeNumber,0),COLUMN())<>"",INDEX(' Employees'!$A$5:$G$65000,MATCH($B7,EmployeeNumber,0),COLUMN()),"")
If you want to change it to refer to columns out of order change both column() in red to the number of the column you want A=1 etc!
Hope this helps
Regards
Darren
|
thank you once again i'll try to work with new column and i wll let you know if I encountered error. God Bless
|
06-25-2009, 08:02 AM
|
|
Registered User
|
|
Join Date: 25 Jun 2009
Location: USA
MS Office Version:Excel 2003
Posts: 1
|
|
|
Re: hyperlink or formula
well its great to be here ....i am learning a lot from this forum ....actually i am an accountant in a printing company and i have such a kinda same problem ....well the problem is solved by this formula thanks for the help ...
Last edited by larycom; 06-25-2009 at 08:04 AM.
|
07-01-2009, 04:54 AM
|
|
Registered User
|
|
Join Date: 01 Jul 2009
Location: USA
MS Office Version:Excel 2003
Posts: 1
|
|
|
Re: hyperlink or formula
I need a formula that would say if any values in column A of sheet1 are found within column C of sheet 2, then give me the value for sheet2 column F for that record that is found in sheet 2 column C.
|
07-01-2009, 05:29 AM
|
|
Forum Guru
|
|
Join Date: 22 Oct 2008
Location: Suffolk, UK
MS Office Version:2002 & 2007
Posts: 13,667
|
|
|
Re: hyperlink or formula
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
Please take the time to read the forum rules.
|
07-06-2009, 07:41 AM
|
|
Registered User
|
|
Join Date: 06 Jul 2009
Location: USA
MS Office Version:Excel 2003
Posts: 1
|
|
|
Re: hyperlink or formula
it's So Easy just open the Excel sheet where your Actual Employee data then go to the Next sheet and wrote =Sheet1!B2 or which data u required on Sheet 2 it's Automatic link with both sheet.
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
