Excel help

[Hawkbit]

I should know this... but I haven't used Excel in 10 years for anything even remotely important.  Help, please?

I need to create a simple running budget spreadsheet.  In B1 I'll have a starting balance of $1000.00.  In A2 I'll have a deduction, say $100.00, so that in B2 I want it to report $900.00.  And so on,. using only those two columns. 

I can create a formula to calculate that from each individual cell, but there should be a way to have the formula repeat itself so that whatever is added into column A is deducted from from the previous balance in B.  I keep thinking this is handled by the 'range' function, but I can't get it to work.  It's only offering me an 'add' function, and it's not handling negative numbers. 

Halp?  Please. 

[Yoru]

If I recall right, you can highlight the cell with the formula in it, click the little black box in the lower-right of the cell and drag it downwards to populate the column. Excel will modify the formula by incrementing the row ordinal without changing the column ordinal. That is, =B1-A2 will turn into =B2-A3 and so on.\

You can drag across columns to in order to get the appropriate columnar effect.

[Paelos]

If you are trying to copy formulas, Yoru is right. You can simply drag the lower right corner of the box to whichever direction and it will auto-populate the rest of the cells with an updated formula.

If you wanted to hold certain parts of a formula, you can add $ symbols in front of whatever you want to stay the same. $F1 would keep F the same and update the number. $F$1 would keep F1 the same no matter what you copied.

[Hawkbit]

That is almost exactly what I was looking for, thanks a ton.  It's been too long since I've used Excel.   

The only issue with dragging the formula down is that it repeats the current total in the B column for as long as I have dragged it.  Not a huge deal though, you both put me on the right path. 

[Chenghiz]

In that case you could have something like "=b1-a2" in cell b2

[Lantyssa]

I have a related question to populating formulas.

In the A column, starting with 2, I want to enter a value.  So A2, A3, A4, ...  Across row 1, I want to duplicate these, so B1 = A2, C1 = A3, D1 = A4, ...  Is there an easy way to do this?

The purpose is to set up a fusion table for MegaTen, if that helps.  Basically Row 1 will equal Column 1.  If I can get that working easily, then the others will be easy enough to set up.

[apocrypha]

Copy column 1, select cell B1 then paste special -> Transpose.  If I've understood what you want correctly.

[Lantyssa]

You do understand.  It's not cooperating for copying an entire column into a row though, and no formulas are being generated.

Maybe I should just make a small database and use php to collect the data anyways.  The matrix manipulation would probably be easier and it'd let me do some nifty stuff.

[Salamok]

transpose only works with a range of cells, I suppose it would work if you could have 65536 columns.  It will also work fine with formulas but remember it copies them as relative unless they contain absolute references, so results may not be as you were expecting. 

[Tarami]

Not sure what you're trying to achieve... do you want the X-axis to map directly to the Y-axis, so if you change a value in A4 (third on Y), the corresponding value in D1 (third on X) will also change? Two linked axises, so to speak? That can be done by;

=INDEX($A:$A;COLUMN(B1);1)

in B1, then giving A2 value 1 and ascending downwards. Drag-scale B1 to the right to copy the forumula.

[Yegolev]

You do understand.  It's not cooperating for copying an entire column into a row though, and no formulas are being generated.

Maybe I should just make a small database and use php to collect the data anyways.  The matrix manipulation would probably be easier and it'd let me do some nifty stuff.

Did you try Paste Special instead of regular Paste?  I'm not sure if you are pasting formulae or values.

[Lantyssa]

Not sure what you're trying to achieve... do you want the X-axis to map directly to the Y-axis, so if you change a value in A4 (third on Y), the corresponding value in D1 (third on X) will also change? Two linked axises, so to speak? That can be done by;

=INDEX($A:$A;COLUMN(B1);1)

in B1, then giving A2 value 1 and ascending downwards. Drag-scale B1 to the right to copy the forumula.

This worked, though I had to use commas instead of semi-colons.  Thanks.

Did you try Paste Special instead of regular Paste?  I'm not sure if you are pasting formulae or values.

Yes, I was using Paste Special.  I had a workaround where I could drag down a column, paste a transpose, then delete the column, but it was clunky and I couldn't expand the formula further.

I wanted to paste a formula, but didn't know the excel terminalogy to do more than $A1, drag that, which just ended up duplicating $A1.

[Draegan]

Necro thread for my own help please.

I need to do a search in a bunch of cells for a certain combination of a few strings and return true if any of them are in there.  I can't seem to get it to work and this seems like it should be simple.

So far I have

=IF(OR(SEARCH("050-*-*",C2,1),SEARCH("100-*-*",C2,1),SEARCH("150-*-*",C2,1)),"True","False")

It seems to work with on search condition but returns an error with the OR statement.  Halp pls.

I've even tried seated IF statements,

=IF( SEARCH("050-*-*",C2,1), "True", IF(SEARCH("100-*-*",C2,1),"True","False"))

Always returns the first condition correctly, but not the second.

[MisterNoisy]

Necro thread for my own help please.

I need to do a search in a bunch of cells for a certain combination of a few strings and return true if any of them are in there.  I can't seem to get it to work and this seems like it should be simple.

So far I have

=IF(OR(SEARCH("050-*-*",C2,1),SEARCH("100-*-*",C2,1),SEARCH("150-*-*",C2,1)),"True","False")

It seems to work with on search condition but returns an error with the OR statement.  Halp pls.

I've even tried seated IF statements,

=IF( SEARCH("050-*-*",C2,1), "True", IF(SEARCH("100-*-*",C2,1),"True","False"))

Always returns the first condition correctly, but not the second.

Try this:

=IF(COUNTIF(C2, "=*mystring1*") + COUNTIF(C2, "=*mystring2*") > 0, "TRUE", "FALSE")

For your specific example:

=IF(COUNTIF(C2, "=*100-*-*") + COUNTIF(C2, "=*050-*-*") > 0, "TRUE", "FALSE")

[Draegan]

It's returning false for each for all instances.

I tried something with ISERROR and OR and it wasn't working either.

[MisterNoisy]

It's returning false for each for all instances.

I tried something with ISERROR and OR and it wasn't working either.

Odd.  It returns true on the sheet I'm working on for

gkljgklfdj100-fdsjfdsj-lf;kjdfksd (*100-*-*)
kfjdsklfj050-fdsfds-fdsfsd  (*050-*-*)

and similar entries.  What are the strings you're looking for?

[Draegan]

Actually it works, my fault for not putting another Wildcard "*100-*-*" infront of the string.  I had assumed it would search the whole strong for such an instance.

I fail at copying.
Thanks for the help.

[MisterNoisy]

Glad to help!  :)