Question about Excel and Formulas

[jaberwacky]

Hi.  I'm trying to create a spreadsheet which calculates the price of grocery items per ounce amongst other calculations.  I would like to have one formula be applied to an entire column.  I want this so that if I change the formula then I only have to change it once and the update is applied to the entire column.  Is this possible?

[Mat]

Not that I know of, but that's not really how you'd work in excel. Can you really not do it with just extra parameters?

I guess AutoIt could do it

[jaberwacky]

Extra parameters?

[Mat]

Extra parameters?

Can you give an example of where you'd want to change a formula after you'd written it the first time?

[jaberwacky]

I think I'll just stick with the manual way of doing things.  Thank you for your time, Mat.

[Chimaera]

If the changes are static but all different you could put them on a different tab and use a box in the equation where you select 1,2,3 or 4 etc to use the changed formula maybe?

equivalent of our If/Then/EndIf sort of

changing the top one and dragging down will work as well

[TheSaint]

Hi.  I'm trying to create a spreadsheet which calculates the price of grocery items per ounce amongst other calculations.  I would like to have one formula be applied to an entire column.  I want this so that if I change the formula then I only have to change it once and the update is applied to the entire column.  Is this possible?

I'm pretty sure you can do that, but it's been a while for me, and I mostly deal in macros with Excel these days, and apply any formulas with that.

Sure to be websites devoted to such things. I've used a few in the past.

[jaberwacky]

Allright, let's say that I have this equation that I just made up: =A:A*P1 and inside of P1 I have some number.  If I put that equation into B2 and drag downwards then the equation in B3 will be this: =A:A*P2.  I want it to be =A:A*P1 in every row of B.

Edit: Typo - Uppercase 3, 

Edited September 20, 2013 by jaberwocky6669

[water]

Use absolute cell references:

=A:A*$P$1

Edited September 20, 2013 by water

[jaberwacky]

Thanks Water!

[water]

[Mat]

And there I was thinking you wanted to do something more complicated like that.

[jaberwacky]

Well, at first I did.  But I think I should stick with the basics for now.

[czardas]

You can also highlight the cell containing the sum total. Drag the selection top border upwards to embrace the whole column an the click SUM (or something like that - in office 2003). Insert new items (before the sum total) and it should incorporate the new values and adjust automatically. It's been a while for me too.

Edited September 27, 2013 by czardas

[Bacon]

I am trying to search a given column in multiple excel sheets(in the same file)  for different strings and read cells under any indentified cells with that string, then copy the content of those cells and paste them to another new or designated excel file. Can anyone help me figure out where to start and if I need to put those data into an array to do this?

[water]

Welcome to AutoIt and the forum!

As your question is completely unrelated to the question of the OP I suggest you open a new thread.

Short answer: AutoIt comes with an Excel UDF. If this doesn't fit your needs you could try my Rewrite of the Excel UDF (for download please see my signature).