Hi All! Just joined so forgive me if I do something against protocol here!
My question is. I have sheet1 of data, with sheet4 having formulas of +'sheet1'!B4 in a cell, then +'sheet1'!G4, Then +'sheet1'!M4, etc. Basically using the sheet1 data to "fill in a form" created on sheet4. I need to copy that sheet4 and have the formulas also now reference the next row in sheet1. So B5, G5, M5. Hope this makes sense! IF someone can point me in the right direction I would appreciate it!
Hi, and welcome to the forum
If the formula always points back to B4 on sheet1 then once you change B4 any formula will also change.
You'll need to copy the row 4 cells on sheet1 and paste them as values to the next row on sheet4. A macro could perform that task automatically
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Another alternative is using INDEX on Sheet1 and a cell to control the row number.
For example, for Sheet1!D2, i.e. for Row 2 you can use =INDEX(Sheet1!1:1048576,Sheet4!$J$1,4) where Sheet4!$J$1=2
Note: you can use a copy of Sheet4 for a new sheet with row 3 or use the same sheet Sheet4 with Sheet4!$J$1=3
See the file for clarification
Must admit I had interpeted the requirement was to enter a new record on sheet4 and have it ADDED to a database on sheet1, rather than look for a value IN a database on sheet1.
Maybe the OP will clarify the requirement
Correct. All of the formulas are entered in sheet 1 already.
So I have a list of names in sheet1, columnA is a list of names, columnB is blank and awaiting entry of a sales amount, columns C,D,E,F are formulas and results.
Sheet2 is formatted like a report with various cells referencing data in sheet1, Row 2. I need to copy sheet 2 to a new sheet(sheet3) and have all the referencing cells increment to row3 on sheet1
Perhaps it would be best to illustrate what you need by uploading a fictionalized workbook. Be sure to manually include the output you expect to see on sheets 2, 3 and 4(?) based on the data placed in sheet 1 so that we will have something against which to compare the results of our proposed formulas/code.
To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks