Reference formula for non adjacent cells
worksheet 1 is a data table; 2 columns. worksheet 2 is a report. Worksheet 2 has 75 rows of data between entries. here is what happens when I do the auto fill. =worksheet1A1 (good)=worksheet1A76 (bad)
since the data isn't in vertically adjacent cells, the auto fill function just isn't working.
I need cell worksheet2A76 to reference worksheet1A2 and so forth
I hope this is clear, I am extremely frustrated. Please help. thank you
Do you mix up Worksheets?
andWorksheet 2 has 75 rows of data between entriesI think what you mean is that WS1 has the data with 75 rows in between and you need to reference to Ws1!1, Ws1!76, Ws1!151, etc. Right?=worksheet1A1 (good) =worksheet1A76
What's in worksheet1!A2 till worksheet1!A75 ? Empty ?
Looking for great solutions but hate waiting?
Seach this Forum through Google
www.Google.com (e.g. +multiple +IF site:excelforum.com/excel-general/ )
www.Google.com (e.g. +fill +combobox site:excelforum.com/excel-programming/ )
Ave,
Ricardo
Re: Reference formula for non adjacent cells
Worksheet 2 has 450 lines of data. Worksheet 1 is my report. Worksheet1A1 is a name entry, this name needs to come from worksheet 2.
The name entries are separated by 75 rows of other relevant data.
In worksheet 2A1=worksheet1A1 good
worksheet 2A76=worksheet1A76 (no I need worksheet1A2)
I hope this clearer.
Clearer, but i must state your naming is very confusing and in your case it is not helping.
Upload your WB, put values in you require and we'll do it for you. This is more efficient for both of us.
Looking for great solutions but hate waiting?
Seach this Forum through Google
www.Google.com (e.g. +multiple +IF site:excelforum.com/excel-general/ )
www.Google.com (e.g. +fill +combobox site:excelforum.com/excel-programming/ )
Ave,
Ricardo
I think you need something like this in your sheet2!A1 and sheet2!A76 etc:
=INDIRECT("'worksheet1'!A"&((ROW()-1)/75)+1)
copy this into A76, A151, etc...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks