# Posts by boll55

• ## Index match variation

Re: Index match variation

Ok I do have a question regarding the formula you showed me. I modified it slightly for another project I'm working on and I think i need some help with it. I now want it to look up based on a single date. I changed this formula:

=B2&"_"&TEXT(DATE(YEAR(I2),MONTH(I2),DAY(I2)),"m/d/yyy")&"_"&COUNTIFS(B\$2:B2,B2,I\$2:I2,">="&DATE(YEAR(I2),1,1),I\$2:I2,"<="&DATE(YEAR(I2),12,31))

so it shows the whole short date. this works but when i use the other formula with it no data is retrieved. I want it to do exactly what the formula you showed me did but rather by a date not a year.

• ## Index match variation

Re: Index match variation

It's beautiful. Thank you!

• ## Index match variation

Re: Index match variation

Formula works great. The only thing is when it goes into the next year it doesnt start in the next cell. It counts how ever many cells with data from the previous year and then starts the new data. I dont like this. Can this been adjusted?

• ## Index match variation

Hello, quite a while ago I was introduced to the Index match formula, which i am in LOVE with. I discovered a variation of the formula to pull data from one sheet to anther based on data in one cell and drag it down to include all the data that pertains to one cell. I love this formula and use it for almost all of my spread sheets for when I want to look up information on a specific person. This is the formula: (it normally goes by ROWS but I changed it to COLUMNS for this instance.)

=INDEX('ECM Data'!\$K:\$K,MATCH(\$C7&"_"&COLUMNS(\$E6:L6),'ECM Data'!\$P:\$P,0)))

I think is the direction i need to go with but I cant quite figure it out and need a little help. I have attached spreadsheets so you can see the data im working with.

Basically what I am wanting is for each driver to pull up all the idle percentages that fall with in the specific year. I want the first one of 2012 to fall in column E and through column N (possibly further depending on how many there are). Then starting 2013 in the next consecutive cell in column O (respectively) and so on.

• ## Highlight active row

I need to be able to select any cell in my worksheet and it highlight the whole row. I had found a macro that worked when i entered the formula, =ROW()=\$A\$1 into conditional formatting. It worked fantastically but i some how managed to delete the macro and Ive searched the web and cant find anything that works.

HELP!!!!

• ## Index Match with Max Value

Hello,

I need some help with a formla I am trying to create using index match with a max too.

Here is what Im looking for:

=IF(IFERROR(INDEX(Max(Sheet8!K:K),MATCH(C6,Sheet8!H:H,0)),"")<B6,"",IFERROR(INDEX(Max(Sheet8!K:K),MATCH(C6,Sheet8!H:H,0)),""))

even though i feel like this should work, it doesnt. It i take out the max() part the formula works correctly matching the term date of a specific driver. But some drivers are one the driver list more than once and i want only the most current term and hire date.

• ## Hyperlinking with formula to new workbook

Hi, I have, hopefully, a simple and easy question.

I have a formula that give me a hyperlink to a workbook:

a1 is the location where it takes me and although this is FABULOUS i do wish to tweek it just a little bit. I would now like it to not only take me to the workbook but to also take me to the corrct worksheet in that work book. I have been using a formula for creating a hyperlink:

which also works FABULOUS. I now want to somehow combine the two if that's possible...

Make sense?

Thanks!

• ## Macro to Rename sheets based on cell value

[h=2]Hi I really need some help![/h][INDENT]
I have a workbook withover 2000 sheets. In each sheet in D100 I have the value that i want that sheets name to be..

HELP! [/INDENT]

• ## Multiple Lookup

Re: Multiple Lookup

Works great thank you so much

• ## Multiple Lookup

Re: Multiple Lookup

Here is some sample data with some explanations

• ## Multiple Lookup

Hello...

What I am trying to accomplish seems simple but I am totally stumped.

I have data rainging from columns L2-AE31.

