A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Formula help in Excel 2007



 
 
Thread Tools Display Modes
  #1  
Old May 16th, 2009, 07:10 PM posted to microsoft.public.excel.worksheet.functions
Millie[_2_]
external usenet poster
 
Posts: 118
Default Formula help in Excel 2007

I know it's possible to link cells from one worksheet to another but not sure
if what I want to achieve is possible (I use only the basic functions in
Excel).

I want to insert information into one sheet and have a version of that
information appear in another sheet. For example, I have a list of jobs I've
been commissioned to do in sheet 1 and they are in a list in date order, how
much I got paid and what the job was. In another sheet I have a list of
people who've commissioned me to do work and what the job was - so I can see
at a glance who is offering me the most work. Some of the information is
repeated e.g. the job, the date etc.

At the moment I am manually typing in the same information in each sheet.
Can I link cells so that any info repated autmatically appears in the second
sheet?
--
Millie
  #2  
Old May 16th, 2009, 07:47 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Formula help in Excel 2007

In Sheet2 enter =Sheet1!A1 in an appropriate cell.

Or error-trapped for blank cells in Sheet1

=IF(Sheet1!A1="","",Sheet1!A1)


Gord Dibben MS Excel MVP

On Sat, 16 May 2009 11:10:01 -0700, Millie
wrote:

I know it's possible to link cells from one worksheet to another but not sure
if what I want to achieve is possible (I use only the basic functions in
Excel).

I want to insert information into one sheet and have a version of that
information appear in another sheet. For example, I have a list of jobs I've
been commissioned to do in sheet 1 and they are in a list in date order, how
much I got paid and what the job was. In another sheet I have a list of
people who've commissioned me to do work and what the job was - so I can see
at a glance who is offering me the most work. Some of the information is
repeated e.g. the job, the date etc.

At the moment I am manually typing in the same information in each sheet.
Can I link cells so that any info repated autmatically appears in the second
sheet?


  #3  
Old May 17th, 2009, 12:30 AM posted to microsoft.public.excel.worksheet.functions
Ken Johnson
external usenet poster
 
Posts: 499
Default Formula help in Excel 2007

On May 17, 4:10*am, Millie wrote:
I know it's possible to link cells from one worksheet to another but not sure
if what I want to achieve is possible (I use only the basic functions in
Excel).

I want to insert information into one sheet and have a version of that
information appear in another sheet. For example, I have a list of jobs I've
been commissioned to do in sheet 1 and they are in a list in date order, how
much I got paid and what the job was. In another sheet I have a list of
people who've commissioned me to do work and what the job was - so I can see
at a glance who is offering me the most work. Some of the information is
repeated e.g. the job, the date etc.

At the moment I am manually typing in the same information in each sheet.
Can I link cells so that any info repated autmatically appears in the second
sheet?
--
Millie


One way uses IF, ISERROR, INDEX, ROW, MATCH and SMALL functions.

Let the first sheet be "All_Jobs" and the second sheet
"Commissioners".
Let All_Jobs have the following structure...
Column A Date
Column B Job Description
Column C Commissioner
Column D Amount Paid

Enter the commissioner's names into the even numbered columns (B, D,
F...) in the top row of the Commissioners sheet.

Enter this formula into A2 on the commissioners sheet then fill it
down as far as required (= rows on All_Jobs sheet)...

=IF(All_Jobs!$C2B$1,"",ROW(1:1))

Enter this formula into B2 on the commissioners sheet then fill down,
again as far as required...

=IF(ISERROR(SMALL(A$2:A$32,ROW(1:1))),"",INDEX(All _Jobs!$B$2:$B
$32,MATCH(SMALL(Commissioners!A$2:A$32,ROW(1:1)),C ommissioners!A$2:A
$32,0)))

Select then copy A2:B2 of the Commissioners sheet.
Select from C2 up to what ever column holds the last Commissioner's
name in row 1 then paste the formulas into those selected row 2 cells
(there should be an even number of selected cells), then fill the
pasted formulas down as far as required (as before).

Use the Ctrl key and mouse to select the odd numbered columns holding
the =IF(All_Jobs!$C2B$1,"",ROW(1:1)) formula then hide those
columns.

Now as you enter data into the All_Jobs sheet it will automatically be
sent to the Commissioners sheet where the Job descriptions will appear
under the relevant Commissioners' Headings.

When new commissioners are added to the All_Jobs sheet you will need
to add the new commissioner's name to the Commissioners sheet and copy/
paste a pair of columns to the right of the existing ones. Unless you
manage it differently, you will need to remember that one of the
columns to be copied will need to be unhidden first.

Ken Johnson
  #4  
Old May 17th, 2009, 02:25 PM posted to microsoft.public.excel.worksheet.functions
Ken Johnson
external usenet poster
 
Posts: 499
Default Formula help in Excel 2007

Oops!, the 32 in each of A$2:A$32 and $B$2:$B$32 in the second formula
should really be a number that is at least equal to the number of rows
used on the All_Jobs sheet.

Alternatively use dynamic named ranges.

Ken Johnson
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.