# Posts by Webbers

• ## Multiple Concatenate Based On Len

Re: Multiple Concatenate Based On Len

Brian---

That works a little. I did change the R1 reference at the end to make it absolute. The L portion works find, but the C portion (Column W) doesn't display a leading 0 if it is only 1 character.

=IF(S4="","","U"&V4&"C"&IF(W4<10,"0","")&W4&IF(X4="","","L"&IF(X4<10,"0","")&X4)&\$R\$1)

• ## Multiple Concatenate Based On Len

I have a formula, and it works okay, but I have to change it based on how many characters appear in a cell. I would like to avoid this, as there is just too much room for error.

The end result of my formula is to get a result like the following: U1C01EP or U1C01L04EP depending on which columns are completed. But the "variation" on the number of characters in the cell (1 or 2) and accounting for the leading zero is what is getting me confused.

In cell R4, I have the following formula:

=IF(S4="","",IF(X4="",CONCATENATE("U",V4,"C0",W4,\$R\$1),CONCATENATE("U",V4,"C0",W4,"L0",X4,\$R\$1)))

Column V always contains only 1 character, so this is not an issue. "U" will be added before the contents of this column in the formula
Column W will contain either 1 or 2 characters. If there is only 1 character, the concatenate needs to add a leading 0 following the "C" and then the contents of column W.
Column X will contain either 1 or 2 characters. If there is only 1 character, the concatenate needs to add a leading 0 following the "L" and then the contents of column X. Now this column may also be blank, if that happens, then there should be no "L" within the result, that portion would be left off.
Cell R1 contains the code that is added to the very end of my result. This code will change, so it is placed in a fixed location to be added to the end.

And I would like to keep the IF statement that I wrote accounting for Column S being Blank, yielding a blank cell completely as a result.

I spent 1/2 of yesterday trying to figure this out on my own, and last night this was all I could think about during dinner! Any help that could be offered would be greatly appreciated! Thanks so much in advance!

• ## Vlookup Returns 0 When Blank

Re: Vlookup Returns 0 When Blank

