• 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.

Needing help using consecutive references

L

LWROZANS

New Member
I have a reference sheet which contains names and serial numbers and a rather large spreadsheet with a format that cannot be changed due to it being a legal document. What I mean by large is that there are rows and columns with arbitrary information ("NAME", "ITEM TYPE", etc.). When I attempt to get the reference information onto my spreadsheet which is using the reference information, it skips however may rows the arbitrary information covers (34 rows). Instead of going from using reference sheet2!a1,b1,c1,d1,e1,f1,g1,h1,i1,j1,k1,l1 to using sheet2!a2,b2,c2,etc, it goes to sheet2!a35,b35,c35,etc. Can anyone assist me?

I do not know a lot about using arrays and hate to sound lazy, but I have a time constraint.
 
Can you show us an example of what you are trying to do please? I don't quite see a question in your problem. Thanks.
 
Yes its hard to figure what the issue is and why arrays are part of it - but maybe it's very simple? Save the workbook first in case but it sounds like you could just copy your formulas that are referencing the source info correctly down the page from row 1 to what ewver it needs to be to ref all data and then the relative formulas which I assume you would have will pick up the missing rows and be back the way you want it. I know that sounds a very simple answer but it might be all there is too it?

cheers
 
Lwrozans


As the previous posts have said, it is important to understand what your trying to do with your data.


However instead of using a range reference

Code: 
Sheet2!a1,b1,c1,d1,e1,f1,g1,h1,i1,j1,k1,l1


Have you thought of a simple

Sheet2!A1:L1


Now you can use either an Offset or an Indirect to construct the other ranges.


=Offset(Sheet2!A1:L1, NoRows,)


or


=Indirect("Sheet2!A" & Z1 & ":L" & Z1)
 
Gentlemen,

I greatly appreciate the help you've put forth on this. I think '=offest' would be the best way to go about doing this but these forms that are actually using the reference data are, like I mentioned, 34 rows tall, and I will have a large quantity of the forms on one sheet. I'm thinking somethiing like this but I don't know how to tell the formulas to compensate for this exponential growth of cells; this is my best stab. (keep in mind, I have little/no experience using offsets)

'=offset(sheet2!A1:L1,34*formnumber)'

"formnumber" is just which form is actually going to be recieving information from the reference and is an easy addition and will greatly simply the math for the offset.

Yes? No?
 
You must log in or register to reply here.
Back
Top