I am weakest in excel; I have an understanding of VBA in Word and I have
attended an advanced Access course.

I have this problem in excel, I have no idea what track to take with it,
Hence the cross posting between programming and worksheet functions.

This workshbook is to be used by our surveyors to input data from site
inspections.

I have a table on one worksheet with the column headings:

Mark As Behind
Task No
Task Description
Contractor's Estimate
Our Estimate

I have a table on another worksheet with the column headings:

Mark As Behind
Task No
Task Description
Total Weeks for Task
Contractor's Estiamte
Our Estimate

Worksheet one is percentages of work carried out. We use this to estimate
the overall progress.

Worksheet two is weeks of work carried out. This calculates the average no
of weeks behind schedule.

I only use the tasks which are behind programme in table two. Hence the
column 'Mark as Behind'. How do I programme excel, to use this column, to
take the tasks Marked as Behind into table two?

Regards
Dylan Dawson
Building Surveyor
Glasgow, Scotland

Hi Dylan

On Sheet2 in cell B2
=IF(Sheet1!$A2="","",Sheet1!B2)
Copy to C2 and E2:F2
Copy down as far as required

If your sheet names are not Sheet1 etc. but have spaces in the name,
enclose with single quotes e.g.
'Main Sheet'!$A2
--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
I am weakest in excel; I have an understanding of VBA in Word and I
have
attended an advanced Access course.

I have this problem in excel, I have no idea what track to take with
it,
Hence the cross posting between programming and worksheet functions.

This workshbook is to be used by our surveyors to input data from site
inspections.

I have a table on one worksheet with the column headings:

Mark As Behind
Task No
Task Description
Contractor's Estimate
Our Estimate

I have a table on another worksheet with the column headings:

Mark As Behind
Task No
Task Description
Total Weeks for Task
Contractor's Estiamte
Our Estimate

Worksheet one is percentages of work carried out. We use this to
estimate
the overall progress.

Worksheet two is weeks of work carried out. This calculates the
average no
of weeks behind schedule.

I only use the tasks which are behind programme in table two. Hence
the
column 'Mark as Behind'. How do I programme excel, to use this column,
to
take the tasks Marked as Behind into table two?

Regards
Dylan Dawson
Building Surveyor
Glasgow, Scotland

Roger,

Thanks for your quick response.

I understand the copy link procedure, but way you are suggesting will
involve a lot of manual time in excel for each survey. I am looking for is
something which will only copylink the items that are flagged as being
behind programme in the "Mark as Behind" column.

something along the lines of
If A5 is null do nothing
If A5 =1, copylink this row (or specific cells in this row) to Worksheet 2.

The problem I see in making the copied rows consecutive. I wonder if this
would involve a VBA routine where the routine works on the selected items as
a single object?

This is my general understanding of the problem anyway.

Any suggestions?

Regards
Dylan Dawson


"Roger Govier" wrote in message
...
Hi Dylan

On Sheet2 in cell B2
=IF(Sheet1!$A2="","",Sheet1!B2)
Copy to C2 and E2:F2
Copy down as far as required

If your sheet names are not Sheet1 etc. but have spaces in the name,
enclose with single quotes e.g.
'Main Sheet'!$A2
--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
I am weakest in excel; I have an understanding of VBA in Word and I
have
attended an advanced Access course.

I have this problem in excel, I have no idea what track to take with
it,
Hence the cross posting between programming and worksheet functions.

This workshbook is to be used by our surveyors to input data from site
inspections.

I have a table on one worksheet with the column headings:

Mark As Behind
Task No
Task Description
Contractor's Estimate
Our Estimate

I have a table on another worksheet with the column headings:

Mark As Behind
Task No
Task Description
Total Weeks for Task
Contractor's Estiamte
Our Estimate

Worksheet one is percentages of work carried out. We use this to
estimate
the overall progress.

Worksheet two is weeks of work carried out. This calculates the
average no
of weeks behind schedule.

I only use the tasks which are behind programme in table two. Hence
the
column 'Mark as Behind'. How do I programme excel, to use this column,
to
take the tasks Marked as Behind into table two?

Regards
Dylan Dawson
Building Surveyor
Glasgow, Scotland