Okay, that is just freaky! While I am posting my response, you posted to try a 2nd formula (which I didn't see until I posted my response about figuring it out.

• ## Vlookup Returns 0 When Blank

Re: Vlookup Returns 0 When Blank

OMG! I just figured it out, and it works!!!!

=IF(\$E11="","",IF(\$E11="~ None ~","",IF(VLOOKUP(\$E11,Projects!\$F\$3:\$T\$226,5,FALSE)="","",VLOOKUP(\$E11,Projects!\$F\$3:\$T\$226,5,FALSE))))

But without the awesome people at OzGrid, I would never be able to do this. I hope that this solution helps someone else. Cringe I owe ya a cup of coffee for your help, especially this early in the morning. Thanks as usual!

• ## Vlookup Returns 0 When Blank

Re: Vlookup Returns 0 When Blank

That formula still gives the 0 rather than the blank.

• ## Vlookup Returns 0 When Blank

Re: Vlookup Returns 0 When Blank

Quote from ByTheCringe2

And what do you want it to show?

Sorry about that blonde moment Cringe! Need more coffee! I want to cell to display a blank rather than the 0 that is currently displaying.

• ## Vlookup Returns 0 When Blank

I have a formula that references another worksheet. Using the VLOOKUP function, it works great, except if the data on the other worksheet has a blank cell, it then returns a 0 (zero). Within the formula below, cell E10 is my "unique key" and is the only raw data within this spreadsheet. All other cells are strictly formulas. This formula I am using below works perfectly if there is complete data, but that is not always the case. Thanks in advance for all your time and effort with my question, it is greatly appreciated! :p

=IF(\$E10="","",IF(\$E10="~ None ~","",VLOOKUP(\$E10,Projects!\$F\$3:\$T\$226,5,FALSE)))

• ## Tracking Evil Doers

Re: Tracking Evil Doers

Ranger----

Thanks so much! That works like a dream! I am just learning VBA, and so excited about what you can accomplish with it.

• ## Tracking Evil Doers

Re: Tracking Evil Doers

Okay, this is extremely cool. I was looking for something like this. but I want to know is if it is possible to have the VBA code list everything in seperate columns rather than seperating with that little square box symbol. Basically I need a nice clean log in xls format. Thanks so much!

• ## Vba, R.i.p?

Re: Vba, R.i.p?

I myself was "anti-ribbon", but I have been using it (Office 2007) at home on a daily basis (and use 2003 at work), and I find the other features definately worth while. While the new interface is a complete 180 from what we were all used to, I have been getting used to it. My biggest issue was the ability to customize. I like having "my" version of Excel with all my customizations. It allows me to work effectively and efficiently. thanks for sharing those links, I am loading those pages as we speak.

I start my day of work with a cup of coffee and a bit of Oz. it is a healthy diet for an Excel junkie! : D

• ## SUMIF Less than or Equal to a date

Re: SUMIF Less than or Equal to a date

Andy----

Okay, I just plain don't get this. I used your "smaller" data formula, and it worked. Then I slowly increase the data range until it encompassed my entire data area, and now it works again. What is up with that? I am not sure what the problem was, but it is now working perfectly. I hail to OZ (as usual)! I would never have thought of reducing my data size within the formula. Andy, thanks so very much!!!!!!

• ## SUMIF Less than or Equal to a date

Re: SUMIF Less than or Equal to a date

Okay, I need help again. This formula has been working, and rather nicely. But now, over the last week, I am getting a #VALUE! error message, and I haven't a clue as to why. I have verified that ONLY numbers appear in column B, all formatted as numbers as well. I am at a loss as to why this working formula would stop working and result in an error. Any words of advice on resolving this woul;d be greatly appreiated! Thanks so very much!

• ## Naming a tab from a cell

Re: Naming a tab from a cell

I have been wanting to do this for a while. This is super cool by the way. I found it works perfect both ways, for the existing woksheet and adding a new one.

Now, my twist is a little different, I have a name listed in cell E5, that I would like the tab name to be. It is not quite as simple as changing the cell location. This cell gets data from a formula, and displays the name in text. So instead of a value, the data is retriving the data from a fromula. Can this macro be adapted. If you require an example worksheet, I can provide that tonight, once I get home. But any guidence you can offer would be appreciated! Thanks in advance for all your time!

• ## Attached Sample - Calendar

Re: Attached Sample - Calendar

That is just too cool! I had often thought about how would I create something like that. But I took the easy way out and added things to my Outlook. But I could easily use this for MANY purposes! You will never find a better answer to a "challenge" then here in Oz! : D

• ## Thank You and goodbye

Re: Thank You and goodbye

Quote from Will Riley

:roll: Don't think so. Excel on it's own is no longer challenging.

I cannot imagine ever being able to say this statement and actually mean it! But in addition to the massive amounts of knowledge that I have gained, I have made some awesome friends. I bow to the Excel Masters! Dennis, perhaps you will consider "popping in" from time to time to let us know how you are doing with your new challenge. :ole:

• ## Thank You and goodbye

Re: Thank You and goodbye

I could not agree more. I was always fairly good at Excel. But since I discovered this forum and started reading on my own time, I would honestly say that my Excel knowledge has tripled or better. There was a time that I could not spell VLOOKUP (well, I AM an IDIOT!), and now, not only can I spell it, but I use this function (along with many others) and better yet.... I can nest multiple functions.

I would honestly be lost without OZ. Dennis we will miss you. You have been a great asset to the forum, and to all the members. We wish you luck. And if you have any plans to get rid of any old Excel Books, CALL ME!

• ## SUM, VLOOKUP & CONCATENTATE in Formula

Re: SUM, VLOOKUP &amp; CONCATENTATE in Formula

Quote from Fin Fang Foom

=IF(ISERROR(DOLLAR(SUM(VLOOKUP(\$D3,\$J\$3:\$L\$10,3)*(\$G3*\$C3*\$E3)))&" per "&VLOOKUP(\$D3,\$J\$3:\$K\$10,2)),"",DOLLAR(SUM(VLOOKUP(\$D3,\$J\$3:\$L\$10,3)*(\$G3*\$C3*\$E3)))&" per "&VLOOKUP(\$D3,\$J\$3:\$K\$10,2))

Okay, I am finally back from my errands. The first thing I did, was check the forum! Okay, I tried the formula above. But using it in my example I posted is not calculating properly.

It displays a few errors in calculations. I have manually calculated the correct "cost per" and listed that in the far right column.

Strrike that! I fixed the error! Okay, the problem was in the

Code
``(\$G3*\$C3*\$E3)``

and after calculating it manually, I then realized WHY there was a problem. that portion should be

Code
``(\$G3/\$C3*\$E3)``

An d then, it matches my manual calculations perfectly! The adjusted formula, in case anyone else can benefit from this is:

Quote

=IF(ISERROR(DOLLAR(SUM(VLOOKUP(\$D3,\$J\$3:\$L\$10,3)*(\$G3/\$C3*\$E3)))&" per "&VLOOKUP(\$D3,\$J\$3:\$K\$10,2)),"",DOLLAR(SUM(VLOOKUP(\$D3,\$J\$3:\$L\$10,3)*(\$G3/\$C3*\$E3)))&" per "&VLOOKUP(\$D3,\$J\$3:\$K\$10,2))

Super Special Thanks to both StefanG and Fin Fang Foom! Thanks so very much for all your help. This challenge was so difficult for me because of all the variable to include. Thanks again!!!!

• ## SUM, VLOOKUP & CONCATENTATE in Formula

Re: SUM, VLOOKUP &amp; CONCATENTATE in Formula

That works, partially. I put the formula into my "live" spreadsheet after adjusting the formula, and I noticed a few things.

N/A is displayed if the data in the Size (Column C) or Multipack (Column E) are blank.

#VALUE! is displayed if the value in Price (Column G) is blank.

Also is there a way to display this data in a currancy format? I have tried chaning the format but it does not work right. If this cannot be done, I may need to add an additional column for the Conversion type (i,e, ounces), and revise the current formula to exclude that information. Would this be the best way to diplay my information in Accounting format??

Thanks so much for all your help! I have a few errands to do, but I will be back soon. Any help you can offer, and opinions are greatly appreciated. Thanks again!

• ## SUM, VLOOKUP & CONCATENTATE in Formula

I am at a major impass on figuring this out. And where can I get an answer, OZ of course. This has really made me feel like an idiot!

Okay, here is the deal. What I am trying to do is get a "price per" on various items. I am breaking down most things per ounce. I have created a conversion table (J3:L10). I thought that I could use this for a VLOOKUP. This table contains:

Original (Column J)
Convert to (Column K)
Conversion Rate (Column L)

I am trying to populate cells H3:H10 with the formula (one it works). Now the current Packaging is located in D3:D10. I want to use that data to locate in the conversion table (Column J) the same amount.

Also multiply it by the current price (Column G), it needs to be multiplied by the current size (located in Column C) and then the Multi-pack column (Column E).

The end result would use the Concatenate function to display the result along with the "Convert to" that is listed in Column AJ of the Conversion Table.

In addition, as you can tell from the conversion table, I need to convert most items to ounces. So I need to ensure that the calculations are correct.

I have been working on this for a few hours, and well, obviously I have not gotten the result I need, as I am posting. Currently the formula I created is:

[CODE]=SUM(VLOOKUP(\$D3,\$J\$3:\$L\$10,3)*(\$G3*\$C3*\$E3),CONCATENATE("per",(VLOOKUP(\$D3,\$J\$3:\$L\$10,2))))[CODE]

And as you can guess, the result is #VALUE! (Thems fightin' words!). I am still getting the hand of how to string multiple functions togather properly. Any help you can offer would be appreciated. Oh, and one other thing. I want to be sure that if the values in Column C, D, E or G are blank, then an error message is NOT displayed. And sometimes the price in Column G is listed as 0, so the cost per in Column H should result in 0, right?

I have attached a sample of my workbook so you can "see" what I am attempting to accomplish. Thanks again!

• ## left command causing trouble

Re: left command causing trouble

Quote from Panoramix

I wrote simple program which do one thing: it goes through number of cells in particular column and and cut first 10 chars from following 15/01/2006 13:28 so there is only date left without the time.

Okay, I am still learning VBA, but I also have a need to do things in a simple manner. For this type of task, as a member of the Idiot Club, I would (personally) perfer to use ASAP Utilities Add-in which is FREE. Based on your original post you need to cut 10 characters. ASAP Utilities allows you to select the cells and then select "Delete Number of Leading Characters" from the ASAP menu and then you enter 10 for the number you want, and instantly DONE!

I have a need to not make things more difficult than they need to be. For me, ASAP Utilities is a must-have add-in that I would be lost without. And the fact that it is a free add-in is much appreciated by poor idiots, like myself. I do hope that this information is of use to someone else.