Alright Excel Gurus...

[Boblobla]

Here is my issue. I have one column (column A) that has a Y or N in it (for yes or no...) and another column (column B) that requires a value if column 1 has a Y. Is there any way to get the cell in column B to highlight if they are blank AND the column A value is Y?

I have consulted the internet and played with if() statements along with conditional formatting and I can't get it to work. I need something like the following but can't figure out how to get it to work.

if(A1="Y" and B1="blank", change cell color of B1 to red, do nothing)

I am not sure if excel can do this...

Please help me CP, you are my only hope.

[Fuzz]

If B1 gets a value when A1 is Y, otherwise it stays blank, can't you just do a conditional format on B1 being blank or not?

[Boblobla]

Originally Posted by Fuzz

If B1 gets a value when A1 is Y, otherwise it stays blank, can't you just do a conditional format on B1 being blank or not?

I only want the cell colour to change when B1 is blank and A1 is Y. A1 can also be N and when A1 is N, B1 will be blank and that is acceptable and I don't want the cell highlighted.

[Boblobla]

HA, got it.

I needed to use an AND statement in conditional formatting.

=and(A1="Y", isblank(B1))

the condition is change the cell to red and the range is the full column and it works.

[Fuzz]

Ah, ok, so three states:
A1=Y, B1=value, no highlight
A1=Y, B1=blank, highlight
A1=N, B1=blank, no highlight

Is B1 actually blank, or is there a formula making it not have a value(in this case, it isn't blank because the formula resides in the cell, even though you see nothing).

You can do multiple rules, so you can first highlight everything where A1=Y, then the next rule can remove the highlight on cells where B isn't blank.

[Fuzz]

Ah, nice. I was going to suggest that, but I wasn't sure if B was actually blank or hiding a formula.

[indes]

Just to piggyback on this thread I have an excel dilemma.

I am doing my taxes and have my banking transactions in a spreadsheet.

I put everything in, in the right order. Now that I have used "sort" it is out of order.

I can sort everything by date, but would like to have the transactions sorted from highest balance to lowest balance on any specific day. Make sense to anyone?

You can see that the dates are in order, but the transactions are not. Can I sort A and have a secondary sort for E?

[calgarygeologist]

Originally Posted by indes

Just to piggyback on this thread I have an excel dilemma.

I am doing my taxes and have my banking transactions in a spreadsheet.

I put everything in, in the right order. Now that I have used "sort" it is out of order.

I can sort everything by date, but would like to have the transactions sorted from highest balance to lowest balance on any specific day. Make sense to anyone?

You can see that the dates are in order, but the transactions are not. Can I sort A and have a secondary sort for E?

Easily done. You just have to set up multiple sort criteria.

You can find instructions here:
http://www.contextures.com/xlSort01.html

[Fuzz]

Basically, I'd like to pass a cell value like you would pass a variable to a function, and get a result in another cell.

I've got what will be an expanding table, and there will be a number in Column A. I'd Like column B to get the result of a complicated process on another sheet. I know I could put it all on one sheet, and fill down, but I'd have about 20 extra columns that I'd rather keep off the main sheet. So is there a way to pass that to the other sheet, run it through one row of calculations and spit it back out in column B?

[edslunch]

It's easy to pass a value from the cell on one sheet to a selling another sheet. Simply click on the destination cell, type in =, then click the source cell on the other sheet. Do this in One Direction for your input cell in the other direction for your output cell.

[edslunch]

Originally Posted by Fuzz

Basically, I'd like to pass a cell value like you would pass a variable to a function, and get a result in another cell.

I've got what will be an expanding table, and there will be a number in Column A. I'd Like column B to get the result of a complicated process on another sheet. I know I could put it all on one sheet, and fill down, but I'd have about 20 extra columns that I'd rather keep off the main sheet. So is there a way to pass that to the other sheet, run it through one row of calculations and spit it back out in column B?

Replied

[polak]

You can even do it in other work books.

[Fuzz]

Yes, I know I can do that. What I want is to pass the value to sheet 2 A1, and get the value from W2 back on sheet 1, which I know is easy. But I don't want to repeat that all the way down the sheet, so on the next row in sheet one, I want that to go to the same sheet 2 A1. See the issue?

[GGG]

$a$1

I think this is what you are asking. The sheet it is on doesn't really matter. To hold the value as you copy down any rows you just use $ sign in front of the affected row or column in the formula.

ie A1*$B$1 is in C1 if you copy the formula down to c2 and beyond you get
A2*$B$1 in C2
A3*$B$1 in C3

toss the sheet reference in front and your done.

[Fuzz]

I don't think that is what I am looking for. I don't want a corresponding row on sheet 2 for every row in sheet 1, I just want one row that has the formula, and every row on sheet one gets put through that.

If anyone is a programmer, it's just a basic function passing a variable. I could do it in VBS, but I don't want a macro in this, as it will be distributed.

[Regorium]

Originally Posted by Fuzz

I don't think that is what I am looking for. I don't want a corresponding row on sheet 2 for every row in sheet 1, I just want one row that has the formula, and every row on sheet one gets put through that.

If anyone is a programmer, it's just a basic function passing a variable. I could do it in VBS, but I don't want a macro in this, as it will be distributed.

From what I know, you can't do this with formulas.

You could just fill down on sheet 2 and hide the sheet if it's for distribution.

[Fuzz]

Ya, that's my backup plan. I just didn't want it to fail if a bunch of data gets added in the future, and thought it would be nice to have it on one row. My Googling didn't find anything though. I would think this would be a handy feature to have.

[GGG]

I don't think there is any Loop functionality in basic excel functions which it sounds like you would need here.