Selective linking items between two worksheets

[dd]

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 Govier]

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

 

[dd]

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


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

 

[Roger Govier]

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

 

[dd]

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?

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

 

[Roger Govier]

Hi Dylan

Start with a blank new sheet.
Copy your Headings from your main sheet
In cell A2 enter =1
Data>Filter>Advanced Filter>Select 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.

 

[dd]

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



Hi Dylan

Start with a blank new sheet.
Copy your Headings from your main sheet
In cell A2 enter =1
Data>Filter>Advanced Filter>Select 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.

 

[Roger Govier]

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

 

[DD]

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]

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