If statement is false skip to next row

[jmack549]

So here's the deal. I've used Excel quite extensively, but not like this when it comes to programming certain things. I'm in a bit of a rut right now.

The company I work for has a calendar made in excel. It just has the date up top, and about 8 empty rows below the date broken into 2 columns. In the left hand column goes the item, in the right hand column goes the price.

This is where I'm lost. This information is changed weekly, and to make it easier on the mangers they want to be able to add info into a secondary sheet and have it autofill. I thought this wouldn't be a problem but it's turned out to be trickier than I thought.

My first thought was VLOOKUP, but that was quickly squashed because the spreadsheet has the date repeated as many times as there are items, so it always just pulls the first item.

Then I went to an IF statement. This worked out well, but since I can't figure out how to tell it to skip the to next row if it is false, by the third day I have a bunch of blanks that won't work.

I'm not familiar with VBA, but I'm willing to try. I am really just looking for an IFFALSE statement that will allow me to skip that row and go to the next one, as opposed to giving me a bunch of blank lines. Any ideas?

Thanks in advance!

[tigeravatar]

jmack549,

Welcome to the forum!

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

[jmack549]

Sure, here you go. This is a very basic example, but you can see the problem I'm running into on the top of Friday the 2nd. It gets worse the further you get into the month, because it constantly checks against the top and works it's way down.

While I'm here, anyone got any ideas to make my layout prettier? I'm having trouble getting everything to fit on one page and look nice.

[jmack549]

I should have also mentioned that VLOOKUP sort of works, but only for a bit since you can't have duplicates. As soon as it finds the date I'm looking for it returns the result, so it continually returns the same item over and over. If there's a way around this I'd be interested as well, I'm not set on using an IF statement.

[NBVC]

Here is one formula-based method.

Add a helper column in Sheet2, starting at D2 with formula:

Please Login or Register  to view this content.

copied down to last row of data.

In Sheet1, B2 enter formula:

Please Login or Register  to view this content.

and in C2, enter formula:

Please Login or Register  to view this content.

copy both down to bottom of sheet (row 63)

Then copy those two columns of formulas and paste to E2, H2, K2 and N2

You may need to re-add borders around each date group....

[Pauleyb]

This may work for you. See attached file. The index and match functions were used.

The REF errors should be removed when you have your larger file, but you can remove them if needed with the iferror command (note, you say you have Excel 2003, but you posted a .xlsx document, so I'm assuming you have 2007 or later).

Note, as you move to different dates, you will have to modify the date reference cell (e.g. J2 in the formula in cell K2).

Pauley

[jmack549]

Wow, you guys are incredible. Both solutions worked perfectly, thank you both.

Is there a good starting resource to learn this stuff? I can see how this would be absolutely invaluable in the business world.

[Pauleyb]

Forums like this are probably the best way to learn. Look for threads that seem interesting and then work to understand the solution. Look at the function list in Excel to understand what each one does. Finally, the 'Evaluate Formula' tool is one of the most useful.

Pauley

PS - please mark this as thread as solved.

[shreevvairam]

Hi,

I am facing Somewhat similar problem.. I tried using different technique but failed to get accurate result as Vlookup,match,index,two way match index , helper column etc ... all take first row in account to update details.

i want to update leave taken by employee on different date in a single row against his name or emp ID. But every time I use the formulas ( Mentioned above) it update only first row. I have attached sample file.

Thanks In advance as I know you will definitely help to solve this very small problem.

Regards

Shree

[Cutter]

Hello shreevvairam, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[shreevvairam]

Hi,

Thanks... I am new to your forum..and apologize for breaking forum rule..I have tried to open new thread but;.......... frankly I don;t know how to start new thread? Can any one guide me?

Regards

Shree

[Cutter]

shreevvairam

Go to the most appropriate sub-forum and in the upper left area click on the "Post New Thread" button. Clearly describe your problem, giving as much detail as possible. Be sure to give your thread a clear and concise title that describes your issue.

[shreevvairam]

Thanks a lot!!!

[dztmond89]

Thanks guys. this is really helpful