Formula help in Excel 2007

[Millie]

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?

 

[Gord Dibben]

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

 

[Ken Johnson]

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?

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!$C2<>B$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)),Commissioners!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!$C2<>B$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

 

[Ken Johnson]

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