If Statement

Microsoft Excel: mac 2008 (mac)
May 28, 2010 at 00:42:09
Specs: Macintosh OS 10.6
I am creating a spreadsheet that utilising two sheets
to hold data, and one sheet to show me a summary of
the other two sheets.

The two sheets that are holding data are
called Beach Carnivals and Pool
Carnivals
and the table with a summary of both is
called Totals.

In the totals table, I have put a formula in (for
example) cell A1, =('Beach Carnivals'!A1) , and then
in A2, there is the formula =('Pool Carnivals'!A1).

I want to be able to highlight the cells which come
from the pool carnivals table with blue, and
the cells which come from the beach carnivals
table in yellow
.

Is this possible?


James


See More: If Statement

Report •

#1
May 28, 2010 at 14:07:37
There is no formula I know of for getting the sheet name, which means you will need some type of VBA.

I am not good at VBA, but try this:

This is a three-step process,

First: create code that will get the formula from a cell,
Second: cut up the formula from step one into something we can use,
Third: use Conditional Formatting to color the cell.


First the VBA Code for GetFormula

   Function GetFormula(Cell as Range) as String
      GetFormula = Cell.Formula
   End Function

The above code is not my creation, I obtained it at:
http://www.mvps.org/dmcritchie/exce...

Now to install the above code:

First, open your workbook in Excel and bring up the Visual Basic Editor by pressing Alt-F11,

There should be a Project window on the left,
A large Code window, and an “Immediate” window at the bottom.

If not then use Ctrl+R, to bring up the Project Window

Within the Project Explorer window, select your project, which is your workbook name.

If you do not see modules in your project (workbook) then on the Task Bar, invoke the Insert (menu), and then select module.
Copy and paste the above code into the module that you just picked.
Save and exit the Editor.


You can now use the =getformula() function in a cell.

Second, with your linked data in cell A1
in cell B1 enter the formula: =getformula(a1)
it should return: ='Beach Carnivals'!A1

Now, since all we want is the sheet name, we have to slice and dice a bit to get what we need.

Just so you can see what's happening,

In cell C1 enter the formula:

=MID(getformula(A1),FIND("'",getformula(A1))+1,FIND("'",getformula(A1),3)-4)

This should return only the sheet name Beach Carnivals which is what we want as we will be using this formula in our Condition Formatting.


Third, the Conditional Formatting.
I'm not sure how Excel on Mac has its Tool Bar arraigned but try this:

1st - Highlight your data cell, Cell A1

On the Menu Bar:

2nd - Format
3rd - Conditional Formatting
4th – Change “Cell Value is” to “Formula Is”
5th – Enter the formula:

=IF(MID(getformula(A1),FIND("'",getformula(A1))+1,FIND("'",getformula(A1),3)-4)="Beach Carnivals",TRUE,FALSE)

Sub Menu Format:
6th - Patterns
7th - Select the Color Yellow
8th - Press OK

9th - Press Add

10th – Change “Cell Value is” to “Formula Is”
11th – Enter the formula:

=IF(MID(getformula(A1),FIND("'",getformula(A1))+1,FIND("'",getformula(A1),3)-4)="Pool Carnivals",TRUE,FALSE)

Sub Menu Format:
12th - Patterns
13th - Select the Color Blue
14th - Press OK
15th - Press OK


If all went according to plan, the cell A1 should now be colored Yellow.

(DerbyDad03 & Humar, if you see any mistakes, your help is always appreciated.)

MIKE

http://www.skeptic.com/


Report •

#2
May 28, 2010 at 19:19:11
It is my understanding that VBA does not work in Excel 2008 - which is the Mac version of Excel.

I don't have access to a Mac this weekend, so I can't offer any suggestions. I can try some things on Monday.


Report •

#3
May 28, 2010 at 20:08:47
DerbyDad03, according to the MacWorld.com site you are correct.
In reviewing Excel 2008, they say: "No Visual Basic for Applications support"

The OP's only other option would be to convert the VBA code to AppleScript, if possible.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
May 28, 2010 at 21:40:38
re: There is no formula I know of for getting the sheet name

Allow me to introduce you to the CELL function. ;-)

In the windows version of Excel (and OOO3) the CELL function will return the complete path, including the Sheet name when used as follows:

=CELL("filename",A1)

"filename" is really the word filname in quotes, not the actual filename. The file must be saved for this work properly.

Adding to that, this will return just the sheet name:

(Note: I only have access to OOO3, so I can't test the windows version of this formula (given below) but the OOO3 version works:

=MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255)

Now, I'm not sure we can use this info for Conditionally Formatting a cell based on the sheet named used in a formula on a Mac. I'm sitting at a Windows machine running OOO3, so I'm 2 universes away form your setup, but I'll keep trying to get something to work.


Report •

#5
May 28, 2010 at 22:48:21
I uploaded my spreadsheet to rapid share, you can download it here (http://rapidshare.com/files/392795985/Skill.xlsx) if it helps.

Report •

#6
May 29, 2010 at 07:06:56
DerbyDad03, I tried the cell function, and it would not work for me. All I could ever get was the path to the workbook, if I could get it to work at all.

Now the valuable lesson to learn from this, is that at least in Excel 2000, the =CELL("filename") will only work on a Saved workbook.
If it's new and unsaved you get nada.

MIKE

http://www.skeptic.com/


Report •

#7
May 29, 2010 at 07:23:46
Have played around a bit, and it seems that =CELL("filename",A1) will not work in 2000.

When I tried it on a workbook with differently
named sheets, it will return only the current
sheet name,

If cell A1 was linked to:
='National Monuments'!A1

then the =CELL("filename",A1) only returned
the path to the current sheet name, not the name in A1:

C:\Documents and Settings\Owner\My Documents\Dad\[National_Parks.xls]Sheet3

and using the formula:

=MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1, 255)

only returns Sheet3.

It would seem that the =CELL() function is ignoring the address, A1.

MIKE

http://www.skeptic.com/


Report •

#8
May 29, 2010 at 20:47:24
I'm sorry if you took my response to mean that the CELL function will return the Sheet name in a formula.

I was responding to your statement:

There is no formula I know of for getting the sheet name

I took that to mean that you didn't know of a formula to return the sheet name - period - not the sheet name in a formula. The CELL function will return the name of the sheet in which it is used. That's all it will do, and that's all I meant. Sorry for the confusion.

I also added that I don't know if the CELL function will help with this particular question since I won't have access to a Mac (or even Excel) until Monday. (I don't think it will, but I wanted to try a few things) Heck, I don't even know if CELL works on a Mac. ;-)

re: the valuable lesson to learn from this is...the =CELL("filename") will only work on a Saved workbook.

Yes, I mentioned that in Response Number 4:

...the =CELL("filename") will only work on a Saved workbook.


Report •

#9
May 30, 2010 at 00:50:50
I did a bit of digging, and found that the formulas:


=CELL("filename",A1) will give you the path to the sheet in which the formula is written.

=CELL("filename") will give you the path to the currently active sheet.

MIKE

http://www.skeptic.com/


Report •

#10
May 30, 2010 at 06:37:56
I wonder if it works differently in OOO3. I can't test Excel right now.

In OOO3, I put CELL("filename") in Sheet1.A3 and it returned the path and that sheet's name as expected

'file:///C:/Documents and Settings/Main/My Documents/CellFunction.ods'#$Sheet1

Then I switched to Sheet3 and entered =Sheet1.A3.

Since Sheet3 was now the ActiveSheet, shouldn't =Sheet1.A3 show

'file:///C:/Documents and Settings/Main/My Documents/CellFunction.ods'#$Sheet3

since the Cell function in Sheet1 should have returned the ActiveSheet name?


Report •

#11
May 30, 2010 at 11:54:20
What I did was to take two workbooks and split the screen with one on top the other at the bottom.

The top workbook was called Nation_Parks.xls with three sheets, Nation Parks, Nation Monuments, Sheet3

The bottom workbook, Gas Mileage.xls, with Ford, Chevy, Sheet3


In the top National_Parks, Sheet3
in cell B1 I enter: =CELL("filename",A1)
in cell B2 I enter: =CELL("filename")

When both formulas are initially entered they both show:

C:\Documents and Settings\Owner\My Documents\Dad\[National_Parks.xls]Sheet3

I then drop down to the Gas Mileage, Sheet Chevy
in cell I74 I enter: =CELL("filename",A74)
and as expected the result is:
C:\Documents and Settings\Owner\My Documents\Dad\[Gas Mileage.xls]Chevy

in cell I75 I enter: =CELL("filename")
and as expected the result is:
C:\Documents and Settings\Owner\My Documents\Dad\[Gas Mileage.xls]Chevy

BUT the moment I hit the enter key in cell I75, presto, chango, cell B2 in the National_Parks workbook changes to read:

C:\Documents and Settings\Owner\My Documents\Dad\[Gas Mileage.xls]Chevy

If I now jump back up to National_Parks, and hit F9 to recalculate and force Naional_Parks to become the active work sheet, then Cell I75 in Gas Mileage changes to:

C:\Documents and Settings\Owner\My Documents\Dad\[National_Parks.xls]Sheet3


A little bit’o magic in Excel.

MIKE

http://www.skeptic.com/


Report •

#12
May 30, 2010 at 20:03:33
I don't doubt what you say is true, but unless I misunderstood your "instructions", the CELL("filename") does not work the same in OOO3.

Both CELL("filename";A74) and CELL("filename") return the path to the sheet containing the formula, not the path to the ActiveSheet.

Neither your test, nor mine (response 10) result in CELL("filename") returning the ActiveSheet name - unless of course the ActiveSheet is the sheet containing the formula.

I'll try both on Excel when I get home tomorrow.

That said, one thing in your test confuses me, so instead of waiting until tomorrow, I'll ask now:

You said this - note the bold line


I then drop down to the Gas Mileage, Sheet Chevy
in cell I74 I enter: =CELL("filename",A74)...

in cell I75 I enter: =CELL("filename")...

BUT the moment I hit the enter key in cell I75, presto, chango, cell B2 in the National_Parks workbook changes to read:

C:\Documents and Settings\Owner\My Documents\Dad\[Gas Mileage.xls]Chevy

You said that B2 changed to read Chevy when you hit the enter key in I75.

It seems to me that B2 should have changed to Chevy as soon as you entered the formula in I74.

So, assuming CELL("filename") waits until the Gas Mileage sheet calculates to consider it "active", why didn't B2 change as soon as it calculated CELL("filename",A74) in I74?


Report •

#13
May 31, 2010 at 07:24:23
You said that B2 changed to read Chevy when you hit the enter key in I75.
Yes, that is correct.

It seems to me that B2 should have changed to Chevy as soon as you entered the formula in I74.

No, because I74 is =CELL("filename",A74) which will return only the name of the sheet in which the formula was written, which was Chevy.

While =CELL("filename") does not know until you press the enter key at the end of the formula whether or not you are looking for the Active Sheet, or the Sheet name of the the workbook where the formula is written..

The "address" of A1 or I74 anchors the formula to the workbook in which it is written, like when you anchor a cell with $A$1, while the formula with out an address seeks the name of the current workbook/sheet.

MIKE

http://www.skeptic.com/


Report •

#14
May 31, 2010 at 07:53:38
Also, it would appear that the Address in the =CELL("filename",XX) is immaterial, as long as it is a valid cell reference.
So =CELL("filename",A1) through =CELL("filename",IV65536) are valid while anything else will get you an error message.

MIKE

http://www.skeptic.com/


Report •

#15
May 31, 2010 at 17:06:34
What version of Excel are you trying this in?

I'm running 2003 and I just tried the exact steps you laid out in Response 11 and CELL("filename") is not changing, regardless of which sheet is active. This is the same thing I saw in OOO3.

The Gas Mileage workbook always shows Chevy as the sheet name in both cells, and the Nation_Parks always shows Sheet3 in both cells.

I've sent you an email address via PM. Can you send me both workbooks so I can see what happens when I open them on my machine(s)?

Now, back to the other issue...

re: It seems to me that B2 should have changed to Chevy as soon as you entered the formula in I74.

No, because I74 is =CELL("filename",A74) which will return only the name of the sheet in which the formula was written, which was Chevy.

I think you missed my point.

I was responding to your comment that [Nation_Park]Sheet3!B2, which contains =CELL("filename") changed to [Gas Mileage.xls]Chevy when you hit enter after typing a formula in [Gas Mileage]Chevy!I75, indicating that Chevy was the ActiveSheet.

I'm still curious as to why [Nation_Park]Sheet3!B2 didn't change when you entered the formula in [Gas Mileage]Chevy!I74. As soon as you entered that formula, Chevy become the ActiveSheet, didn't it?

In other words, B2 contained a formula that should return the ActiveSheet name, and Chevy should have become the ActiveSheet when you entered the formula in I74.

Is it possible that you entered I75 first? Or maybe you meant the moment I hit the enter key in cell I74?


Report •

