More than 7 If Functions

[SamDev]

I have a formula that has more the 7 tests - from what I know only 7
functions can be used in a nested if - any suggestions.

Thanks.

 

[Paul]

I have a formula that has more the 7 tests - from what I know only 7
functions can be used in a nested if - any suggestions.

Post what you are trying to do (rather than how you are trying to do it) and
you will get an answer. There is always a better way than using more than 3
IFs, never mind 7.

 

[SamDev]

Thanks....

Example:

In cell B2 there is an entry for example of "Payment" and cells B3:B8 are
blank and need to be filled in with "Payment" . This goes on for over 7000
rows and the number of blank cells vary. The entry of "Payment" can be that
or 10 other options. Basically what is in B2 needs to be in the next set of
blank cells. Then there will be another set that could have an entry of
"Deposit" and then blank cells that need to be filled in with "Deposit" and
so on. There is no pattern to the entries.

Hope this helps.

Thx,

 

[Paul]

I am not sure if I have understood the problem properly, as I cannot see how
an IF statement would help you no matter how many IFs it had.

As far as I can see, what you need is simply a formula in each blank cell
that equals the cell above it. So, in B3 you would need =B2. If you were
then to copy this cell and paste it into B4:B8, the formula would adjust so
that, for example, in B8 you would have =B7. But you need to paste this
formula into all the blank cells in the range of 7000 cells in column B. To
achieve this all in one go (almost):
Copy B3.
Select column B.
Use Edit > Go To > Special > Blanks > OK
Paste.
I said "almost". This process will paste your formula into all the
intervening blank cells. You will have to do the last section (below the
last entry) manually, as Excel will not know how far you want the last entry
copied. (The alternative would have been to put something temporarily in the
cell immediately below where you want the last entry copied before starting
the procedure described above.)

If I have misunderstood your requirement, I apologise. Don't despair; have
another go at explaining.

 

[SamDev]

HI Paul:

That works except (and maybe I didn't explain properly) but after each "set"
of blank cells there is a blank row to separate each "set". So using the
suggested method (which I had totally forgotten about - dah!) works except
for that one glitch.

Thanks,

 

[Paul]

I suggest putting something in the cells of the blank separator rows in this
column, so that the process will not overwrite the separator row. In other
words, make them 'almost blank'. You could use just a space or an apostrophe
(which will not show in the cell) or a hyphen or whatever you choose.

 

[SamDev]

Thanks - sounds great - much appreciate your quick responses.

Thank you!