Using VBA to return values from several cells as a formula.

BYahr · #1 05-23-2023, 11:56 AM

I'm trying to get the data from more than one cell to combine in another cell.

Example: In worksheet 1, cells A23, A24 and A25 have dollar values.
In cell Q165 of worksheet 2, the desired result is =value of A23 + value of A24 - value of A25. (or =35+100-60). Showing in Q165 is 75.

p45cal · #2 05-24-2023, 04:03 AM

I don't know what your sheets' names are so you'll need to adjust this:

Code:

With Sheets("Sheet1")
  Sheets("Sheet2").Range("Q165").Value = .Range("A23").Value + .Range("A24").Value - .Range("A25").Value
End With

…and there are many other ways.

ArviLaanemets · #3 05-24-2023, 04:53 AM

Why don't you simply use a formula in target sheet (Sheet2!Q165)

Code:

=SUM(Sheet1A23:A25)

BYahr · #4 05-25-2023, 11:58 AM

Thank you, p45cal. This helps. The result is the correct value but I would prefer to get the individual values in a formula. EX =100+5-1 to display 104. This makes it easy to know where the values originated.

The sheets that I am working with are in two different workbooks.

kilroyscarnival · #5 05-31-2023, 12:00 PM

Oh, you want to actually see the individual figures?

In your destination cell try:

=CONCATENATE("="&A23&"+"&A24&"+"&A25)

That cell will now display "=35+100+-60" but it's merely a text string.

If you wanted to make it a calculable number again, copy, paste values, and then you'll need to do something to refresh that cell for it to convert back to a number where it will display the "75" but in the formula bar you will see the =35+100+-60. You can click inside the formula then out and that should refresh it. My cheat involves having a macro button that runs a macro simply replacing every digit from 0 through 9 with itself, thereby refreshing all numbers.

BYahr · #6 06-06-2023, 11:47 AM

Thank you, Kilroyscarnival.

This helped. Still had to adapt to work between two workbooks, but the cell is working.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to match one list with another and return certain values from corresponding cells in excel	Oluagbe1	Excel	10	02-11-2019 07:00 PM
Loop through cells and return only unique values	trevorc	Excel Programming	4	10-11-2018 03:49 PM
have a formula that combines two cells return a zero instead of a blank cell	Kubi	Excel	3	08-07-2017 11:52 PM
Formula/function to return column names for matching values	GiJoe81	Excel	1	06-27-2015 12:13 AM
Excel Formula: return a range of cells that match	tinfanide	Excel	4	08-30-2014 07:03 AM