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?
Posts
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!
VLOOKUP(LookupValue, Columns1-4ofRangeToSearch/ReturnIn, 4, FALSE)
or =VLOOKUP(D1, Catalog!$A:$D, 4, FALSE)
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?
EDIT - with filter.
also people look at me like i just summoned a demon from hell to do my bidding when i show them one.
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....
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!
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).
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.
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?