auto fill rows based on number and formula
Looking for a way that will auto fill rows on sheet1 based off value in cells B5:B13
ex: B5 = 10 (or whatever number entereed) and on sheet1 rows 2-12 in columnA would be filled in based on the following:
first cell would be 4000 and each cell after would add 400 to the previous so that row12 would read 7600
attached worksheet but manually entered values on sheet 1
Getting the 4000 start and 400 increase is fairly simple, but where the "B5 = 10 (or whatever number entereed) " part come in?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
B5 would be on the land calc page, so if you would enter 10 in that cell then it would autofill on sheet1 column A cells B3-B12
current value on the Land Calculator.xlsx is currently 10,000 so really column A on sheet1 cells A3-A10002 would autofill starting @ 4000 and then 400 increase so the last value in sheet1 A10002 would be 4,003,600
THis is probably far too simplistic, but it may give us a starting point.
In sheet1 A3, use this...
=IF('Land Calc'!B5=10000,4000,"")
then in A4, copied down, use this...
=IF(A3="","",A3+400)
this is the same as i have already done just used 4000 in A2 and then copied down =A2+400Originally Posted by FDibbins
what i need is to do this automatically, figured it would be a script or macro or something.
'Land Calc'!B5 would fill in Sheet1 Column A
'Land Calc'!B6 would fill in Sheet1 Column B
'Land Calc'!B7 would fill in Sheet1 Column C
etc....
so really 'Land Calc'!B5:B13 could be considered the rows that need to filled in on the corresponding column
OK thought it was too easy lol.
Try this in sheet2 A3, copied across...
=OFFSET('Land Calc'!$B$4,COLUMN(),0)
that just returns the same values on land calc page
'Land Calc'!B5 would fill in Sheet1 Column A = 10,000
'Land Calc'!B6 would fill in Sheet1 Column B = 4,310
'Land Calc'!B7 would fill in Sheet1 Column C = 4,640
Sorry, to me, thats what it looked like you asked for?
Please provide a sample of what your expected outcome would look like?
sheet1 is the expected outcome, but i dont want to have to enter any information on this page manully no matter what the number entered on the land calc page is the rows would auto fill not sure how else to explain it
Screenshot_1.png
another way i guess i could explain it would be to on sheet1 is to take Row 1 and autofill in that column that many rows starting (number in cell A1:I1) at the 3rd Row with a certain formula
been playing around with VBA and figured out this little code for autofill
Sub myautofill()
Range("a4").Value = "=A3+400"
Range("a4").Select
Selection.AutoFill Destination:=Range("A4:A100")
End Sub
almost does what i want but little mod needed:
i need this only to fill in the number of rows listed in A1 if A1=500 then i need A4-A503 filled in with out changing code everytime, so when A1 is changed autofills that many rows also need this to reset everytime anything in row 1 is changed
Last edited by work4nutz; 11-06-2013 at 07:27 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks