Autofill cell references
Hi There,
I have a list of populated cells in sheet 1 e.g.
A
1 Cat
2 Dog
3 Pig
4 Cow
I want to take the data in Sheet1, and via cell referencing, create a second data set which follows a regular repeating format as below:
X
1 a1
2 a1
3 a1
4 a2
5 a2
6 a2
7 a3
8 a3
9 a3
I setup the above data in Sheet 2, but when I attempt to "autofill" the column down, I end up with something like the following which is of no use:
X
1 a1
2 a1
3 a1
4 a2
5 a2
6 a2
7 a3
8 a3
9 a3
10 a4
11 a5
12 a6
Can anyone, help? Having looked extensively Excel appears to have no autofill option with regards to cell references, only cell contents.
Cheers,
Ste
Last edited by stephen.ward; 02-19-2010 at 05:33 AM. Reason: proof read it, needed correcting!
Put this formula in X1 and copy down:
=INDEX(A:A, CEILING(ROW() / 3, 1))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Cheers,
I'm almost there, except that when I use the formula described, the data is offset by one row too high. I'm currently using the following formula:
=INDEX('MASTER Risk Register'!$H$5:$H$500,(CEILING(ROW()/4,1)))
The first piece of data I want to display in 'MASTER Risk Register' H6, but if I use H6 in the above, the first output of four, references H7 instead. So therefore I'm using H5 as at least that references the correct cell, though using the above offsets the output against the test as illustrated below:
Test Column Formula Output
(blank) dog
dog dog
dog dog
dog dog
dog cat
cat cat
cat cat
cat cat
cat mouse
mouse
How can i write the formula, so that all the animals align?
Many thanks.
Hehe, I guess we can just cheat:
=INDEX('MASTER Risk Register'!$H$5:$H$500, CEILING(ROW() / 4, 1) + 1 )
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
That still provides the same result!
Since I can't see your sheet, you're going to have to fiddle with these ROW() references until you get the result you want. Perhaps:
=INDEX('MASTER Risk Register'!$H$5:$H$500, CEILING((ROW() + 1) / 4, 1))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Well thanks to your help I've been able to achieve a lot and saved masses of time!
So thanks again!
Ste
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].
========
(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks