Indirect value changing?

rcrummett

New Member
Joined
Jan 24, 2005
Messages
5
I don't know if indirect is the proper term to use; I refer not to the function but rather to what, in my mind, I am trying to do.

Is there a way to change the value of a cell from another cell, specifically in an IF function? For example, suppose cell A2 has something like:

=IF(A1="Value", "True", (change value in B3))

So we are looking at the value of one cell (A1) from another cell (A2), but if a condition is not met we change the value in yet a third cell (B3).

Can this be done? I want to say yes, but I don't know how. Thanks in advance.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
Office Version
  1. 365
You cannot use a formula in one cell to change the value of another.

That being said, what exactly are you trying to do? Shouldn't your if statement instead be in B3?

such as B3 = if(A1="value", sum(B1:B2),sum(B1:B2)*5)

And, A2 = if(A1 = "value", "true", B3)
 

rcrummett

New Member
Joined
Jan 24, 2005
Messages
5
The question may seem a little bizarre; it is actually one little piece to the puzzle I am working with.

I am trying to put together a "Smart" checkbook using Excel, one where I can enter the standard information such as date, payee and amount, but also the type of transacton (check, ATM, online, etc) and what the transaction was for (groceries, oil change, etc). Using this information, I want to generate multiple reports, such as how much was spent on groceries during a certain period of time. I figure one way I can do this is to scan the original register (Sheet 1) for any transaction categorized as Groceries and IF the transaction category matches, the information is copied into another sheet (let's say Sheet 5). I can do it where I write an IF statement that will copy the information, but if the transaction does not match the category, I get an empty row in the Groceries report. What I want is to look at a line and if the category matches, the information is copied; if not, then I look at the next line.

I was coming up with a way to do this where I would enter the starting row number (say, in cell C1) and use that number to generate the address using the ADDRESS function (let's put that in C2). Then I was planning on using the INDIRECT function (in cell A4) to call the address in C2. If the categories did not match, then I would increment the value in C1, which would in turn change the value in C2, and so on...as I think about it more, I think I would need a little more at the end here, but that's beside the point. What I really want is a "loop" that checks for a match and copies if yes, but moves on if no.

There! The long answer to a short question, but I hope it helps shed more light on what I am trying to do. Any suggestions? Thanks in advance.
 

Forum statistics

Threads
1,147,691
Messages
5,742,659
Members
423,746
Latest member
Joaogomes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top