Hi Dylan

You could write VBA code to do it, but another alternative would be to
use Advanced Filter to bring the data across to you second sheet.
Take a look at Debra Dalgleish's site for some excellent instruction on
how to do this.
http://www.contextures.com/xladvfilter01.html#ExtractWs
There are also some sample workbooks on the site you can download

--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
Roger,

Thanks for your quick response.

I understand the copy link procedure, but way you are suggesting will
involve a lot of manual time in excel for each survey. I am looking
for is
something which will only copylink the items that are flagged as being
behind programme in the "Mark as Behind" column.

something along the lines of
If A5 is null do nothing
If A5 =1, copylink this row (or specific cells in this row) to
Worksheet 2.

The problem I see in making the copied rows consecutive. I wonder if
this
would involve a VBA routine where the routine works on the selected
items as
a single object?

This is my general understanding of the problem anyway.

Any suggestions?

Regards
Dylan Dawson


"Roger Govier" wrote in message
...
Hi Dylan

On Sheet2 in cell B2
=IF(Sheet1!$A2="","",Sheet1!B2)
Copy to C2 and E2:F2
Copy down as far as required

If your sheet names are not Sheet1 etc. but have spaces in the name,
enclose with single quotes e.g.
'Main Sheet'!$A2
--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
I am weakest in excel; I have an understanding of VBA in Word and I
have
attended an advanced Access course.

I have this problem in excel, I have no idea what track to take with
it,
Hence the cross posting between programming and worksheet functions.

This workshbook is to be used by our surveyors to input data from
site
inspections.

I have a table on one worksheet with the column headings:

Mark As Behind
Task No
Task Description
Contractor's Estimate
Our Estimate

I have a table on another worksheet with the column headings:

Mark As Behind
Task No
Task Description
Total Weeks for Task
Contractor's Estiamte
Our Estimate

Worksheet one is percentages of work carried out. We use this to
estimate
the overall progress.

Worksheet two is weeks of work carried out. This calculates the
average no
of weeks behind schedule.

I only use the tasks which are behind programme in table two. Hence
the
column 'Mark as Behind'. How do I programme excel, to use this
column,
to
take the tasks Marked as Behind into table two?

Regards
Dylan Dawson
Building Surveyor
Glasgow, Scotland

I couldn't understand the criteria part of this.
My criteria would be to select items = 1 in column A and filter out others.
How would I do this?

"Roger Govier" wrote in message
...
Hi Dylan

You could write VBA code to do it, but another alternative would be to
use Advanced Filter to bring the data across to you second sheet.
Take a look at Debra Dalgleish's site for some excellent instruction on
how to do this.
http://www.contextures.com/xladvfilter01.html#ExtractWs
There are also some sample workbooks on the site you can download

--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
Roger,

Thanks for your quick response.

I understand the copy link procedure, but way you are suggesting will
involve a lot of manual time in excel for each survey. I am looking
for is
something which will only copylink the items that are flagged as being
behind programme in the "Mark as Behind" column.

something along the lines of
If A5 is null do nothing
If A5 =1, copylink this row (or specific cells in this row) to
Worksheet 2.

The problem I see in making the copied rows consecutive. I wonder if
this
would involve a VBA routine where the routine works on the selected
items as
a single object?

This is my general understanding of the problem anyway.

Any suggestions?

Regards
Dylan Dawson


"Roger Govier" wrote in message
...
Hi Dylan

On Sheet2 in cell B2
=IF(Sheet1!$A2="","",Sheet1!B2)
Copy to C2 and E2:F2
Copy down as far as required

If your sheet names are not Sheet1 etc. but have spaces in the name,
enclose with single quotes e.g.
'Main Sheet'!$A2
--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
I am weakest in excel; I have an understanding of VBA in Word and I
have
attended an advanced Access course.

I have this problem in excel, I have no idea what track to take with
it,
Hence the cross posting between programming and worksheet functions.

This workshbook is to be used by our surveyors to input data from
site
inspections.

I have a table on one worksheet with the column headings:

Mark As Behind
Task No
Task Description
Contractor's Estimate
Our Estimate

I have a table on another worksheet with the column headings:

Mark As Behind
Task No
Task Description
Total Weeks for Task
Contractor's Estiamte
Our Estimate

Worksheet one is percentages of work carried out. We use this to
estimate
the overall progress.

Worksheet two is weeks of work carried out. This calculates the
average no
of weeks behind schedule.

I only use the tasks which are behind programme in table two. Hence
the
column 'Mark as Behind'. How do I programme excel, to use this
column,
to
take the tasks Marked as Behind into table two?

Regards
Dylan Dawson
Building Surveyor
Glasgow, Scotland

Hi Dylan

Start with a blank new sheet.
Copy your Headings from your main sheet
In cell A2 enter =1
DataFilterAdvanced FilterSelect copy to new location
click on the icon in List Range and select the whole of the data range
(including headers) on Main sheet.
Click on icon in Filter Range and select A1:E2 of the new sheet
Click on Copy to and select cell A5 of new sheet
Click OK


All of the rows with 1 on Main sheet should appear on the new sheet.
Set any criteria in B2:E2 that you wish and repeat the process, and the
list will be those rows that satisfy all of the criteria set.


--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
I couldn't understand the criteria part of this.
My criteria would be to select items = 1 in column A and filter out
others.
How would I do this?

"Roger Govier" wrote in message
...
Hi Dylan

You could write VBA code to do it, but another alternative would be to
use Advanced Filter to bring the data across to you second sheet.
Take a look at Debra Dalgleish's site for some excellent instruction
on
how to do this.
http://www.contextures.com/xladvfilter01.html#ExtractWs
There are also some sample workbooks on the site you can download

--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
Roger,

Thanks for your quick response.

I understand the copy link procedure, but way you are suggesting will
involve a lot of manual time in excel for each survey. I am looking
for is
something which will only copylink the items that are flagged as
being
behind programme in the "Mark as Behind" column.

something along the lines of
If A5 is null do nothing
If A5 =1, copylink this row (or specific cells in this row) to
Worksheet 2.

The problem I see in making the copied rows consecutive. I wonder if
this
would involve a VBA routine where the routine works on the selected
items as
a single object?

This is my general understanding of the problem anyway.

Any suggestions?

Regards
Dylan Dawson


"Roger Govier" wrote in message
...
Hi Dylan

On Sheet2 in cell B2
=IF(Sheet1!$A2="","",Sheet1!B2)
Copy to C2 and E2:F2
Copy down as far as required

If your sheet names are not Sheet1 etc. but have spaces in the name,
enclose with single quotes e.g.
'Main Sheet'!$A2
--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
I am weakest in excel; I have an understanding of VBA in Word and I
have
attended an advanced Access course.

I have this problem in excel, I have no idea what track to take with
it,
Hence the cross posting between programming and worksheet functions.

This workshbook is to be used by our surveyors to input data from
site
inspections.

I have a table on one worksheet with the column headings:

Mark As Behind
Task No
Task Description
Contractor's Estimate
Our Estimate

I have a table on another worksheet with the column headings:

Mark As Behind
Task No
Task Description
Total Weeks for Task
Contractor's Estiamte
Our Estimate

Worksheet one is percentages of work carried out. We use this to
estimate
the overall progress.

Worksheet two is weeks of work carried out. This calculates the
average no
of weeks behind schedule.

I only use the tasks which are behind programme in table two. Hence
the
column 'Mark as Behind'. How do I programme excel, to use this
column,
to
take the tasks Marked as Behind into table two?

Regards
Dylan Dawson
Building Surveyor
Glasgow, Scotland

Hi Roger,

I want to link the info so that other users don't have to spend too much
time on it.

I have taken it as far as I can go using an If statement, but it still
doesn't meet my needs, because I want to use three conditions - two main
conditional statements and a null statement (if the field is blank),
something along the lines of;

=IF('Site Percentages'!A4="A",PRODUCT('Site Percentages'!D4,D6,2.01)," ")
If the A4 cell is A then multiply 'Site Percentages'!D4 by cell D6 and the
figure 2.01, if it is Bt hen multiply 'Site Percentages'!D4 by cell D6 and
the figure 0.01, or else leave the cell blank

The A and B conditions mean Ahead or Behind

I think the easy way out will be to create two columns.

Thanks for your assistance.


Regards
Dylan Dawson



"Roger Govier" wrote in message
...
Hi Dylan

Start with a blank new sheet.
Copy your Headings from your main sheet
In cell A2 enter =1
DataFilterAdvanced FilterSelect copy to new location
click on the icon in List Range and select the whole of the data range
(including headers) on Main sheet.
Click on icon in Filter Range and select A1:E2 of the new sheet
Click on Copy to and select cell A5 of new sheet
Click OK


All of the rows with 1 on Main sheet should appear on the new sheet.
Set any criteria in B2:E2 that you wish and repeat the process, and the
list will be those rows that satisfy all of the criteria set.


--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
I couldn't understand the criteria part of this.
My criteria would be to select items = 1 in column A and filter out
others.
How would I do this?

"Roger Govier" wrote in message
...
Hi Dylan

You could write VBA code to do it, but another alternative would be to
use Advanced Filter to bring the data across to you second sheet.
Take a look at Debra Dalgleish's site for some excellent instruction
on
how to do this.
http://www.contextures.com/xladvfilter01.html#ExtractWs
There are also some sample workbooks on the site you can download

--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
Roger,

Thanks for your quick response.

I understand the copy link procedure, but way you are suggesting will
involve a lot of manual time in excel for each survey. I am looking
for is
something which will only copylink the items that are flagged as
being
behind programme in the "Mark as Behind" column.

something along the lines of
If A5 is null do nothing
If A5 =1, copylink this row (or specific cells in this row) to
Worksheet 2.

The problem I see in making the copied rows consecutive. I wonder if
this
would involve a VBA routine where the routine works on the selected
items as
a single object?

This is my general understanding of the problem anyway.

Any suggestions?

Regards
Dylan Dawson


"Roger Govier" wrote in message
...
Hi Dylan

On Sheet2 in cell B2
=IF(Sheet1!$A2="","",Sheet1!B2)
Copy to C2 and E2:F2
Copy down as far as required

If your sheet names are not Sheet1 etc. but have spaces in the name,
enclose with single quotes e.g.
'Main Sheet'!$A2
--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
I am weakest in excel; I have an understanding of VBA in Word and I
have
attended an advanced Access course.

I have this problem in excel, I have no idea what track to take with
it,
Hence the cross posting between programming and worksheet functions.

This workshbook is to be used by our surveyors to input data from
site
inspections.

I have a table on one worksheet with the column headings:

Mark As Behind
Task No
Task Description
Contractor's Estimate
Our Estimate

I have a table on another worksheet with the column headings:

Mark As Behind
Task No
Task Description
Total Weeks for Task
Contractor's Estiamte
Our Estimate

Worksheet one is percentages of work carried out. We use this to
estimate
the overall progress.

Worksheet two is weeks of work carried out. This calculates the
average no
of weeks behind schedule.

I only use the tasks which are behind programme in table two. Hence
the
column 'Mark as Behind'. How do I programme excel, to use this
column,
to
take the tasks Marked as Behind into table two?

Regards
Dylan Dawson
Building Surveyor
Glasgow, Scotland

Hi Dylan

You can make that one formula
=D4*D6*CHOOSE((CODE(A4)-64),0.01,2.01)

If you like send me a copy of your workbook and a description of what
you want to see on the second sheet and I will set it up for you.
To send direct, leave NOSPAM out of my email address

--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
Hi Roger,

I want to link the info so that other users don't have to spend too
much
time on it.

I have taken it as far as I can go using an If statement, but it still
doesn't meet my needs, because I want to use three conditions - two
main
conditional statements and a null statement (if the field is blank),
something along the lines of;

=IF('Site Percentages'!A4="A",PRODUCT('Site Percentages'!D4,D6,2.01),"
")
If the A4 cell is A then multiply 'Site Percentages'!D4 by cell D6 and
the
figure 2.01, if it is Bt hen multiply 'Site Percentages'!D4 by cell D6
and
the figure 0.01, or else leave the cell blank

The A and B conditions mean Ahead or Behind

I think the easy way out will be to create two columns.

Thanks for your assistance.


Regards
Dylan Dawson



"Roger Govier" wrote in message
...
Hi Dylan

Start with a blank new sheet.
Copy your Headings from your main sheet
In cell A2 enter =1
DataFilterAdvanced FilterSelect copy to new location
click on the icon in List Range and select the whole of the data range
(including headers) on Main sheet.
Click on icon in Filter Range and select A1:E2 of the new sheet
Click on Copy to and select cell A5 of new sheet
Click OK


All of the rows with 1 on Main sheet should appear on the new sheet.
Set any criteria in B2:E2 that you wish and repeat the process, and
the
list will be those rows that satisfy all of the criteria set.


--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
I couldn't understand the criteria part of this.
My criteria would be to select items = 1 in column A and filter out
others.
How would I do this?

"Roger Govier" wrote in message
...
Hi Dylan

You could write VBA code to do it, but another alternative would be
to
use Advanced Filter to bring the data across to you second sheet.
Take a look at Debra Dalgleish's site for some excellent instruction
on
how to do this.
http://www.contextures.com/xladvfilter01.html#ExtractWs
There are also some sample workbooks on the site you can download

--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
Roger,

Thanks for your quick response.

I understand the copy link procedure, but way you are suggesting
will
involve a lot of manual time in excel for each survey. I am looking
for is
something which will only copylink the items that are flagged as
being
behind programme in the "Mark as Behind" column.

something along the lines of
If A5 is null do nothing
If A5 =1, copylink this row (or specific cells in this row) to
Worksheet 2.

The problem I see in making the copied rows consecutive. I wonder if
this
would involve a VBA routine where the routine works on the selected
items as
a single object?

This is my general understanding of the problem anyway.

Any suggestions?

Regards
Dylan Dawson


"Roger Govier" wrote in message
...
Hi Dylan

On Sheet2 in cell B2
=IF(Sheet1!$A2="","",Sheet1!B2)
Copy to C2 and E2:F2
Copy down as far as required

If your sheet names are not Sheet1 etc. but have spaces in the name,
enclose with single quotes e.g.
'Main Sheet'!$A2
--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
I am weakest in excel; I have an understanding of VBA in Word and I
have
attended an advanced Access course.

I have this problem in excel, I have no idea what track to take
with
it,
Hence the cross posting between programming and worksheet
functions.

This workshbook is to be used by our surveyors to input data from
site
inspections.

I have a table on one worksheet with the column headings:

Mark As Behind
Task No
Task Description
Contractor's Estimate
Our Estimate

I have a table on another worksheet with the column headings:

Mark As Behind
Task No
Task Description
Total Weeks for Task
Contractor's Estiamte
Our Estimate

Worksheet one is percentages of work carried out. We use this to
estimate
the overall progress.

Worksheet two is weeks of work carried out. This calculates the
average no
of weeks behind schedule.

I only use the tasks which are behind programme in table two. Hence
the
column 'Mark as Behind'. How do I programme excel, to use this
column,
to
take the tasks Marked as Behind into table two?

Regards
Dylan Dawson
Building Surveyor
Glasgow, Scotland

Roger

I've finished work for the weekend, but the suspense is killing me so to
speak.
I've never used the SELECT function, can you explain the term
=D4*D6*CHOOSE((CODE(A4)-64),0.01,2.01)

I'll be able to sleep knowing this :)

Have a good weekend
Dylan

"Roger Govier" wrote in message
...
Hi Dylan

You can make that one formula
=D4*D6*CHOOSE((CODE(A4)-64),0.01,2.01)

If you like send me a copy of your workbook and a description of what you
want to see on the second sheet and I will set it up for you.
To send direct, leave NOSPAM out of my email address

--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
Hi Roger,

I want to link the info so that other users don't have to spend too much
time on it.

I have taken it as far as I can go using an If statement, but it still
doesn't meet my needs, because I want to use three conditions - two main
conditional statements and a null statement (if the field is blank),
something along the lines of;

=IF('Site Percentages'!A4="A",PRODUCT('Site Percentages'!D4,D6,2.01)," ")
If the A4 cell is A then multiply 'Site Percentages'!D4 by cell D6 and
the
figure 2.01, if it is Bt hen multiply 'Site Percentages'!D4 by cell D6
and
the figure 0.01, or else leave the cell blank

The A and B conditions mean Ahead or Behind

I think the easy way out will be to create two columns.

Thanks for your assistance.


Regards
Dylan Dawson



"Roger Govier" wrote in message
...
Hi Dylan

Start with a blank new sheet.
Copy your Headings from your main sheet
In cell A2 enter =1
DataFilterAdvanced FilterSelect copy to new location
click on the icon in List Range and select the whole of the data range
(including headers) on Main sheet.
Click on icon in Filter Range and select A1:E2 of the new sheet
Click on Copy to and select cell A5 of new sheet
Click OK


All of the rows with 1 on Main sheet should appear on the new sheet.
Set any criteria in B2:E2 that you wish and repeat the process, and the
list will be those rows that satisfy all of the criteria set.


--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
I couldn't understand the criteria part of this.
My criteria would be to select items = 1 in column A and filter out
others.
How would I do this?

"Roger Govier" wrote in message
...
Hi Dylan

You could write VBA code to do it, but another alternative would be to
use Advanced Filter to bring the data across to you second sheet.
Take a look at Debra Dalgleish's site for some excellent instruction
on
how to do this.
http://www.contextures.com/xladvfilter01.html#ExtractWs
There are also some sample workbooks on the site you can download

--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
Roger,

Thanks for your quick response.

I understand the copy link procedure, but way you are suggesting will
involve a lot of manual time in excel for each survey. I am looking
for is
something which will only copylink the items that are flagged as
being
behind programme in the "Mark as Behind" column.

something along the lines of
If A5 is null do nothing
If A5 =1, copylink this row (or specific cells in this row) to
Worksheet 2.

The problem I see in making the copied rows consecutive. I wonder if
this
would involve a VBA routine where the routine works on the selected
items as
a single object?

This is my general understanding of the problem anyway.

Any suggestions?

Regards
Dylan Dawson


"Roger Govier" wrote in message
...
Hi Dylan

On Sheet2 in cell B2
=IF(Sheet1!$A2="","",Sheet1!B2)
Copy to C2 and E2:F2
Copy down as far as required

If your sheet names are not Sheet1 etc. but have spaces in the name,
enclose with single quotes e.g.
'Main Sheet'!$A2
--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
I am weakest in excel; I have an understanding of VBA in Word and I
have
attended an advanced Access course.

I have this problem in excel, I have no idea what track to take with
it,
Hence the cross posting between programming and worksheet functions.

This workshbook is to be used by our surveyors to input data from
site
inspections.

I have a table on one worksheet with the column headings:

Mark As Behind
Task No
Task Description
Contractor's Estimate
Our Estimate

I have a table on another worksheet with the column headings:

Mark As Behind
Task No
Task Description
Total Weeks for Task
Contractor's Estiamte
Our Estimate

Worksheet one is percentages of work carried out. We use this to
estimate
the overall progress.

Worksheet two is weeks of work carried out. This calculates the
average no
of weeks behind schedule.

I only use the tasks which are behind programme in table two. Hence
the
column 'Mark as Behind'. How do I programme excel, to use this
column,
to
take the tasks Marked as Behind into table two?

Regards
Dylan Dawson
Building Surveyor
Glasgow, Scotland

Hi Dylan

CODE() returns the ASCII code value of a character, and the CODE(A) is
65, CODE(B) is 66.
Taking 64 away gives a value of 1 or 2.
CHOOSE(1,0.01,2.1) will return the first term in the series, 0.01 so
that will be the result for A in A4 and 2.1 will be the result for B in
A4

--
Regards

Roger Govier


"DD" wrote in message
...
Roger

I've finished work for the weekend, but the suspense is killing me so
to speak.
I've never used the SELECT function, can you explain the term
=D4*D6*CHOOSE((CODE(A4)-64),0.01,2.01)

I'll be able to sleep knowing this :)

Have a good weekend
Dylan

"Roger Govier" wrote in message
...
Hi Dylan

You can make that one formula
=D4*D6*CHOOSE((CODE(A4)-64),0.01,2.01)

If you like send me a copy of your workbook and a description of what
you want to see on the second sheet and I will set it up for you.
To send direct, leave NOSPAM out of my email address

--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
Hi Roger,

I want to link the info so that other users don't have to spend too
much
time on it.

I have taken it as far as I can go using an If statement, but it
still
doesn't meet my needs, because I want to use three conditions - two
main
conditional statements and a null statement (if the field is blank),
something along the lines of;

=IF('Site Percentages'!A4="A",PRODUCT('Site
Percentages'!D4,D6,2.01)," ")
If the A4 cell is A then multiply 'Site Percentages'!D4 by cell D6
and the
figure 2.01, if it is Bt hen multiply 'Site Percentages'!D4 by cell
D6 and
the figure 0.01, or else leave the cell blank

The A and B conditions mean Ahead or Behind

I think the easy way out will be to create two columns.

Thanks for your assistance.


Regards
Dylan Dawson



"Roger Govier" wrote in message
...
Hi Dylan

Start with a blank new sheet.
Copy your Headings from your main sheet
In cell A2 enter =1
DataFilterAdvanced FilterSelect copy to new location
click on the icon in List Range and select the whole of the data
range
(including headers) on Main sheet.
Click on icon in Filter Range and select A1:E2 of the new sheet
Click on Copy to and select cell A5 of new sheet
Click OK


All of the rows with 1 on Main sheet should appear on the new sheet.
Set any criteria in B2:E2 that you wish and repeat the process, and
the
list will be those rows that satisfy all of the criteria set.


--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
I couldn't understand the criteria part of this.
My criteria would be to select items = 1 in column A and filter out
others.
How would I do this?

"Roger Govier" wrote in message
...
Hi Dylan

You could write VBA code to do it, but another alternative would be
to
use Advanced Filter to bring the data across to you second sheet.
Take a look at Debra Dalgleish's site for some excellent
instruction
on
how to do this.
http://www.contextures.com/xladvfilter01.html#ExtractWs
There are also some sample workbooks on the site you can download

--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
Roger,

Thanks for your quick response.

I understand the copy link procedure, but way you are suggesting
will
involve a lot of manual time in excel for each survey. I am
looking
for is
something which will only copylink the items that are flagged as
being
behind programme in the "Mark as Behind" column.

something along the lines of
If A5 is null do nothing
If A5 =1, copylink this row (or specific cells in this row) to
Worksheet 2.

The problem I see in making the copied rows consecutive. I wonder
if
this
would involve a VBA routine where the routine works on the
selected
items as
a single object?

This is my general understanding of the problem anyway.

Any suggestions?

Regards
Dylan Dawson


"Roger Govier" wrote in message
...
Hi Dylan

On Sheet2 in cell B2
=IF(Sheet1!$A2="","",Sheet1!B2)
Copy to C2 and E2:F2
Copy down as far as required

If your sheet names are not Sheet1 etc. but have spaces in the
name,
enclose with single quotes e.g.
'Main Sheet'!$A2
--
Regards

Roger Govier


"dd" <dd.dd wrote in message
...
I am weakest in excel; I have an understanding of VBA in Word and
I
have
attended an advanced Access course.

I have this problem in excel, I have no idea what track to take
with
it,
Hence the cross posting between programming and worksheet
functions.

This workshbook is to be used by our surveyors to input data from
site
inspections.

I have a table on one worksheet with the column headings:

Mark As Behind
Task No
Task Description
Contractor's Estimate
Our Estimate

I have a table on another worksheet with the column headings:

Mark As Behind
Task No
Task Description
Total Weeks for Task
Contractor's Estiamte
Our Estimate

Worksheet one is percentages of work carried out. We use this to
estimate
the overall progress.

Worksheet two is weeks of work carried out. This calculates the
average no
of weeks behind schedule.

I only use the tasks which are behind programme in table two.
Hence
the
column 'Mark as Behind'. How do I programme excel, to use this
column,
to
take the tasks Marked as Behind into table two?

Regards
Dylan Dawson
Building Surveyor
Glasgow, Scotland