Jun 11 2018
12:01 PM
- last edited on
Jul 31 2018
08:26 AM
by
TechCommunityAP
Jun 11 2018
12:01 PM
- last edited on
Jul 31 2018
08:26 AM
by
TechCommunityAP
Hi All, just like the title says I'm trying to develop a sheet where a series of formulas are updated when a single cell is given a value. essentially it's a numbering system based on some basic math:
I am creating data validation sheets for 20-digit barcodes. Currently I use several formulas to generate the barcodes in the correct format (including converting the numbers to "text" to get around the 16-character limit) but there's still too much manual entry for my liking. I'd like to be able to just type in one number (the starting number) and have Excel math out all the rest. This means drawing upon data from other cell calculations. For example I have a cell [E41] that counts the number of cells with data [=COUNTA(A4:F39)-COUNT(A4:F39)] that returns a value [216]. I want to then use that value in another formula. Is there any way to reference the value of this cell rather than the formula? I'm trying to minimize user input to limit potential fat-fingered errors.
Jun 11 2018 01:33 PM
Hi,
Not sure I understand your issue. If you use, for example, the formula
=E41*2
it returns 512 in your case
Jun 12 2018 08:53 AM
The formula I'm trying to use the result from is another multi-step calculation. There's a number in J2, so the formula in K2 (which is my problem) is [=((E41*100)-1)+J2] which gives me a circular reference error.
What other data do I need to provide (which cell's formulas are causing my issue)? I've attached a picture of the reference map.
Jun 12 2018 09:18 AM
Sorry, but from the picture it's not clear how circular reference appears. Is it possible to receive copy of your file with non-sensitive information to check the formulas?
Jun 12 2018 09:39 AM
Thanks for offering to look at this. I've attached a test book. K2 should be 198600.
Jun 12 2018 10:03 AM
Thank you. In your file K2 is <something>+J2 and J2=K2. Other words, K2=<something>+K2. I have no idea what's the logic behind, but in Excel that doesn't work such way. You shall change your logic for K2 or J2 not have the reference within the cell on itself.
Jun 12 2018 10:24 AM
I'm confused. J2 in that file is 177001, K2 is =((E41*100)-1)+J2. I changed the COUNT formula in E41 to just be a manually entered number and that fixed things for now. Thank you for your help!
Jun 12 2018 11:53 AM
Sorry, I was wrong, that's circular reference due to H2, etc.