Excel help

Davoid

Uh oh, I bit off more than I could chew! I need to figure out some excel formulas and I don't know where to start.

I need it to do the following:
Look through a column in a worksheet for a value that matches the value the user inputs
Displays text found in the 4 cells to the right of that value (the worksheet it needs to look through have product numbers and 4 product categorizations next to them)

I am by no means an excel guy, so I don't know what functions could do this (The fun part will happen after I get this part down, when I need to make a series of cells that take the 4 text values displayed and give outputs based on those values).

Any excel gurus around?

Inquisitor77

Sounds like you want to use VLOOKUP. Excel's built-in help should be good enough to explain that one for you. If not, Google is your friend.

Davoid

I've actually almost got it using the LOOKUP function, but something is wrong with the results vector, it isn't giving me the result I'm looking for

It's giving me a result that is actually 2 rows lower than the row I want it to draw from for some reason

Ah, I get it, I can just offset the result to adjust it manually. How do I adjust it to go upwards? It keeps giving me a reference error if I try a negative

Hn, there is no problem with negatives when I look it up, I must be doing something wrong


=LOOKUP($D$1,Catalog!$A:$A,OFFSET(Catalog!$A:$A,0,2))

So I need it to look for the value in D1 in the first sheet in the A column, then show the result that is 2 columns to the right. However, it displays the results 2 rows below the ones I want as well. Not sure what's going on there.

Yet an earlier formula worked well

D1=LOOKUP(C1,Catalog!B:B,Catalog!A:A)

Input the number in C1, and the product name shows up by looking for the number in column B of the sheet, and then outputing the name next to it from column A (I'm not sure why it works).

Wow, ok, nevermind, it just seems to be grabbing a row of values randomly. If I change the input value, it just seems to grab the row value from wherever

And I solved it in a stupid way, got it.

Now I need it to take the keywords, and give a "True" or "False" statement if certain keywords are present
The problem now is, some cells I want it to be true if a word is present OR another word is present
and some cells I want to be true only if more than one keyword is present (that's not too bad, I can just go "=IF(COUNTIFS($A$4:$E$4,"Keyword1",$A$4:$E$4,"Keyword2")>0,"Yes","No")"

Ah, nevermind, got it. I was doing it a stupid way.

Fixed code: "=IF(AND($A$4="Physics",$C$4="Wave",OR($B$4="Energy",$B$4="Force")),"Yes","No")"

I guess this can be locked up. Sorry!

schuss

Use this:
VLOOKUP(LookupValue, Columns1-4ofRangeToSearch/ReturnIn, 4, FALSE)
or =VLOOKUP(D1, Catalog!$A:$D, 4, FALSE)

Davoid

Thanks! It's working now, but I want to fix up the interface a bit

Before, every applicable item would produce a "yes" or "no" response under a criteria box. Unfortunately, there are like, 200 criteria boxes, and searching through them for "yes" or "no" is a pain.

Is there a way I can search the criteria matrix and display only the headings (the cell above the yes or no) that meet the "yes" requirement on a separate page?

I'd also like it if it could check to see if it already has counted a "yes", and would display the next available "yes" in the next cell.

Is this possible?

schuss

Pivot Table!
EDIT - with filter.

Dr. Frenchenstein

pivot tables give me a boner

also people look at me like i just summoned a demon from hell to do my bidding when i show them one.

Davoid

I will do some research! Thanks! If I get stuck again, I'll come back, but it's coming along nicely.

Basically, I am checking all the assets we have on a website, and we have some deals with various scientific textbook companies to link our content to their chapters.
So I have like, 30 textbooks all with various chapters, if you look up an asset number it returns a handful of rough keywords, the sheet should look through the keywords, each textbook chapter has criteria, if the keywords meet the criteria, then I want it to the display the chapter title.

So far, I have the keyword look up and "yes", "no" criteria working. Now I want to make it easy to read before I move on.

EDIT: Ah, I figured out how to make it recognize "yes" as the criteria, but it's only showing the sum of yes. How do I make it show the box below the "yes"? (I moved the chapter headings beneath the results headings to make it easier for the table to recognize the criteria.)

EDITEDIT: Ah, pivot tables don't do that, they just sum, count, etc. Hnnnn....

schuss

Pull in the categories as the side labels, and drag the yes to a filter condition.

Davoid

Would it help if I uploaded the sheet somewhere? I am not sure I am visualizing it correctly.

It seems like this is for numeral values, which I am not really looking for at all. For example, if 2 chapters in the textbook are "yes", it seems to give me "2" as my output, but I want the chapter titles those "yes" are attributed to instead.

Oh I got it to display it, that's cool, but, unfortunately, it doesn't seem to update automatically as the input is changed... hnnnn..

Fixed that with a macro.

Last thing to figure out: How I keep the pivot table always looking for "Yes"

If it comes up that there are no "yes", the pivot table stops searching for it when a new input with "yes" appears

EDIT: apparently I solved that in an ugly way using a place holder cell that is always set to "yes"


And now last problem: I don't really know how worksheet coding works, is there a way I can save the workbook so the coding for the worksheet is always present? Right now, if I save then leave, when I re-open it, the code is gone again and I have to recode it.

aaand figured that out
Ok! My first serious excel file ready to go! Thanks everyone!

Bobble

Well, if you can give us a better idea of the data structure here. I'm not sure how the original data is layed out.

So the first sheet you worked with went: ID#, word, word, word, word, and those were the five cells you needed to look at, right?

but I'm not sure what things look like on your next question(s).

Davoid

Yea, initially I laid it out like that, but I found a system that works, and used some macros to update the pivot tables automatically, now it looks pretty good and gives good data

This was actually a lot of fun to make, to think about how all the different parts of this "machine" work together as a system to give the outputs based on the keyword inputs.

Alpharius

I'm late to the party but my 2c is index matching is better than vlookup

schuss

Dr. Frenchenstein wrote: »

pivot tables give me a boner

also people look at me like i just summoned a demon from hell to do my bidding when i show them one.

You should see what you can do with tools like Hyperion/Cognos. Data is my bitch.

EDIT - if you upload a sample of the sheet with fake data in it, we can work through it. Also - Excel 2007-10 or Excel 2003?

Dr. Frenchenstein

I've used Hyperion, oh i know. my company is picking up Prophix (which is allegedly just like Hyperion), which is hopefully replacing bullshit-ass FRx