Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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.

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

....

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

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

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.

Related Topics:

- If nesting limits
- Help with "Nested Do While Loop"
- creating a figure using nested loops
- Dynamic userform - create nested frames?
- Nested For Loop Plz Help
- Problem in nested Custom Tags
- Help with Objects and Nested Collections
- Help with nested if statement - with and & or
- Nesting a repeater in a datalist
- Dropdown nested in repeater
- Dropdown nested in repeater
- please help nested exception
- nested class
- Limit size of swing frame & or components
- ASP.NET file upload and size limit?
- Is it possible to have limited number of rows and columns in a sheet
- VBA Max Line Limit
- Variable scope & Lifetime limits causing trouble
- Limit in number of patterns in MessageFormat
- Outlook Express shell string limit?
- Access Time-out period.... Or session variable time limit?
- Access Time-out period.... Or session variable time limit?