Jump to content
Sign in to follow this  
jaberwacky

Question about Excel and Formulas

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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 :)

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Share this post


Link to post
Share on other sites
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,  :P

Edited by jaberwocky6669

Share this post


Link to post
Share on other sites
water

Use absolute cell references:

=A:A*$P$1
Edited by water
  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

:)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by czardas

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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).


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×