Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Limited with IF Then Else Nesting

  Asked By: Lambodar    Date: Aug 18    Category: MS Office    Views: 860
  

I've created a few basic custom functions using
Select Case, but now I'm a little stumped. I've got several sheets
that each have 500 pieces of data that is dependent on what is input
into one cell that I'll call $U$3. I'm using regular If, then, else,
false formulas here to reference a number of different things in
$U$3, pulling the data from other sheets and other workbooks to
create certain statistics. For example, one entry in $U$3 could
be "wk" which tells the copied formulas in each of the 500 cells to
take last weeks data number from the master sheet and take the
percentage difference from the current sheet [example: =if(b4="wk",
('Sheet5'!r4/'[LastWeek.xls]Sheet5'!r4)-1,""). The only problem is
that I now have more than 7 variables that I'm trying to cram in the
regular "if, then" and excel won't process the formulas.

My question is, is it possible to create a custom function that
looks for data to grab in another worksheet or workbook in a row
that is based on the location of the cell that it will fill? In
other words, if I know that F40=Sheet5!D287 and that it will never
change, can I tell G40 to grab data from Sheet5!h287, Sheet5!j287,
Sheet7!r287, etc. based on the conditions that I give it in the If
Then Else? The rows will always be constant, but the columns,
sheets, or workbooks will be different based on the condition. I
know I might be able to some how finagle Excel to work with
different sheets being referenced to another condition - build
an "if then" within the "if then" using if(and or if(or, but this
would be seem time consuming and less efficient than what I've heard
about VBA. I'm just not sure how possible it is. If anyone out there
has any ideas or different possible ways of addressing the nesting
problems, I'd really appreciate it.

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Wanda Patterson     Answered On: Aug 18

I hope I understand your question.
it seems that you should use the Offset property.
For example, you may have the following code:
....
'a range name "Base" is defined for a reference cell
dim col_offs as integer
dim val as variant
if cond1 then col_offs = 1
if cond2 then col_offs =2
if cond3 then col_offs = 3
....

val = range("Base").offset(col_offs, 0).value
....

 
Answer #2    Answered By: Joel Brown     Answered On: Aug 18

I'll try this. Will this also be able to work on other sheets or
workbooks?

Here's the function I'm using in Excel, if it helps:

=IF($U$3="pe",'G4'!T352, IF($U$3="lo",('G4'!I352/'G4'!Q352)-1, IF
($U$3="hi",('G4'!I352/'G4'!P352)-1, IF($U$3="dl",('G4'!I352/'G4'!
L352)-1, IF($U$3="dh",('G4'!I352/'G4'!K352)-1, IF($U$3="vl",('G4'!
M352/100000000), IF($U$3="yt",('G4'!I352/'U:\Research\[G4K
010305.xls]G4'!J352)-1,IF($U$3="wk",('G4'!I352/'U:\Research\[G4K
013105.xls]G4'!J352)-1,'G4'!O352))))))))

This is what is in cell P3. Cell P4 would be the same formula except
it would reference row 353

 
Answer #3    Answered By: Noel Peterson     Answered On: Aug 18

You may find the attached instructive. (Don't panic - no virus inside!)

 
Answer #4    Answered By: Iqbal Bashara     Answered On: Aug 18


It sounds like you are looking for an Excel formula rather than some
VBA code (or perhaps looking for either, as long as it does what you
need).

In Excel, one way around the problem is to use formulas in multiple
cells to determine what to do with the entry in $u$3.

Here is how you could use 3 cells to check for up to 13 possible
entries in cell u3.

1. In cell v3, make a nested IF statement checking for just 7 of the
possible values in u3. If none of those seven appear, the formula
will return some other string or number of your choosing, for example
"xxx":

IF($U$3="pe",'G4'!T352, IF($U$3="lo",...etc..,"xxx")))))))
So, if none of those seven terms appear in u3, cell v3 will just
contain "xxx"

2. Then, put another nested IF in the next cell over (cell w3). It
should first check if v3 contains "xxx". If so, then it continues
checking for 6 possible values in u3 using the nested-IF structure.
Otherwise, it returns the string "No match".

So, cell w3 would look like:
=IF(V3="xxx", IF($U$3="wk",...etc..., "No Match")))))))

3. Finally, cell x3 has:
=IF(V3="xxx",W3,V3)

The value in x3 will be either one of the values you are after, or the
words "No match".

 
Didn't find what you were looking for? Find more on Limited with IF Then Else Nesting Or get search suggestion and latest updates.




Tagged:    

 
 
 
 

Related Post