Solved: Assistance with Excel 2007 Formula

tatum97 · 08-Aug-2008, 03:12 PM #1

Surely I am missing something...

In a spreadsheet, cell H1 contains the number 150, this number will change frequently. Is there a way to get column A to automatically(sequentially 1-150) number based on the number that is manually input in cell H1? I know of the <Edit> <fill> <series> <linear> option, but am in search of a formula that will automatically number instead of this option.

computerman29642 · 08-Aug-2008, 04:05 PM #2

tatum97, welcome to the forum.

Could you clarify what exactly you are trying to accomplish? Could you attach a sample spreadsheet?

tatum97 · 08-Aug-2008, 04:19 PM #3

What I want to do is this, I want the Numbers listed in column A of the work sheet to automatically correspond to the quantity manually entered into cell H1

computerman29642 · 08-Aug-2008, 04:25 PM #4

Ok, I am still not a 100% sure what you are looking for. So, if I enter the number 25 in cell H1, what should display in cell A1?

tatum97 · 08-Aug-2008, 04:30 PM #5

Column a should then number 1 thru 25

computerman29642 · 08-Aug-2008, 04:33 PM #6

Could you attach a sample file?

computerman29642 · 08-Aug-2008, 04:36 PM #7

I believe I understand what you are trying to accomplish. Let me see what I can do.

slurpee55 · 08-Aug-2008, 05:18 PM #8

In cell A1, enter
=IF(1+$H$1>1,1,"")
Then in A2, enter
=IF(A1="","",IF(A1+1-$H$1>0,"",A1+1))
and drag it to fill column A
It will fill to whatever is in H1, and have blanks in all the following cells

slurpee55 · 08-Aug-2008, 05:19 PM #9

Of course, all the apparently blank cells in column A are not blank - they all have formulas in them.

computerman29642 · 08-Aug-2008, 05:22 PM **#10**

slurpee55, that is some pretty nice coding you got going on there.

computerman29642 · 08-Aug-2008, 05:23 PM **#11**

Could you breakdown the formula?

tatum97 · 08-Aug-2008, 05:42 PM **#12**

It works!

slurpee55 · 08-Aug-2008, 05:47 PM **#13**

Well, this part is pretty simple
=IF(1+$H$1>1,1,"") - all that does is make sure that the number in H1 is larger than 0, by adding 1 to it and seeing if it is larger than 1. If it isn't larger than 0, nothing ("") shows in the cell. (The $H$1 prevents this from changing to H2, H3, etc. as you drag and fill the cells)
This part
=IF(A1="","",IF(A1+1-$H$1>0,"",A1+1))
checks two things.
=IF(A1="" is the case where $H$1=0.
The first statement after that is the value if it is true - which is to show "" (nothing) again. If this is true, the rest of the statement is ignored.
The second statement is checked if it is false:
IF(A1+1-$H$1>0 first checks to see if A1 (which, if it checking this must be equal to 1) plus 1 minus whatever is in $H$1 is > 0. If it is, it shows "" again, if it isn't, it shows A1+1.

slurpee55 · 08-Aug-2008, 05:48 PM **#14**

Glad to help tatum97 - welcome to the Forum!!!

slurpee55 · 08-Aug-2008, 05:53 PM **#15**

Oh, tatum, please mark this thread as Solved using the button at the top of the page.
Thanks!

Search
Search in:
Show Threads Show Posts
Advanced Search

Tag Cloud
audio blue blue screen boot bsod compaq computer cpu crash dell drivers dvd error excel firefox format freeze freezing hard drive install internet internet explorer kb951748 lan laptop loss of internet malware memory motherboard network networking outlook printer problem restart screen slow sound startup trojan update virus vista windows windows xp winxp wireless wmp zonealarm zone alarm

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)