I have a project consisting of a "job sheet" and a "work details" sheet for
each day (jobsheet - Monday, Work Details - Monday etc). I am trying to take
all information from the jobsheet with job code "EOD" in "N6" and put all the
information on the work detail sheet with the job code being in "G14". I
also then need to get the Reg No from "F6" and other work carried out from
the same row, and put this on the work details sheet too with Reg No going in
"E14". It is so long since I have done anything like this, and I have
forgottoen so much. Is this better with formulas in each cell on the Work
Details sheet, or using a Macro? Can anyone please help?

There are advantages in using macros and there are advantages in using
formulas. The main difference is the formulas automatically get updated
while most macros don't (event macros will automatically get called but these
are not needed in this case).

There are two types of macros. The first is a function (sometimes refered
to UDF) which works just like a worksheet function such as =sum(A5:A10). The
second type are SUB which is a software which can access your worksheet cells.

When to use each. Functions cannot move data to a cell they can only return
one value to a cell. If you need to move a row of data, then you need to use
a subroutine. When to use a worksheet function verses a UDF. I use UDF when
the function starts to get complicated because I find it is much easier to to
debug and you can do a lot more with UDF then worksheet functions.

For simple things use worksheet functions. When you are repeatively doing
the same thing day after day and it takes a lot of time to set up the
formulas then start looking at a macro.

I need more details to help you with your present problem. It is confusing
when you say you need to do a lookup and reference a single cell. Lookups
are when you are looking for data in a range of cells such as a row, column,
or area (A1:D7).

to reference a single cell all you need to do is use the equal sign.

='work details'!A7


"izzyt1972" wrote:

I have a project consisting of a "job sheet" and a "work details" sheet for
each day (jobsheet - Monday, Work Details - Monday etc). I am trying to take
all information from the jobsheet with job code "EOD" in "N6" and put all the
information on the work detail sheet with the job code being in "G14". I
also then need to get the Reg No from "F6" and other work carried out from
the same row, and put this on the work details sheet too with Reg No going in
"E14". It is so long since I have done anything like this, and I have
forgottoen so much. Is this better with formulas in each cell on the Work
Details sheet, or using a Macro? Can anyone please help?

Hi Joel.

I have job codes in column N6 to N29 on the "Job Sheet", that if the job
code is "EOD" then the data in the corresponding row needs to be taken into
the "Work Details" sheet. There could be various job codes on the jobsheet,
but at present I am only interested in the "EOD" codes. The important part is
the "Reg No" which has to be included and is located in column F6 to F29 on
the Job Sheet and E14 to E27 on the Work Details sheet. When there is a Reg
No with an EOD job code, then there are 19 other columns that have possible
work done that will need to have details added, but I should be able to do
that with a "Lookup" formula. If you need to know more, then please let me
know.
Many Thanks, Iain

"Joel" wrote:

There are advantages in using macros and there are advantages in using
formulas. The main difference is the formulas automatically get updated
while most macros don't (event macros will automatically get called but these
are not needed in this case).

There are two types of macros. The first is a function (sometimes refered
to UDF) which works just like a worksheet function such as =sum(A5:A10). The
second type are SUB which is a software which can access your worksheet cells.

When to use each. Functions cannot move data to a cell they can only return
one value to a cell. If you need to move a row of data, then you need to use
a subroutine. When to use a worksheet function verses a UDF. I use UDF when
the function starts to get complicated because I find it is much easier to to
debug and you can do a lot more with UDF then worksheet functions.

For simple things use worksheet functions. When you are repeatively doing
the same thing day after day and it takes a lot of time to set up the
formulas then start looking at a macro.

I need more details to help you with your present problem. It is confusing
when you say you need to do a lookup and reference a single cell. Lookups
are when you are looking for data in a range of cells such as a row, column,
or area (A1:D7).

to reference a single cell all you need to do is use the equal sign.

='work details'!A7


"izzyt1972" wrote:

I have a project consisting of a "job sheet" and a "work details" sheet for
each day (jobsheet - Monday, Work Details - Monday etc). I am trying to take
all information from the jobsheet with job code "EOD" in "N6" and put all the
information on the work detail sheet with the job code being in "G14". I
also then need to get the Reg No from "F6" and other work carried out from
the same row, and put this on the work details sheet too with Reg No going in
"E14". It is so long since I have done anything like this, and I have
forgottoen so much. Is this better with formulas in each cell on the Work
Details sheet, or using a Macro? Can anyone please help?

Sub Get_EOD()

'clear cells
With Sheets("Work Details")
.Range("E14:E27").Clear
End With
WorkRowCount = 14
For JobRowCount = 6 To 29
With Sheets("Job Sheet")
If .Range("N" & JobRowCount) = "EOD" Then
RegNo = .Range("F" & JobRowCount)
With Sheets("Work Details")
.Range("E" & WorkRowCount) = RegNo
End With
WorkRowCount = WorkRowCount + 1
End If
End With
Next JobRowCount
End Sub


"izzyt1972" wrote:

Hi Joel.

I have job codes in column N6 to N29 on the "Job Sheet", that if the job
code is "EOD" then the data in the corresponding row needs to be taken into
the "Work Details" sheet. There could be various job codes on the jobsheet,
but at present I am only interested in the "EOD" codes. The important part is
the "Reg No" which has to be included and is located in column F6 to F29 on
the Job Sheet and E14 to E27 on the Work Details sheet. When there is a Reg
No with an EOD job code, then there are 19 other columns that have possible
work done that will need to have details added, but I should be able to do
that with a "Lookup" formula. If you need to know more, then please let me
know.
Many Thanks, Iain

"Joel" wrote:

There are advantages in using macros and there are advantages in using
formulas. The main difference is the formulas automatically get updated
while most macros don't (event macros will automatically get called but these
are not needed in this case).

There are two types of macros. The first is a function (sometimes refered
to UDF) which works just like a worksheet function such as =sum(A5:A10). The
second type are SUB which is a software which can access your worksheet cells.

When to use each. Functions cannot move data to a cell they can only return
one value to a cell. If you need to move a row of data, then you need to use
a subroutine. When to use a worksheet function verses a UDF. I use UDF when
the function starts to get complicated because I find it is much easier to to
debug and you can do a lot more with UDF then worksheet functions.

For simple things use worksheet functions. When you are repeatively doing
the same thing day after day and it takes a lot of time to set up the
formulas then start looking at a macro.

I need more details to help you with your present problem. It is confusing
when you say you need to do a lookup and reference a single cell. Lookups
are when you are looking for data in a range of cells such as a row, column,
or area (A1:D7).

to reference a single cell all you need to do is use the equal sign.

='work details'!A7


"izzyt1972" wrote:

I have a project consisting of a "job sheet" and a "work details" sheet for
each day (jobsheet - Monday, Work Details - Monday etc). I am trying to take
all information from the jobsheet with job code "EOD" in "N6" and put all the
information on the work detail sheet with the job code being in "G14". I
also then need to get the Reg No from "F6" and other work carried out from
the same row, and put this on the work details sheet too with Reg No going in
"E14". It is so long since I have done anything like this, and I have
forgottoen so much. Is this better with formulas in each cell on the Work
Details sheet, or using a Macro? Can anyone please help?

sorry, but for some reason this is not working. Would it be better if I
sent you a sample of the sheets?

"Joel" wrote:

Sub Get_EOD()

'clear cells
With Sheets("Work Details")
.Range("E14:E27").Clear
End With
WorkRowCount = 14
For JobRowCount = 6 To 29
With Sheets("Job Sheet")
If .Range("N" & JobRowCount) = "EOD" Then
RegNo = .Range("F" & JobRowCount)
With Sheets("Work Details")
.Range("E" & WorkRowCount) = RegNo
End With
WorkRowCount = WorkRowCount + 1
End If
End With
Next JobRowCount
End Sub


"izzyt1972" wrote:

Hi Joel.

I have job codes in column N6 to N29 on the "Job Sheet", that if the job
code is "EOD" then the data in the corresponding row needs to be taken into
the "Work Details" sheet. There could be various job codes on the jobsheet,
but at present I am only interested in the "EOD" codes. The important part is
the "Reg No" which has to be included and is located in column F6 to F29 on
the Job Sheet and E14 to E27 on the Work Details sheet. When there is a Reg
No with an EOD job code, then there are 19 other columns that have possible
work done that will need to have details added, but I should be able to do
that with a "Lookup" formula. If you need to know more, then please let me
know.
Many Thanks, Iain

"Joel" wrote:

There are advantages in using macros and there are advantages in using
formulas. The main difference is the formulas automatically get updated
while most macros don't (event macros will automatically get called but these
are not needed in this case).

