Nesting if Functions
 

I would like to use a nested if function to display a months work
summary in a single area using a scroll bar. I have used this to good
effect for small amounts of data tables but would like to extend it to
displaying information covering 12 separate months. I know you can only
nest an if function up to 8 times I believe. Is there a way around this
that would let me display my 12 months of data I need to.

Regards

Don

Nesting if Functions
 

There is bound to be another way, using some sort of lookup probably.

Can you give some details of the data, and what you need to do specifically,
and expected results?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"don" wrote in message
...
I would like to use a nested if function to display a months work
summary in a single area using a scroll bar. I have used this to good
effect for small amounts of data tables but would like to extend it to
displaying information covering 12 separate months. I know you can only
nest an if function up to 8 times I believe. Is there a way around this
that would let me display my 12 months of data I need to.

Regards

Don

Nesting if Functions
 

Bob Phillips wrote:
There is bound to be another way, using some sort of lookup probably.

Can you give some details of the data, and what you need to do specifically,
and expected results?

Bob not sure how to explain further but here goes

Table below shows one group of workers for october the codes represent
their attendance.

There are 3 other shifts of workers and this information resides in
their own shift 1 2 3 & 4 workbook.

I use a seperate workbook to pull in all the information from the four
shifts and different locations.

At present I use to good system of paste links to a spreadsheet that
holds all four shifts (located further down spreadsheet so the user
cannot see them without scrolling down) and use what you might call a
template table at the top of the spreadsheet to house all of the common
heading such as dates etc

Template Table

October
Location 1
Shift 1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 etc

Bob 1 1 1 1 1 1 r r 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
John 1 1 1 1 1 A A A A A A A A A A A A A 1 1 1 1 1 1
Bill 1 1 1 1 R R 1 1 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
Mike 1 1 1 1 1 r r 1 1 1 1 1 1 1 1 1 r r 1 1 A A A A

<<<<<<Scroll Bar


Then using a form scroll bar inserted below the template table I use
nested if functions to display the shift table data within the template
structure.

Hidden data tables

October
Location 1
Shift 1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 etc

Bob 1 1 1 1 1 1 r r 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
John 1 1 1 1 1 A A A A A A A A A A A A A 1 1 1 1 1 1
Bill 1 1 1 1 R R 1 1 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
Mike 1 1 1 1 1 r r 1 1 1 1 1 1 1 1 1 r r 1 1 A A A A



October
Location 1
Shift 2 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 etc

Neil 1 1 1 1 1 1 r r 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
Bob 1 1 1 1 1 A A A A A A A A A A A A A 1 1 1 1 1 1
Ralph 1 1 1 1 R R 1 1 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
Mike 1 1 1 1 1 r r 1 1 1 1 1 1 1 1 1 r r 1 1 A A A A



October
Location 1
Shift 3 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 etc

Bob 1 1 1 1 1 1 r r 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
John 1 1 1 1 1 A A A A A A A A A A A A A 1 1 1 1 1 1
Bill 1 1 1 1 R R 1 1 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
Mike 1 1 1 1 1 r r 1 1 1 1 1 1 1 1 1 r r 1 1 A A A A

October
Location 1
Shift 4 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 etc

Bruce 1 1 1 1 1 1 r r 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
John 1 1 1 1 1 A A A A A A A A A A A A A 1 1 1 1 1 1
Paul 1 1 1 1 R R 1 1 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
Mike 1 1 1 1 1 r r 1 1 1 1 1 1 1 1 1 r r 1 1 A A A A



Whilst I can do this already and can display one month and the four
shifts within the one template I cant display one shift over 12 months
because I run out of if functions.


Help :-)

rEgards

Don

Nesting if Functions
 

Don,

I don't understand why you need a formula at all, why not just simple links
back to the data?

Can you also post the formula, albeit incomplete, that you envisage?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"don" wrote in message
...
Bob Phillips wrote:
There is bound to be another way, using some sort of lookup probably.

Can you give some details of the data, and what you need to do
specifically,
and expected results?

Bob not sure how to explain further but here goes

Table below shows one group of workers for october the codes represent
their attendance.

There are 3 other shifts of workers and this information resides in
their own shift 1 2 3 & 4 workbook.

I use a seperate workbook to pull in all the information from the four
shifts and different locations.

At present I use to good system of paste links to a spreadsheet that
holds all four shifts (located further down spreadsheet so the user
cannot see them without scrolling down) and use what you might call a
template table at the top of the spreadsheet to house all of the common
heading such as dates etc

Template Table

October
Location 1
Shift 1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 etc

Bob 1 1 1 1 1 1 r r 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
John 1 1 1 1 1 A A A A A A A A A A A A A 1 1 1 1 1 1
Bill 1 1 1 1 R R 1 1 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
Mike 1 1 1 1 1 r r 1 1 1 1 1 1 1 1 1 r r 1 1 A A A A

<<<<<<Scroll Bar


Then using a form scroll bar inserted below the template table I use
nested if functions to display the shift table data within the template
structure.

Hidden data tables

October
Location 1
Shift 1 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 etc

Bob 1 1 1 1 1 1 r r 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
John 1 1 1 1 1 A A A A A A A A A A A A A 1 1 1 1 1 1
Bill 1 1 1 1 R R 1 1 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
Mike 1 1 1 1 1 r r 1 1 1 1 1 1 1 1 1 r r 1 1 A A A A



October
Location 1
Shift 2 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 etc

Neil 1 1 1 1 1 1 r r 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
Bob 1 1 1 1 1 A A A A A A A A A A A A A 1 1 1 1 1 1
Ralph 1 1 1 1 R R 1 1 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
Mike 1 1 1 1 1 r r 1 1 1 1 1 1 1 1 1 r r 1 1 A A A A



October
Location 1
Shift 3 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 etc

Bob 1 1 1 1 1 1 r r 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
John 1 1 1 1 1 A A A A A A A A A A A A A 1 1 1 1 1 1
Bill 1 1 1 1 R R 1 1 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
Mike 1 1 1 1 1 r r 1 1 1 1 1 1 1 1 1 r r 1 1 A A A A

October
Location 1
Shift 4 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 etc

Bruce 1 1 1 1 1 1 r r 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
John 1 1 1 1 1 A A A A A A A A A A A A A 1 1 1 1 1 1
Paul 1 1 1 1 R R 1 1 1 1 1 1 1 1 r r 1 1 1 1 1 1 1 1
Mike 1 1 1 1 1 r r 1 1 1 1 1 1 1 1 1 r r 1 1 A A A A



Whilst I can do this already and can display one month and the four
shifts within the one template I cant display one shift over 12 months
because I run out of if functions.


Help :-)

rEgards

Don

Nesting if Functions
 

Bob Phillips wrote:
Don,

I don't understand why you need a formula at all, why not just simple links
back to the data?

Can you also post the formula, albeit incomplete, that you envisage?


Bob,

I am using links but the IF formula decides where to look depending on
the control number set by the scroll bar. The idea is to keep the
template table static and the table data to appear within the template
window depending on the scroll bar selection. The formula below has
been copied over to three template tables all in a row for Oct Nov Dec
which allows me to pick out each shift easily. Hope this helps


=IF($B$71=1,B76,IF($B$71=2,B105,IF($B$71=3,B134,IF ($B$71=4,B163))))

Regards

Don