Posts by restlessheron19

Walter Jackson48 said:It looks like this:=IF(A1=1;"one";IF(A1=2;"two";IF(A1=3;"three";IF(A1=4;"four";IF(A1=5;"five";IF(A1=6;"six";IF(A1=7;"seven";IF(A1=8;"eight";""))))))))&IF(A1=9;"nine";IF(A1=10;"ten";IF(A1=11;"eleven";IF(A1=12;"twelve";""))))

Right... I tried to stitch those together using that logic... like this...

=IF(B4>$H$15;$I$15;IF(B4>$H$14;$I$14;IF(B4>$H$13;$I$13;IF(B4>$H$12;$I$12;IF(B4>$H$11;$I$11;IF(B4>$H$10;$I$10;IF(B4>$H$9;$I$9;IF(B4>$H$8;$I$8;""))))))) )&IF(B4>$H$7;$I$7;IF(B4>$H$6;$I$6;IF(B4>$H$6;$I$6; IF(B4>$H$5;$I$5;IF(B4>$H$4;$I$4;IF(B4>$H$3;$I$3;IF(B4>$H$2;$I$2;IF(B4>$H$1;$I$1;""))))))))

But... there are too many arguments here... and it just isn't working.

Since I'm just doing this for my own use, getting the first eight functions right should be enough.
I'll handle the other eight differently... by using some sort of criteria override.

I put together my own version based on the LOGIC FUNCTIONS IN Microsoft Excel 2003 - example no. 5 and it’s working exactly how I want with 8 functions included.
The problem is, I can't seem to expand the formula.
Whenever I try to scale it up to 15 functions using the concatenation operator &
things go sideways. For instance, if I input the "highest" amount... instead of just seeing "1500" as the result, the cell where the formula lives ends up merging two different cells... so rather than a clean 1500, I get something like 1500700 or 1400700.

Yeah... this forum is running slow today...

=IF(AND(A1>100,A1 =IF(AND(A1>200,A1 =IF(AND(A1>300,A1 =IF(AND(A1>400,A1 =IF(AND(A1>500,A1 =IF(AND(A1>600,A1 =IF(AND(A1>700,A1 =IF(AND(A1>800,A1

How can I combine these, or is it even possible? These are the formulas I'm using.
I have an amount flowing into cell A1 (this formula checks the value range), and if that amount falls within one of my specified ranges, I want the midpoint value—the one inside the quotes—to appear in cell C3. Any help? Even just some basic guidance would be appreciated.

coastalotter83 said:I'm trying to figure out how to use two different cells in Excel to determine a value in a third one. Basically, an "if" statement—like, if both cell A1 and cell B1 are greater than 80, then set the value in C1 to 0.

Here's an example:
http://www.microsoft.com/en-us/office/excel200.../if-and-or.htm

rednomad3 said:Thanks. One more thing—is there any way to convert those tab stops from inches over to centimeters? If so, how do I do it?

Word > Tools > Options > Advanced > Measurement units > cm

rednomad3 said:Hey everyone, does anyone know how to adjust the indentation size for when you hit tab at the start of a line? Thanks.

Open Word > start a blank doc > type your name > hit the Home key > hit Tab (notice the indent) > check the image; the arrow should make it pretty obvious.

http://www.megashare.com/3858146

Public Sub DeleteRowOnCell()

ERROR Resume Next Selection
Selection.SpecialCells(xlCellTypeBlanks).EntireRow .Delete
Delete ActiveSheet.UsedRange

End Sub

I tried using this macro to handle some data sorting... could use a hand here...
Here’s the scenario:
1. Select column H.
2. Hit the "sort" button.
3. In column H, the blank cells are highlighted in red.
4. This macro is supposed to wipe out those empty cells within the selection—meaning it should delete the entire row.

I'm trying to achieve a specific result, but I've hit a bit of a wall.
If you put an "X" in cells F1 and F16... say you've selected a range of 15 + 15 numbers...
Try deleting the "X" in cell E16 just to see how it behaves.

If you have the first 15 numbers selected initially—so one "X" in F1 is enough...
Then select column C and click the "sort" button.
The colored rows get deleted... but what I actually need is for the numbers in cells D16 through D30 to be wiped out too. Since the cells in column C from 16 to 30 are blank, the whole rows should be going.
Is the command failing because there are formulas in the cells? Or something else?
If I copy column C to a new column—let's say column M—and use "Paste Special" > "Values," then column M won't have any formulas. The macro *should* work then, but it doesn't.
I've tried different formulas, but the result is always the same.

Here is an example: http://www.megashare.com/3858054

brightcyclist2 said:Your Formula 1 logic is solid, but when you're typing a "/" or "//", you have to double-click cell F1 first just to let the system accept the entry.

Thanks... helpful tip...

Since I'm constantly entering things like "/"... I was messing around in Excel and stumbled upon this...
Tools > Options > Advanced > Under "Editing options" - Microsoft Office Excel menu key: /
I just cleared out the "/" from that field, saved it, and now there's no need for the double-click.

Walter Jackson48 said:I’m not quite sure what you’re trying to achieve with this formula. I assume you were messaging someone privately and now you're replying here (unless I somehow missed your previous post)

=IF(F1="/";"1";IF(F1="//";"2";""))

Here is my goal with this formula:
If I type "/" or "//" into cell F1, I want cell K1 to display either a 1 or a 2.

The problem is, if I put the "/" inside quotation marks... I can't actually type the "/" character into the cell itself anymore.
I could technically enter it into the formula bar, but that isn't a practical solution for me.

To prove what I mean... try this... but read this part first.

1. Type a single "/" into a cell. Do this before you enter the aforementioned formula anywhere in Excel.
The character appears fine. Everything works.

2. But once the formula is entered and cell F1 is active—in this specific case—this happens:
When I try to type the "/" again, even without a formula present, the keystroke triggers an Excel shortcut... specifically, it opens the "File" menu. And the worst part? That behavior sticks. I have no idea how to disable it.

So, I have two questions...
1. Regarding the formula itself.
2. How do I stop typing Shift+7 or just "/" from triggering the "File" button via keyboard shortcuts?
Basically,the standard way of typing "/" in Excel just doesn't work for me anymore.

This formula works fine
=IF(F1=1;"/";IF(F1=2;"//";""))

But this one keeps throwing an error
=IF(F1=/;"1";IF(F1=//;"2";""))

Does it just need a quick fix, or am I asking for too much?
Or... is there a better way to get the same result?

Walter Jackson48 said:Try setting your ranges like this: D1:D1000, E1:E1000, and F1:F1000 (rather than just using the whole columns like D:D, E:E, or F:F) and then just drag the formula down to row 1000. At least, that’s if I’m following you correctly.

Not quite... but you helped me out. My laptop screen finally feels bright again...
this formula actually works
=IF(C1="K7";D:D;IF(C1="K8";E:E;IF(C1="K9";F:F;"")) )

So... how do I... automatically apply a specific formula in Microsoft Excel?
For instance:
1. I have a formula sitting in H1—it’s linked to the data in cell C1.

I want to drag that same formula down column H for about 1,000 rows... but I need the formula to adjust its references as it goes.
Basically, the formula in H2 should point to the data in C2, and so on.

What’s the best way to auto-fill this down to row 1,000? I really don't want to manually update the cell references every single time—especially since I have three different conditions to manage here.

Here is the formula:
=IF($C$1="K7";D😁;IF($C$1="K8";E😈;IF($C$1="K9";F: F;"")))

restlessheron19 said:I tried importing, but all the data just ends up crammed into a single cell.
Is there a way to configure this in Microsoft Excel? Specifically under Data > Get External Data > Import Data... to make sure it lands where it should?

Problem solved... just set the delimiter to the "|" symbol.

Could use a little help here.
I'm trying to pull a text file like this into Excel.

The data in the text file is delimited by a pipe symbol "|".
I need each piece of data to land in its own cell... and, obviously, one line in the text file should equal one row in Excel.

For example:
This

A|B|label1|

so it looks like the image.

I tried importing it, but everything just ends up crammed into a single cell.
Is there a way to tweak the settings in Excel? Maybe under Data > Get External Data > Import Data... to make it work the way I want?

rustywalker1 said:What format did you end up using? If you haven't done it yet, just format it in... general

Yeah, it worked... once I switched the formatting over to General.

Quick question..

Here's the deal:
In cell C1, I have a word.. DOORS
In cell A1, I've put this formula.. =C1

I click on cell A1..
Then I go up to the formula bar.. double-click the formula.. =C1
Hit the F4 key
Now the formula bar shows this.. =$C$1
But here's the catch.. cell A1 doesn't show "DOORS" anymore... it just shows the formula itself.. =$C$1

How do I fix this?

I tried changing the cell formatting.. no luck..
I even tried copying an empty cell over the same spot.. same exact problem..
Could there be something going on with the cells next to it?

Walter Jackson48 said:In cell B1, you’d enter this formula: =IF(New York City=0;"";New York City). Basically, if there's a zero in New York City, B1 stays blank; otherwise, it just pulls the value from New York City. Once you drag that formula down the rest of column B, it’ll clear out any cells where the corresponding row in column A hits a zero.

I guess I need to brush up on my IF statements... doesn't look too bad, though..🙂
Perfect. That does it. Thanks!

Kyle Johnson2 said:Check if this might help you: Hide/unhide number of Columns based on cell value or at least give you some inspiration.

Yeah, I took a similar approach, at least conceptually.
I set up two buttons (on/off) to toggle the visibility of certain columns. It works fine for now, though there’s always room for improvement...

I'm also hunting for a formula that would...
clear the contents of an adjacent cell if the current cell value is zero
If anyone has a solution for that...

Walter Jackson48
Yeah, I get where you're coming from... I see your point..
That example you gave isn't quite what I was looking for, though. It just shows the values. What I actually wanted was the ability to edit the cells being displayed. But, no matter—it was still helpful... if we can't find another way around it.

Walter Jackson48 said:If you put either a 2 or a 3 in cell A1, and columns B and C contain the data you want to display in column D, here is how you handle it. In D1, enter this formula: =IF($A$1=2;B🙂;IF($A$1=3;C:C;"")). Then, just drag that formula down to cover all the rows used in columns B and C.

Yeah, that should do the trick... Thanks!