Boolean Logic
Posted by Brian P on November 25, 2000 7:49 PM
I am trying to grasp the Boolean thing. I have a sheet that I am collecting data on. The data that shows up on my collection sheet is dependant upon a number switch on sheet 1.
Example, if Sheet 1 cell A1=1, I want data from sheet 10 Cell A1 to show up on my collection sheet.
If Sheet 1 cell A1=2, I want data from sheet 11 Cell A1 to show up on my collection sheet
If Sheet 1 cell A1=3, I want data from sheet 12 Cell A1 to show up on my collection sheet.
I have a total of about 12 sheets, so a Nested If is out of the question, and I have no VBA experience. I would assume that Boolean logic would be the answer but I am having trouble getting it worked out.
Another wrench in the gears is that some of the cells that I am wanting to copy are text and some are numbers.
TIA,
Brian P
Posted by Tim Francis-Wright on November 26, 2000 7:16 PM
If all you had were numbers, then the following would work:
=(Sheet1!a1=1)*Sheet10!a1 + (sheet1!a1=2)*Sheet11!a1 + ... etc.
One advantage to this sort of formula is that the individual cells can be
anywhere in your notebook.
Since you have some alphabetic entries, you'll
need something more devious--this depends (1) on the
data being on the same place in all of the data sheets,
and (2) all of the data sheets being named
Sheet10 through Sheet21 (or any other range of 12 Sheets)
=INDIRECT("Sheet" & Sheet1!A1 + 9 &"!A1")
(I used 9 because it appears that you have data in
sheets 10 through 21.)
There is probably a nifty VBA way to do this,
even if your sheets have more intuitive names,
but you would need to make sure that the proper
sheet was linked to the proper index.
HTH
