Home

Help with formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-924705%22%20slang%3D%22en-US%22%3EHelp%20with%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-924705%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20create%20a%20spreadsheet%20to%20record%20batch%20numbers%20but%20I%20would%20like%20the%20batch%20number%20automatically%20generated.%20So%2C%20I%20want%20to%20say...%20if%20I%20put%20Yes%20in%20a%20certain%20cell%20I%20get%20given%20a%20batch%20numer%20and%20that%20number%20increases%20by%201%20every%20time%20I%20put%20Y%20into%20the%20next%20cell.%20I%20have%20tried%26nbsp%3B%3C%2FP%3E%3CP%3EIF%20(E5%20%3D%22%22%2C%22%22%2C2350%2B1)%20but%20keep%20getting%20stuck.%3C%2FP%3E%3CP%3EThe%20hope%20is%20that%20if%20you%20put%20Y%20in%20D5%20a%20batch%20number%20is%20displayed%20in%20D6%2C%20then%20if%20you%20put%20Y%20in%20E5%20the%20next%20batch%20number%20in%20the%20series%20is%20displayed%20in%20E6%2C%20but%20if%20nothing%20is%20put%20in%20D5%20the%20batch%20number%20displayed%20in%20E6%20is%20the%20next%20number%20in%20the%20series.%20I%20have%20started%20my%20batch%20number%20series%20at%202350.%3C%2FP%3E%3CP%3EAny%20assistance%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-924705%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-925665%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-925665%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F430446%22%20target%3D%22_blank%22%3E%40HorseyAmanda%3C%2FA%3E%26nbsp%3B%20OK%2C%20I've%20entered%20IF%20formulas%20in%20both%20D6%20and%20E6%20that%20will%20do%20(I%20think)%20what%20you%20want.%20I've%20left%20the%20D5%20and%20E5%20cells%20blank%20for%20now%2C%20but%20if%20you%20enter%20Y%20into%20either%20cell%20you'll%20see%20the%20result.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20minor%20modification%20I've%20made--and%20I%20think%20you'll%20need%20something%20like%20this--is%20I%20placed%20your%20%22starting%20number%22%20in%20the%20upper%20left%20corner%2C%20Cell%20A1%2C%20and%20then%2C%20depending%20on%20whether%20you%20have%20one%20or%20two%20batch%20numbers%20on%20this%20first%20page%2C%20you'll%20see%20that%20cell%20A1%20on%20the%20NEXT%20spreadsheet%20starts%20with%20a%20batch%20number%20already%20incremented%20by%20one.%20So%20the%20same%20formulas%20put%20into%20that%20sheet's%20D6%20and%20E6%20will%20continue%20the%20pattern.%20(I%20was%20assuming%20that%20you're%20going%20to%20be%20using%20this%20same%20layout%20on%20subsequent%20sheets....)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-927141%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-927141%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BThank%20you%20very%20much%20for%20your%20help.%20It%20works%20very%20well%20but%20I%20was%20going%20to%20add%20more%20than%20one%20batch%20record%20per%20sheet.%20Each%20sheet%20will%20represent%20a%20week%20with%20a%20total%20of%2053%20sheets%20for%20the%20year.%20There%20is%20a%20potential%20of%2028%20batch%20numbers%20per%20week.%20So%2C%20I'm%20now%20struggling%20with%20the%20formula%20to%20carry%20the%20batch%20number%20to%20the%20next%20batch%20record.%20How%20do%20I%20populate%20L6%20%26amp%3B%2F%20M6%20with%20the%20same%20rules.%20Again%2C%20thanks%20for%20your%20help%2C%20much%20appreciated%3C%2FP%3E%3C%2FLINGO-BODY%3E
HorseyAmanda
New Contributor

I am trying to create a spreadsheet to record batch numbers but I would like the batch number automatically generated. So, I want to say... if I put Yes in a certain cell I get given a batch numer and that number increases by 1 every time I put Y into the next cell. I have tried 

IF (E5 ="","",2350+1) but keep getting stuck.

The hope is that if you put Y in D5 a batch number is displayed in D6, then if you put Y in E5 the next batch number in the series is displayed in E6, but if nothing is put in D5 the batch number displayed in E6 is the next number in the series. I have started my batch number series at 2350.

Any assistance would be greatly appreciated.

2 Replies

@HorseyAmanda  OK, I've entered IF formulas in both D6 and E6 that will do (I think) what you want. I've left the D5 and E5 cells blank for now, but if you enter Y into either cell you'll see the result.

 

The minor modification I've made--and I think you'll need something like this--is I placed your "starting number" in the upper left corner, Cell A1, and then, depending on whether you have one or two batch numbers on this first page, you'll see that cell A1 on the NEXT spreadsheet starts with a batch number already incremented by one. So the same formulas put into that sheet's D6 and E6 will continue the pattern. (I was assuming that you're going to be using this same layout on subsequent sheets....)

 

 

@mathetes Thank you very much for your help. It works very well but I was going to add more than one batch record per sheet. Each sheet will represent a week with a total of 53 sheets for the year. There is a potential of 28 batch numbers per week. So, I'm now struggling with the formula to carry the batch number to the next batch record. How do I populate L6 &/ M6 with the same rules. Again, thanks for your help, much appreciated

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies