• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Remark update in another sheet

dingdang

dingdang

Member
Hi,

I have customer database in "data"sheet ( will be hide for user ).and second sheet as "form" which get same data in different format from "data"sheet by using offset formula and linked with scroll bar button to get next record.

user will update the 1st /2nd/3rd remark in B20/B21 & B22 in "form" sheet which should be copy in "data" sheet against particular row in colomn Z / AA & AB respectively.

after click on the next scroll button for next record again remark colomn should be blank for new updation for next record.

attached sample file for your ref. Pls help.
 

Attachments

  • Dummy (1).xlsx
    18.9 KB · Views: 10
The above spreadsheet could use a lot of help with its set up. You should use one row for headings and you should not merge cells under any circumstances. This makes the formula more complex. I would suggest unmerging the header row on the Data tab for starters. Those headers easily fit in one row without stretching or anything. Once you have done that this formula in B20 on the form sheet.

=OFFSET(Data!$Z$1,$A$4,0,1,1)

Your file needs a lot of TLC as it steers well clear of something that is pliable.

Take care

Smallman
 
Smallman,

Have unmerge all header row on the data sheet as suggested.but my requirement is remark updated in "Form"B20,B21,B22 need to updated in Data sheet in colomn Z to AB for each different record.

Pls guide.
 
OK so I unmerged the headers and the formula above worked for me. Just paste that Offset formula into the first line of the Remarks section. It will work. Break the formula down if you have a different set up and it will work for you too.

Take care

Smallman
 
Smallman,

sorry..its not what i required.. if remark inputed in B20 in form sheet for sr. no.3 should update the same in sheet DATA in Z4 and so on....just like vlookup.
 
Hi dd

I am sorry about the confusion on my part. I misread your post. You will need vb to achieve this. Here is the basic code but you will need to add this to a worksheet change event or a button.

Code: 
Sub testo()
Dim ar As Variant
ar = Range("B20:B22")
    Sheet1.Cells(Cells(4, 1) + 1, 26).Resize(, 2) = ar
End Sub

Hope this gets you closer.

Smallman
 
You must log in or register to reply here.
Back
Top