Posts by EDR

Re: Match, Print

Hi:

I'm trying everything from VLOOKUP, LOOKUP, OFFSET, MATCH, INDEX, DSUM:

I.e...

=INDEX(F4:F33,MATCH(1,C4:C33,0),-3)

I've also tried:

=INDEX(F4:F33,MATCH(>0,C4:C33,0),-3) - hoping to find anything greater than zero, and this won't let me even finish the formula (error).

All I'm getting is a #n/a error?

Nothing I'm trying is working, and I've been trying for hours! UGH!

Any ideas? I'd apprciate any assistance.

Thank you!

Hi Folks:

Another complicated one for my brain ....

Need to extrapolate certain values from a long column and if criteria is met have only those MATCHING VLUES print in another column.

EXAMPLE:

Column F (search F4:F33)
Searching down that column I see only 4 values that are > 0.
Each one of those values > 0 has a string of text in a corresponding row in column C4:C33, like "Mid", "Open", "Close", "Mid 2", etc.
I want to show only those values found (> 0) in a new set of rows beginning at F43 and lower (using the first value found, and printing sequentially as they were found).

So for this specific example:

C4 = Inventory / F4 = 0
C5 = Open / F5 = 1
C6 = Mid / F6 = 1
C7 = Text / F7 = 0
C8 = More Text / F8 = 0
C9 = Close / F9 = 1
C10 = Mid 2 / F10 = 1

Using the above, starting at F43 would begin:

F43: Open
F44: Mid
F45: Close
F46: Mid 2

How do I write this formula w/out using VBA?

Thank you so much!

: D

Re: Countif

If figured this one out too ...

COUNTIF(A1:A20,C2)+COUNTIF(etc,etc,etc).

Thank you.

EDR

Re: Countif

LAST REQUEST FOR THE DAY: I PROMISE:

I need another related formula for COUNTIF

This formula is easy if trying to find all occurances of ONE value in an array. What if I want to count from TWO choices? How do I write it then?

Example:

COUNTIF(A1:A20,C3 OR C4)?

I tried COUNTIF(A1:A20,OR(C3,C4)) and got a value of zero.

Basically, I need to count the total number of cells that contain any value that matches what's in cell C3 OR C4.

Thank you!

Re: MATCH, SEARCH or LOOKUP

Sorry, I got VLOOKUP to work (LOOKUP still not working), I was only going down one column array, not both? Yes, brain dead, I am.

Anyway, problem resolved, and thank you so much!

EDR : D

Re: MATCH, SEARCH or LOOKUP

Quote from Wigi

Hi again

A VLOOKUP might be the easiest:

=VLOOKUP(A20,D1:E50,2,false)

or

=LOOKUP(A20,D1:D50,E1:E50)

or INDEX combined with MATCH, ...

Choice is up to you

Display More

Hi Wigi:

Thank you so much again! However, I don't feel as dumb or as brain dead as I did a little bit ago.

I tried VLOOKUP exactly as you wrote it, and I get a #Ref error.

I also tried LOOKUP and I get a value of 0 (zero).

I tried both of these, like this, before even posting this thread, hence my post.

I also tried INDEX(MATCH(... But not sure if I did that formula right? What specifically should I try for INDEX(MATCH(... ???

Any other ideas?

Thank you!

Hi All:

I think I'm brain dead today. I can't get anything to work. Need a formula to find a value within a row, and whatever value is in another column (corresponding row) need to print that value.

EXAMPLE:

Cell A20 = "Open"

In Cell A22 I want a formula to search for "Open" in another column i.e.,:

Cell D45 contains "Open", and the value in E45 contains "A"

I would like cell A22 to have "A" as its value because it found "Open" and "A" is the value in the corresponding row (same row) in another column.

Re: Countif

Thank you, thank you, thank you! Works great!

Bye for now...

Re: Countif

Wigi:

Perfect! Works great. Thank you so much!

I guess I got mine posted right after you did.

In any case, if anyone has an idea for the SECOND formula (post directly above this), I'll be well on my way, thanking all of you once again!

You all rock! : D : D : D

Re: Countif

Since no one has responded yet, I'm going to ask for a SECOND COUNTIF related formula ... another more complicated one.

How do I ALSO write a COUNTIF formula to compare one column to another column, and if there are any MATCHES in the columns to assign a value of 1.

EXAMPLE:

Column A

Bees
Monkeys
Cows
Dogs
Cats
Mice

Column B

Cows
Zebras
Cats
Tiger
Cheeta
Rat
Horse

The output value would be numerical (2) because both columns have Cats and Cows. One column may have more data in it than the other, which means the formula may find two BLANK rows (technically a match), but I do NOT want to count blank rows, only matches with content in the cell.

So, I need TWO different formulas for COUNTIF (probably nested) if possible. I would greatly appreciate it. This may be an INDEX(MATCH(( type of nested function, but again, I can't seem to get the correct value no matter what I try?

: D

How do I write a nested COUNTIF formula (assigning a value of 1 for each cell) for a range of cells that does NOT contain "", "OFF", or "REQ OFF" ?

For example:

A row of 20 cells (A1:A20), 5 are blank (""), 3 contain the text "OFF", 4 contain the text "REQ OFF", and the rest contain anything other than the three values just mentioned.

I need a sum number for all the cells in that row that contain any other value. In this example, the output value should be 8. Out of 20 cells in the row, a total of 12 cells had either "", OFF, REQ OFF, and the remaining 8 had some other value that did not equal those.

Some sort of COUNTIF(AND(... Can't seem to figure this one out.

Thank you so much!

Re: Color Formatting & Transferring

Hi DOC: You Rock!

I'm a poet ... no seriously, that works great. You're a genius. I can take it and tweak it from here.

Thank you so much! : D : D : D

Re: Color Formatting & Transferring

Checking it out now ... thank you so much! Will post back soon...

Re: Color Formatting & Transferring

Thank you Doc!

That would be very kind of you - much appreciated! : D

Re: Color Formatting & Transferring

DOC:

Thanks! Please re-read post of mine directly above yours. This is very clear. You may have posted this while I was posting my second response (with more clarification)?

My goal is clear, not confusing by any means. The functionality I'm seeking just may not be possible to do in Excel without VBA, hence the purpose of inquiring with some of you masterminds and Excel gurus out there.

Since I'm not a VBA syntax writer by any means, if anyone has any simple code to accomplish this I might be able to tackle it. If it requires a complex Macro, it's beyond my skill level.

Re: Color Formatting & Transferring

Thank you ByTheCringe2:

Here's the requirements spelled out in more detail, and perhaps you can suggest a solution:

SHEET 1:

Using a simple grid of Monday through Sunday, with A.M. and P.M. in 2 rows, I need to set up availability for certain staff members:

In a drop down list if they choose a certain code such as "AD", it means available that day. "AN" means they can work that night.

If when writing a schedule I choose "AD" in the Tuesday AM cell, the cell color turns yellow for that cell (the conditional formatting), which is a more visual aid to tell me they can work during the day shift.

If the cell is left blank, it turns BLACK (as in black out - they can't work).

This sheet name is "Availability"

SHEET 2:

This sheet name is "ENTER SCHEDULE"

Now, this table calls the name of the individual from "Availability", and shows a similar grid (calling certain values from "Availability"), but its drop down arrows contain names of shifts I want to consider putting them in ("Opener", "Mid 1", Mid 2", etc.). There's some 10 options to choose from for the names of various shifts. Thus, ideally, if the cell color was YELLOW, I would know immediately that I can schedule that person for DAY related shifts only (this would prevent me having to toggle back and forth from sheet to sheet, printing it out, etc). If the color was blue, then I know NIGHT SHIFTS ONLY, if black, I won't even consider them for that shift, etc.

Because drop down lists (or "data validation" from a list) will not let you have a formula in that cell, I can't choose "AD", and "Mid 1" in the same cell.

Any ideas for workaround to accomplish the same thing?

Thank you!

Re: Color Formatting & Transferring

Thank you Roy!

The problem is that there is already some data validation (drop down lists) going on in the cells on the second worksheet. The color of the cells on the second worksheet will tell me which group of options I have in the drop down arrow in that same cell. I have to do some initial calculations in one cell that generate a color value, and then somehow call that color value to another cell with drop down list.

Any other ideas, or if writing a macro, some starter code?

Thank you!

Hi All:

Is it possible to take the color generated from conditional formatting in a cell on sheet_1 and have that color value be painted or transferred into a cell on another (sheet_2) without writing VBA/macro code?

For example:

Cell B32 on Sheet_1 has conditional formatting that when TRUE turns the background cell color to GREEN. I also have it formatted to turn RED when another condition is TRUE. I want cell C1 on Sheet_2 to call whatever color value is in Sheet_1!B32 and show it on the other page.

Don't know if this is possible or how to do it?

Thank you!

Re: Round to Specified Criteria

Both work great!

Thank you, thank you! Problem solved. Much appreciated!

EDR : D

Re: Round to Specified Criteria

RE: Add-on

Hi Bill (or anyone else):

If I was to add one more piece of criteria to the formula, how would I do this?

If the whole number to the left of the decimal is >=50 then ROUNDUP to the nearest dollar.

So, I would use your original rounding formula for anything < $50.00. Anything more than or equal to $50.00 would simply ROUNDUP to the nearest dollar.

How can I write this into ONE formula?

Thank you so much!

EDR : D