Copy Cell (Only Value) to different Cell

Hi all, I have a table that copies data from another cell on the page (using formula =C1) and the cell next to it is (=C3) and the cell next to that is (=B1). So i have to manually click on c1, c3, b1 every time i have new data to add to this table.

Is there a way i can click on =c1 and it copies c3 and b1 at the same time (keeping in mind c1 is always the main cell i click on, and the number is c3 will always be 2 cells to the right and b1 will always be 1 cell below)

Is there a way of doing this?


Thanks so much!

Hi Joseph,

I don't understand what you're asking.

Do you need an OFFSET FORMULA? Instead of =C1, =C3, =B1? That would be =OFFSET($C1,0,0), =OFFSET($C1,2,0), and =OFFSET($C1,0,-1).

I am unsure about why you need to click on all cells, so please post a sample of the model. It is often difficult to put your needs into words.

I hope this helps, please let me know!

Regards,

David

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

Hi David, thankyou, i have attached my table here,

frmula.xlsx

the table is a summary of the rota, the intention is to click the name and the time and date are also inserted into the adjacent cell.

Thankyou

Offset formula may just be the thing, but im not sure how to implement it, because i have a few cells (above & below each other) which i would like to copy into a few cells (to the right of each other).

Can any one get me started?

Thankyou

Sorry Joseph,

I started looking at your problem, and had to put it to one side.

I have a few questions.

1. Would it be possible to put the day of the week on row 1? Then the formulas will be consistent.
2. A very wise contributer to this site has two rules. 1. Never use Merge & Center 2. ALWAYS follow rule 1.
Can we get rid of the M&C formatting? You should always use Centre across Selection
3. You seem to have a FIVE WEEK grid. What happens next month? Will Monday and Tuesday be blank?
4. On the JOHNN sheet, if you change the formula in D13 to ='My Hours'!A3, you see JOHN. (Refer to comment 2 above). BUT, what do you mean "CLICK ON IT"?
5. You only have 9 rows in the tables. Why? Could you have a row for each workday in the month, then show the detail if JOHN or TRACY worked?
6. Do you work weekends?

That's a lot of questions for now. More later no doubt!

David

Hi David, thankyou for your time, much appreciated

I have made a few changes as per yoour recommendations, and also changed the layout to simplify.



In answer to your questions
1. Would it be possible to put the day of the week on row 1? Then the formulas will be consistent. yes the days can go on row 1 frmula for edit.xlsx
2. A very wise contributer to this site has two rules. 1. Never use Merge & Center 2. ALWAYS follow rule 1. that is a great rule and i understand why, i have unmerged a few cells and it make the = function work better (how to we make it more aesthetically pleasing
Can we get rid of the M&C formatting? You should always use Centre across Selection done :-)
3. You seem to have a FIVE WEEK grid. What happens next month? Will Monday and Tuesday be blank? each excel workbook will contain 1 month, 4/5 weeks in each month. if there is only 4 weeks in a month then only 4 weeks will be filled. If 1/aug 2/aug falls in week 4 of the workbook they will be included in this workbook, so in the next workbook there it will start from 3/aug (example)
4. On the JOHNN sheet, if you change the formula in D13 to ='My Hours'!A3, you see JOHN. (Refer to comment 2 above). BUT, what do you mean "CLICK ON IT"?iin the new xls attached, the red box corresponds to 1 line in site 1 sheet. So if i could just click on 1 of the boxes in the red outlined box, it will also copy over the DATE, DAY, NAME AND TIMES that correspond to the clicked box
5. You only have 9 rows in the tables. Why? Could you have a row for each workday in the month, then show the detail if JOHN or TRACY worked? there will be more rows in the table, this is just to see f i can get a way for my idea to actually work then i will create the whole workbook according to the number of rows needed etc
6. Do you work weekends? yes and weekends.

Hope this helps

Can't see attachment! Will look at it tomorrow I hope

DAC

Hi Joseph,

This looks quite complex to me to do with formulas, however, have you considered a Pivot Table?

I could do a macro to generate a PT friendly table (as shown in AE1:AK43). You could then generate your own Pivot Table (see my Pivot Table Example).

Would this be acceptable? If so, I could have a go in the next few days (I'm struggling to get over a cold at the moment, and well below par).

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

Hi David, thankyou for your time with you are not well. Hope you feeling better.

I have read up on Pivot tables, although the possibilities are endless with it and it is a great tool, thankyou for introducing it to me, in the instance we would need that small manual task of selecting which shift to place.

In respect to that i have started playing with offset formulas, (attached) i have got part of it to work, were i would type a cell reference for the shift and it would place the relevant data corresponding to that shift but a little hicup in the plan... From the table to you will notice it works fine for shifts in the first set of rows but when it comes to the next the cell offset needs to go to the top of the page, any ideas? (i have added comments to the cells in question)

thankyou

Hi Joseph, I was considering OFFSET myself. Unfortunately, I an tied up tomorrow, but will look at it on Friday. Hopefully someone else might pick up the thread and assist you before then.

David

Thankyou David, i am a novice with macros, but if you think it can be accomplished with then that would be great, so long as we can choose the shift we want to choose and to which sheet we will be placing it to (if that makes any sense)

Hello Joseph,

I really like newdoverman's elegant array formula, so I have used his formula, and combined it with a macro that produces the Proposal sheet.

I also made a change to the layout of the WEEK sheet - eliminating all the blank rows.

This is just a start, but give it some thought.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

hi Newdoverman, thankyou, we have updated the old table to a new one, please see attached,


Not sure how your much appreciated recommendation will function, could you elaborate?
However Newdoverman you have given me an idea, taking how you have named the coloums & taking this formula "=TEXT(DAY(Table1[[#This Row],[Date]])+1,"dddd")" specifically "#This Row". Could we use an OFFSET formula, rename the main date and day rows to "Date" & "Day", use a similar pointer like "#This Row" to point to "Date" & "Day"

I hope you understand my thinking.

Thankyoujoseph_pat - frmula for edit.xlsx

Hi David,

Thankyou for your reply, your proposition is a sound one, eliminating the rows will make it slightly tricky as they would contain other notes etc which is a must. (Also a person could be working 2 locations on the same day which will be at different times in the day).

Would the macro work for a few weeks in the same sheet or will we have to separate each week into a different workbook/sheet?

Would you be kind to breakdown the macro you have created so i may understand it better:

Sub ListData()
Dim tsArray()
ReDim tsArray(1 To 500, 1 To 7)
tsAr = 1

For c = 2 To 29 Step 4
For r = 1 To 10
If IsDate(Cells(r, c)) Then
dte = Cells(r, c)
r = r + 1
End If

If IsEmpty(Cells(r, c)) Then GoTo 100

If Not IsDate(Cells(r, c)) Then
tsArray(tsAr, 1) = dte
tsArray(tsAr, 2) = dte
tsArray(tsAr, 3) = Cells(r, 1)
tsArray(tsAr, 4) = Cells(r, c)
tsArray(tsAr, 5) = Cells(r, c + 1)
tsArray(tsAr, 6) = Cells(r, c + 2)
tsArray(tsAr, 7) = Cells(r, c + 3)
tsAr = tsAr + 1
End If
100:
Next r

Next c
Sheets("Proposal").Select
Range("A2:G500") = tsArray

End Sub


Thankyou

Hi Joseph,

It is possible to work with multiple weeks. Also with multiple rows, but I was working with the data you posted, and there was no indication of multiple shifts or notes. I need to see how they would work, and what you want extracted to the summary.

Please update the workbook with more detail of likely scenarios. (BTW: do you work night shifts? Counting the hours will need a different formula if, say, you start at 8pm and finish as 6am).

As for the macro, I will place a few notes in it next time I post, but it is quite simple.

It creates an array (table) which is 500 rows by 7 columns. (A large array to accommodate a lot of data).

It then works down the worksheet day by day, and if there is data, stores it on the next row of the array. When finished working through the seven days, it writes the data to the PROPOSAL sheet.

If you want to keep the extra rows, a few extra IF statements will be needed, but it still can be done.

Looking forward to your next example! We'll get there!

Regards,

David

Hi David, thankyou that is much appreciated and you have been a great help, and hopefully i will now understand a little about macros.

I have attached a complete month of August. So long as we can understand a macro to create lines then i will be able to use offsets to choose which shift to which sheet :D

Thankyou

formila for davd.xlsx

Hi Joseph,

You have changed the layout again! Now there are 5 columns in each day.

No matter - the macro is easily changed to accommodate this! You will note that I have placed a comment on almost every row of the macro to explain what is happening. I hope this helps.

NOTE! Always make sure that there is shift data against a name in column A, or the name won't be picked up. This does not matter where a person works two shifts on the one day. However, on row 37, you had some data which I moved to row 34 so that the staff name would be recorded.

Also note that you have inserted an extra column in each day for some reason. This will always be ignored.

I assume you can combine this into the other workbook where the Array Formulas have been used to create the tables by site.

Finally, I have ignored the second team. I think it would be better to have this on another sheet rather than way out to the right. It would then be a simple matter to run another macro. All you would have to do is change the name of the sheet to start on, and the addresses of where to write the data on the PROPOSAL sheet.

All the best!

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

If several people have responded, when you reply please make it clear WHO you are responding
to by mentioning their name.

Hi David, thankyou for your help,
i keep getting a error: "compile error sub or function not defined" and a yellow arrow point to "Sub ListData()"

Originally Posted by joseph_pat

Hi David, thankyou for your help,
i keep getting a error: "compile error sub or function not defined" and a yellow arrow point to "Sub ListData()"

Sorry, A typo crept in. Replace with this:

Sub ListData()
Sheets("WEEKS").Select

Dim tsArray() 'Declare the Time Sheet Array
Dim sRange As Range 'Declare the sort range (for array output)
Dim sKey As Range 'Declare the sort key (for array output)
ReDim tsArray(1 To 500, 1 To 7) 'Define the dimensions of array - 500 rows X 7 columns
tsAr = 1 'First row of array
head = Cells(1, 3) 'Heading found on row 1
LastRow = Cells(Rows.Count, "A").End(xlUp).Row 'Find the last row on the WEEKS sheet
For c = 3 To 37 Step 5 'Loop through days - increase by 5 columns each loop
    
    For r = 2 To LastRow + 5 'Loop through every row on the sheet.
        If Cells(r, 3) = head Then GoTo 100 'Ignore rows which match the heading
        If IsDate(Cells(r, c)) Then 'If there is a date, record it, and skip the next row.
            dte = Cells(r, c)
            r = r + 2
        End If
        
        If IsEmpty(Cells(r, c)) Then GoTo 100 'If the cell is empty, go to the next row.
        
        If Not IsDate(Cells(r, c)) Then 'If the cell is does not contain a date, it must be
            tsArray(tsAr, 1) = dte      'data to be recorded, so store the date twice. 1 is date
            tsArray(tsAr, 2) = dte      '2 is day.
            tsArray(tsAr, 3) = Cells(r, 1) 'Get the name from column 1
            tsArray(tsAr, 4) = Cells(r, c) 'Get the site from current column
            tsArray(tsAr, 5) = Cells(r, c + 2) 'Get the start time from 2 cells to the right
            tsArray(tsAr, 6) = Cells(r, c + 3) 'Get the finish time from 3 cells to the right
            tsArray(tsAr, 7) = Cells(r, c + 4) 'Get the total hours from 4 cells to the right
            tsAr = tsAr + 1 'Increase the array row number by 1
        End If
100:
    Next r 'Move to the next row (this is repeated until Last Row is reached

Next c 'Move to the next day - increase column number by 5

'When all days have been worked on - write the result to the PROPOSAL sheet.
Sheets("Proposal").Select '
    [A1] = head 'Place the heading at the top of the sheet
    Range("A3:G500").ClearContents 'Clear any previous data
    Range("A3:G500") = tsArray 'Write the data in the array
    Set sRange = Range("A2:G500") 'Assign the Range for sorting
    Set sKey = Range("A3") 'Assign the sort key
    sRange.Sort Key1:=sKey, Order1:=xlAscending, Header:=xlYes 'Sort the data by date

End Sub

DAC

Hi David, thankyou your help has been much appreciated. Your macro is helping in listing the data and the steps after we can do manually

once again thankyou