In Column M I have a series of numbers and in every 4th column after that I have numbers (IE: Q,U,Y,AC. and these numbers are from smallest to largest)

In the column to spaces from the number i have a name. (Columns O,S,W,AA,AE) I need to match the names to the numbers in a different sheet....Any ideas?

http://www.excelforum.com/exce…tml?p=3333310#post3333310

http://www.mrexcel.com/forum/e…l-lookup.html#post3526148

• ## Matching Hyperlinks with an if statment

MAJOR PROBLEM!!!

Help me [Blocked Image: http://www.excelforum.com/images/smilies/frown.gif]

Here the situation I have a list of around 2000 hyperlinks from my workbook and its various sheets. I have a pre made list of all the hyper links that there should be unfortunelty you know how things go and Im off by some here and there. What I need to figure out is how to match up my hyper link list to my pre made list. Im thinking an if statement looking for keys (truck #, Trailer #, Date) to get me the closest matches I can without having to go through every single hyper link.....of course other ideas are welcome....

Im thinking i need to match the criteria (truck #, Trailer #, Date) to that of the data i got from the hyper links. Ill explain more on my attached work sheet so it is more clear to under stand.....Thanks!

• ## Countif/Sum Product

Hi...I guess im not really sure what my question is so im going to throw out my information and hopefully some one can help solve my problem!

On my attached sheet you will see that my data is laid out pretty basically. At the end of each section (this is just one section there are 5 more) there is the results. In the OOS column her i need it to asses how many X's there are and times it by 2. (Simple got that nailed) The the next column here i need a sum product formula which takes the number in the cell and multiplies it by the number in the header row above it. I get the sumproduct but when there are X's in that row it doesnt read them and my results dont come up correctly which affects my third column which is a combination if statement that adds time weight based on the date of the entry.

So what im needed i think is a solution (I am open to changing/adding any and all of my formulas) to my X situation. Im not really sure where to go from here. Ive tried a bunch of different tricks and back road ideas but nothing seems to be working.

Thanks!

• ## Index Match Based on a formulated value

I really enjoy using index match for look up purposes, i find it very useful but i am struggling with this one instance. I have a formula in column T that reads =LEFT(U7, LEN(U7)-1) and generates a number for me. I want to Index match off of this number but it is not working and only returning N/A. I know my index match formula is correct but the only thing i can assume is it cant generate off this formula but i generate off of formulas all the time so idk why this is an issue this time. Please help!!

Thanks!

http://www.mrexcel.com/forum/e…ed-value.html#post3483369

• ## Pulling unique data from a list

I have a generated list that pulls data from a diffrent sheet. I need a formula to pull only unique values from that generated list since some values are list multipul times.
Thanks!

• ## Moving cells from one sheet to another based on meeting critera

Re: Moving cells from one sheet to another based on meeting critera

forum.ozgrid.com/index.php?attachment/49857/Hi I have some more questions on this. I came back today to do some over looking of things and none of my fomulas are working at all.......I have no idea what i did or whats wrong.....Ill post a sample book and maybe you can help me again.... jeepers

• ## Pulling information from one sheet to another

Re: Pulling information from one sheet to another

I figured it out!

you are my excel garudian angel

• ## Pulling information from one sheet to another

Re: Pulling information from one sheet to another

ok everything works awesome until i go to copy it it the next row of data and i dont get any results back...

is there something i have to change? I realize i have to change the location of where the dates are for it to look up the next set of information but ive done that and it doesnt seem to be working....

something else i need to change maybe??

• ## Pulling information from one sheet to another

Hi quick simple question.

I need to pull information from one sheet to another based on a date.

I need all Physicals, CDLs and Haz Mats that are before (But not equal to) the date of 2/1/13.

I want to be able to change this date every month so im thinking some kind of helper cell with just the date and the formul would refernce that cell would be nice.

I have a attached a sheet.

forum.ozgrid.com/index.php?attachment/49823/