#16
May 31, 2010 at 20:39:51
DerbyDad03, Sorry,
It seems to me that B2 should have changed to Chevy as soon as you entered the formula in I74.

You are correct, when I hit enter on I74, thus making Gas Mileage the active sheet, is when the change takes place.
I must not have been paying attention to what I was doing the first time around. But I just tested it again.

MIKE

http://www.skeptic.com/


Report •

#17
June 1, 2010 at 04:24:07
Mike, thanks for sending the workbooks.

I was able to duplicate your results - after I opened both workbooks in the same instance of Excel.

When you open the files in 2 separate instances of Excel (or OOO3) one instance does not recognize what is going on in the other instance.

That makes sense I just didn't think about earlier.


Report •

#18
June 1, 2010 at 09:40:36
P.S.

Mike,

If this was on a Windows machine where VBA was available, I like your use of the getformula UDF and Conditional Formatting.

However, I would probably use simpler CF formulas, ones that are not so dependent on the exact location of the strings Beach and Pool.

=IF(NOT(ISERROR(FIND("Beach",getformula(A1)))),1,0)

and

=IF(ISERROR(FIND("Beach",getformula(A1))),1,0)

The first will return 1 (TRUE) if it FINDs Beach in the formula and the second will TRUE if it doesn't.

As far as the OP's question, I'll pry my daughter's MacBook out of her hands tonight and see what I can get it to do.


Report •

#19
June 1, 2010 at 12:02:31
Nice formulas.

I'll pry my daughter's MacBook out of her hands tonight and see what I can get it to do

Haven't heard from the OP "jamesquine" since response #5, he may have given up, as this is his only post on the site.

MIKE

http://www.skeptic.com/


Report •

#20
June 1, 2010 at 12:29:28
Well, we did meander a bit since he asked his question. ;-)

I'd still like to see if it can be done without VBA. You never know when something like that might be handy.


Report •

#21
June 1, 2010 at 15:13:26
I don't know if this will work because it will depend on what the results of the formulas are and what you are doing with them.

In each cell where you have:

=('Beach Carnivals'!A1)

change it to be

=('Beach Carnivals'!A1) & " "

then use these in Conditional Formatting:

=IF(RIGHT(A1,1)=" ",1,0)

and

=IF(RIGHT(A1,1)<>" ",1,0)

Since (hopefully) only those cells that have the Beach formulas in them will end in a " " (space), the Conditional Formatting will be based on whether that space exists or not.

It's crude, but it might work for you.


Report •

#22
June 1, 2010 at 17:14:34
DerbyDad03,
very neat trick, but I’m afraid there is one problem with your formula, if the string being transferred, has a space after it, then the condition is true.

I tested it out and found that if the cell being transferred has a trailing space, then in the conditional formatting formula:

=IF(RIGHT(A1,1)=" ",1,0)

the condition is true.


I think a better formula would be something like:

=('Beach Carnivals'!A1) & char(160)

=CHAR(160) is a non printing character and its doubtful if someone will insert it, and being not printing it does not show up in the cell, much like a space.

Just modify the two Conditional Formatting formulas to:

=IF(RIGHT(A1,1)=CHAR(160),1,0)

=IF(RIGHT(A1,1)<>CHAR(160),1,0)

MIKE

http://www.skeptic.com/


Report •

#23
June 1, 2010 at 17:48:55
re: but I’m afraid there is one problem with your formula

Which is precisely why I started my post with:

"I don't know if this will work because it will depend on what the results of the formulas are and what you are doing with them."

Even a non-printing character might cause a problem depending on what the results of the formulas are and what you are doing with them.


re: being not printing it does not show up in the cell

I beg to differ.

Try this:

Put a number in Sheet2!A1, then put these formulas in Sheet1!A1:B3

        A                         B
1  =Sheet2!A1             =Sheet2!A1&CHAR(160)
2  =LEN(A1)               =LEN(B1)
3  =CELL("type",A1)	  =CELL("type",B1)
4  =ISTEXT(A1)            =ISTEXT(B1)

While you're at it, format A1 & B1 to be right-justified.

In each case, you'll get different results with the CHAR(160) than without it.

So, while the CF might work better with the CHAR(160) than with a space, what the OP is doing with the results might be impacted by whatever character is used.

Things are a little better with text in Sheet2!A1, but LEN and the formatting are still impacted by the CHAR(160).

I simply used a space as a "technique", with the disclaimer that it might not work depending on what the results of the formulas are and what you are doing with them.



Report •

Ask Question