There are two types of macros. The first is a function (sometimes refered
to UDF) which works just like a worksheet function such as =sum(A5:A10). The
second type are SUB which is a software which can access your worksheet cells.

When to use each. Functions cannot move data to a cell they can only return
one value to a cell. If you need to move a row of data, then you need to use
a subroutine. When to use a worksheet function verses a UDF. I use UDF when
the function starts to get complicated because I find it is much easier to to
debug and you can do a lot more with UDF then worksheet functions.

For simple things use worksheet functions. When you are repeatively doing
the same thing day after day and it takes a lot of time to set up the
formulas then start looking at a macro.

I need more details to help you with your present problem. It is confusing
when you say you need to do a lookup and reference a single cell. Lookups
are when you are looking for data in a range of cells such as a row, column,
or area (A1:D7).

to reference a single cell all you need to do is use the equal sign.

='work details'!A7


"izzyt1972" wrote:

I have a project consisting of a "job sheet" and a "work details" sheet for
each day (jobsheet - Monday, Work Details - Monday etc). I am trying to take
all information from the jobsheet with job code "EOD" in "N6" and put all the
information on the work detail sheet with the job code being in "G14". I
also then need to get the Reg No from "F6" and other work carried out from
the same row, and put this on the work details sheet too with Reg No going in
"E14". It is so long since I have done anything like this, and I have
forgottoen so much. Is this better with formulas in each cell on the Work
Details sheet, or using a Macro? Can anyone please help?

you can email me the file at

"izzyt1972" wrote:

sorry, but for some reason this is not working. Would it be better if I
sent you a sample of the sheets?

"Joel" wrote:

Sub Get_EOD()

'clear cells
With Sheets("Work Details")
.Range("E14:E27").Clear
End With
WorkRowCount = 14
For JobRowCount = 6 To 29
With Sheets("Job Sheet")
If .Range("N" & JobRowCount) = "EOD" Then
RegNo = .Range("F" & JobRowCount)
With Sheets("Work Details")
.Range("E" & WorkRowCount) = RegNo
End With
WorkRowCount = WorkRowCount + 1
End If
End With
Next JobRowCount
End Sub


"izzyt1972" wrote:

Hi Joel.

I have job codes in column N6 to N29 on the "Job Sheet", that if the job
code is "EOD" then the data in the corresponding row needs to be taken into
the "Work Details" sheet. There could be various job codes on the jobsheet,
but at present I am only interested in the "EOD" codes. The important part is
the "Reg No" which has to be included and is located in column F6 to F29 on
the Job Sheet and E14 to E27 on the Work Details sheet. When there is a Reg
No with an EOD job code, then there are 19 other columns that have possible
work done that will need to have details added, but I should be able to do
that with a "Lookup" formula. If you need to know more, then please let me
know.
Many Thanks, Iain

"Joel" wrote:

There are advantages in using macros and there are advantages in using
formulas. The main difference is the formulas automatically get updated
while most macros don't (event macros will automatically get called but these
are not needed in this case).

There are two types of macros. The first is a function (sometimes refered
to UDF) which works just like a worksheet function such as =sum(A5:A10). The
second type are SUB which is a software which can access your worksheet cells.

When to use each. Functions cannot move data to a cell they can only return
one value to a cell. If you need to move a row of data, then you need to use
a subroutine. When to use a worksheet function verses a UDF. I use UDF when
the function starts to get complicated because I find it is much easier to to
debug and you can do a lot more with UDF then worksheet functions.

For simple things use worksheet functions. When you are repeatively doing
the same thing day after day and it takes a lot of time to set up the
formulas then start looking at a macro.

I need more details to help you with your present problem. It is confusing
when you say you need to do a lookup and reference a single cell. Lookups
are when you are looking for data in a range of cells such as a row, column,
or area (A1:D7).

to reference a single cell all you need to do is use the equal sign.

='work details'!A7


"izzyt1972" wrote:

I have a project consisting of a "job sheet" and a "work details" sheet for
each day (jobsheet - Monday, Work Details - Monday etc). I am trying to take
all information from the jobsheet with job code "EOD" in "N6" and put all the
information on the work detail sheet with the job code being in "G14". I
also then need to get the Reg No from "F6" and other work carried out from
the same row, and put this on the work details sheet too with Reg No going in
"E14". It is so long since I have done anything like this, and I have
forgottoen so much. Is this better with formulas in each cell on the Work
Details sheet, or using a Macro? Can anyone please help?