Nesting problem?

[CBrausa]

I know just enough about Excel to get me into trouble but not enough to
get me out.
I am trying to build a form that buyers will fill out for advertising.
Objective: when they enter the dept. number and the sku number all
information pertaining to that sku will automatically fill in the
appropriate places in the form.

I have 38 depts. I need to look at. I know IF statements can only go 7
layers deep. So I took the information from the 38 depts and put it on
7 different sheets in my workbook. I then find out that this
information is updated monthly so I revamped my worksheets and made a
link to the original sheets.

I am getting the error: Excel cannot complete this task with the
available resources. Choose less data or close other applications.

Our IT department tried to open and close this and with all the memory
they have it still has problems. He did find something about nesting
and it can only go 20 layers deep. Unfortunately I don't know about
nesting and no can seem to help me. Any suggestions? Other than scrap
it. HA!

 

[Roman]

Is it really necessary to use IF function? Your objective seems to me
rather as a 'lookup' one than an 'if' one. Did you consider a use of
any lookup function (e.g. VLOOKUP) to deliver the task?

 

[Guest]

If you are running out of resources then the question becomes haw much data
are you working with? The problem you are having sounds to me like it is
better suited to a database than it is to Excel. Here is a web site which
will give you some clues on the limits Excel has in terms of memory and
perfomance...

http://www.decisionmodels.com/index.htm

Depending on your version of Excel there are inherant limits as to the
amount of memory it can address. It is a limit of the program not your
machine.

 

[Tom Ogilvy]

Jim's provided a good reference, but more than likely you are using a
poor/ill-advised programming approach. There is no reason to have any
nesting to make a decision on what to do with the identification of the
department.

a simple example

v = Array("dept1", "deptAB", "deptEF", "dept5", "RSTUV")
ptrArray = Array(5,8,3,7,4)

sVal = "dept5"

for i = lbound(v) to ubound(v)
if lcase(sVal) = lcase(v(i)) then
sresults = v1(i)
exit for
end if
Next

with Worksheets("Form")
.Range("B9").Value = worksheets(sResults).Range("C11")
.Range("F12").value = worksheets(sResults).Range("B2")
End With

could be expanded to include an unlimited amount of departments. This may
not be applicable to what you are trying to do

But the best advice might be Vlookup as suggested by Roman. Organizing your
data for best use might be the highest payoff first step. Putting all
department information in a database/Table format would facilitate this.