Home

Auto Fill using a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-711145%22%20slang%3D%22en-US%22%3EAuto%20Fill%20using%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711145%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20looking%20for%20a%20function%20that%20would%20allow%20me%26nbsp%3Bto%20auto%20fill%26nbsp%3B%20a%20certain%20number%20of%20rows%20based%20on%20data%20entered%20in%20the%20first%20row.%3C%2FP%3E%3CP%3ESo%2C%20say%20I%20want%20to%20fill%2020%20rows%2C%20I'd%20want%20the%20function%20to%20automatically%20fill%20them%20in%20when%20I%20fill%20in%20the%20first%20cell.%3C%2FP%3E%3CP%3EIs%20there%20a%20function%20that%20can%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20was%20suggested%20that%20I%20use%20the%20IF%20function%20to%20generate%20the%20needed%20number%20of%20rows%2C%20but%20I%20don't%20see%20how%20this%20would%20work.%3C%2FP%3E%3CP%3ECan%20someone%20explain%20or%20give%20another%20idea%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-711145%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711867%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Fill%20using%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711867%22%20slang%3D%22en-US%22%3EHello%20there.%20You%20could%20use%20an%20IF%20formula.%20The%20solution%20there%20being%20you%20can%20set%20the%20false%20condition%20to%20equal%20a%20null%20string%20(%22%22)%20which%20looks%20blank.%20To%20Excel%20there%20is%20a%20value%20in%20there%2C%20it's%20a%20zero%20length%20string%2C%20which%20can%20be%20confusing%20at%20first.%20There%20are%20inherent%20problems%20with%20that%2C%20however.%20First%2C%20since%20Excel%20see's%20it%20as%20having%20data%20(assuming%20the%20cells%20are%20blank)%2C%20it%20will%20still%20print%20that%20range%2C%20because%20Excel%20thinks%20it%20has%20data%20-%20which%20it%20does%2C%20it's%20just%20an%20empty%20string%2C%20but%20it's%20a%20value%20nonetheless.%20Second%2C%20if%20your%20formula%20never%20meets%20the%20false%20condition%2C%20how%20are%20you%20to%20know%20you%20copied%20down%20the%20formula%20far%20enough%3F%20This%20regularly%20gets%20users%20as%20many%20data%20sets%20you%20don't%20know%20how%20long%20it%20will%20go%20down%20and%20often%20the%20solution%20is%20%22copy%20it%20down%20really%2C%20really%2C%20really%20far%20*just%20in%20case*%20you%20need%20that%20many%20rows%22.%20As%20you%20could%20imagine%20this%20has%20some%20caveats.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20best%20case%20scenario%20is%20for%20you%20to%20define%20your%20data%20set%2C%20how%20and%20why%20it%20will%20do%20what%20it%20will%20do%2C%20and%20what%20the%20end%20goal%20is%20for%20it.%20I'm%20not%20sure%20what%20a%20solution%20would%20be%20because%20you%20haven't%20defined%20it%20very%20well%20at%20the%20moment%20but%20I%20can%20tell%20you%20it%20wouldn't%20be%20copying%20down%20an%20IF%20formula.%20Define%20your%20data%20more%20for%20us%20and%20we'll%20help%20you%20get%20a%20solution.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-712065%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Fill%20using%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712065%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F363893%22%20target%3D%22_blank%22%3E%40JMFJones%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20simple%20variant%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20591px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F119964i94853F0837E07BD1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ein%20C4%3C%2FP%3E%0A%3CPRE%3E%3DIF(ROW()-ROW(StartCell)%20%26lt%3B%3DCellsToFill%2C%20StartCell%2C%20%22%22)%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20it%20down%20with%20some%20gap%20against%20your%20possible%20range%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-712074%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20Fill%20using%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-712074%22%20slang%3D%22en-US%22%3EDepending%20on%20the%20OP's%20actual%20needs%2C%20this%20is%20inherently%20bad%20practices%2C%20for%20the%20reasons%20I%20specified%20above.%20Also%2C%20when%20using%20named%20ranges%20in%20your%20formulas%20in%20lieu%20of%20absolute%20cell%20referencing%2C%20it's%20probably%20best%20to%20qualify%20them.%3CBR%20%2F%3E%3CBR%20%2F%3EKind%20regards%3C%2FLINGO-BODY%3E
JMFJones
Occasional Visitor

I am looking for a function that would allow me to auto fill  a certain number of rows based on data entered in the first row.

So, say I want to fill 20 rows, I'd want the function to automatically fill them in when I fill in the first cell.

Is there a function that can do this?

 

It was suggested that I use the IF function to generate the needed number of rows, but I don't see how this would work.

Can someone explain or give another idea?

3 Replies
Hello there. You could use an IF formula. The solution there being you can set the false condition to equal a null string ("") which looks blank. To Excel there is a value in there, it's a zero length string, which can be confusing at first. There are inherent problems with that, however. First, since Excel see's it as having data (assuming the cells are blank), it will still print that range, because Excel thinks it has data - which it does, it's just an empty string, but it's a value nonetheless. Second, if your formula never meets the false condition, how are you to know you copied down the formula far enough? This regularly gets users as many data sets you don't know how long it will go down and often the solution is "copy it down really, really, really far *just in case* you need that many rows". As you could imagine this has some caveats.

The best case scenario is for you to define your data set, how and why it will do what it will do, and what the end goal is for it. I'm not sure what a solution would be because you haven't defined it very well at the moment but I can tell you it wouldn't be copying down an IF formula. Define your data more for us and we'll help you get a solution.

@JMFJones 

 

In simple variant

image.png

in C4

=IF(ROW()-ROW(StartCell) <=CellsToFill, StartCell, "")

and drag it down with some gap against your possible range

Depending on the OP's actual needs, this is inherently bad practices, for the reasons I specified above. Also, when using named ranges in your formulas in lieu of absolute cell referencing, it's probably best to qualify them.

Kind regards
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies