I need to have a way to fill columns with formulas that contain non-consecutive numbers. I don't want to have to type in each one, as I have 300 lines to fill.
Col A Col B Col C
Row 1 =Sales!E9 =Sales!O7 =CONCATENATE("00",Sales!A7,Sales!A4)
Row 2 =Sales!E12 =Sales!O10 =CONCATENATE("00",Sales!A10,Sales!A4)
Row 3 =Sales!E15 =Sales!O13 =CONCATENATE("00"Sales!A13,Sales!A4)
Hopefully the spacing comes out so people can make this out as top what it should look like.
I need to increase each formula by 3, instead of 1, due to spacing setup on the Sales page.
Also, I need to be able to maintain the A4 in the last formula, throughout the whole page. Any way to do that?
I'm working with Excel 2002, but am upgrading to 2007 in a week or two.
Thanks for any help.
Dan
Hello,
In A1, copied down
=INDIRECT("Sales!E"&ROW()*3+6)
In B1, copied down
=INDIRECT("Sales!O"&ROW()*3+4)
In C1, copied down
=CONCATENATE("00",INDIRECT("Sales!A"&ROW()*3+4),Sales!$A$4)
cheers
Alternatively - use INDEX (non volatile)
A1: =INDEX(Sales!$E:$E,9+(ROWS(A$1:A1)-1)*3) copied down B1: =INDEX(Sales!$O:$O,7+(ROWS(B$1:B1)-1)*3) copied down C1: ="00"&INDEX(Sales!$A:$A,7+(ROWS(B$1:B1)-1)*3)&Sales!$A$4 copied down
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thank you both to lyn and Donkey.
I tried the first solution first, as it seems simpler. Works, with a couple tweaks. I was missing one row using them as given, and I assume the problem was again the spacing on the Sales page. By changing the +6 to a +3 and the +4 to a +1, got the missing row, and everything in the correct order.
If one of you doesn't mind, can you explain the +6 and +4? Those numbers don't seem particularly intuitive when I'm trying to add things 3 rows apart.
I see I will now have to read up on the INDIRECT command.
Thanks again.
Dan
You want to start with 9, then 12, 15, etc.
These are increments of 3, i.e. the number in each subsequent row must be 3 more than the last
So you need to multiply the row number by 3 to arrive at a sequence of 3,6,9,12 etc.
You want to start in row 1, showing the value 9
So you need to add 6 to =row()*3 arrive at value 9 for row 1
The row number of row 2 is 2
Multiplied by 3 = 6
Add 6 to arrive at value 12 for row 2
etc.
Last edited by teylyn; 12-09-2010 at 04:25 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks