Dynamic value used to put results in cell defined by dynamic value

Hi DJ
I don't want anything in column F, I would like to use the value in F dynamically to identify a cell that I will have a value put in. The spreadsheet show the data and I have an explaination there of what I am hoping is possible in Excel

Thanks so much for getting to it quickly

Hi Carrot,

Will this work for you?

In cell G19, enter this formula... =IF(G$11>=$D19,$C19-SUM($G18:G18),0)

PS Do you need the formula to "zero-out" after the due date in col E? if so, try this formula instead... =IF(AND(G$11>=$D28,G$11<=$E28),$C28-SUM($G27:G27),0)

Then copy this formula to all the other cells in row 19 or any of the other cells...

Note: this doesn't use col F which you can delete afterwards...

Sorry no it gave me 0 as the result in G19 for both functions above. I have attached an updated version of the spreadsheet. The explaination there with the data references may help.

Hi Carrot,

You added an column which threw my formula off... check out the attachment to see what I meant... the formula in H19 can be copied to the other cells... (Excel tip: create a formula that will work in all the related cells)

In this attachment, you don't need column G anymore...

Let me know if this is what you want it to look like...

Note: what do you want to do with all the "negative" values at the end?

DJ - You are the best!!!!

I did add a column because I did not like having "I" as the column when I was giving the example - simple things bug me sometimes But I did copy your formula in H19 but I did not think about how that would impact the rest of the code -D'oh!!!

So now for the explanation if you don't mind. I was a programmer at some point in my life and I am very good with math so my mind works logically but I am having a hard time with the Excel Macros and I don't like being 'beat' by code.

Your code as I understand it - please fill in the blanks if you have the time!

=IF(H$11>=$E22,$D22-SUM($H21:H21),0)

If the value in H11 is > or = to the value in E22 - YES - Take the time value in cell D22 and subtract the value in H21 giving difference -- Is this correct?
-NO- Value is 0 the code for this the 0 at the end?

I understand that H$11 says always row 11 but the column will change, $E22 the column stays the same and the row varies and the same for $D22. Now for the Sum part of the code I do not understand why it is written like this $H21:H21 can't you just reference the cell?

Do the IF statements automatically keep working until there is a yes?

Really thanks so much
Yes I do want to tweak it, when the day comes when more hours are allotted than needed I would rather it just put the hours used not result in a negative # and to stop the following 0s. And not to be too picky if the the IF is not met could we just make it a blank instead of Zeros?

I am so relieved to have gotten over this hump! THANK YOU

Hi Carrot,

Correct about the IF statement.

The SUM part is a little trick that "fixes" $H21, so when as I copy the formula across, $H21 stays while H21 changes. If you go in edit mode to each of the formulas going left to right, you see that $H21 stays and the sum range increases.

There is no "loop" in an IF statement. So the IF statement will simply return either a TRUE or FALSE statement.

There are 2 ways to show "blank" instead of zeros.
1. Formula way: change formulas to show =IF(H$11>=$E22,$D22-SUM($H21:H21),"") in other words, change 0 to "" (or blank string)
2. Formatting way: highlight the row of cells, right click, Format Cells, Number, choose Category: Custom, and under Type: #,##0;-#,##0;;

The formatting way would be my favorite since I won't mix numbers with text. We're simply not formatting how to show 0. And we keep the formula as is. Copy the formatting to the rest of the rows.

Read more about Number Formatting because there a lot of symbols, but a quick summary is that there are 4 ways to show a number separated by a semicolon
positive; negative; zero; text

For example, the Accounting category has a format that looks like this... _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

If we break this down (separated by semicolons,
_($* #,##0.00_) how to show if the cell contains a positive number
_($* (#,##0.00) how to show if the cell contains a negative number
_($* "-"??_) how to show if the cell contains a zero
_(@_) how to show if the cell contains a text

In our case, I want you to change the Type to #,##0;-#,##0;;
Which says, show a positive (#,##0) with a comma if greater than 1000;
show a negative (-#,##0) with a comma if greater than 1000 and a negative sign;
show a zero as blank since we didn't type in a format (hence just a semicolon)
show a text as blank since we didn't type in a format (hence just a semicolon)

Note that this will not give you decimal places if you do, you can use General;-General;; where General is smart enough to figure out if you want as many decimal places as you want.

Again, read up on Number Formatting... which I think is a very important part of Excel...

Anyway, let us know if you have more questions, this forum is a great place...

DJ - Sorry for the delay in responding I have been training someone.

THANK YOU!! And for the tips on what to read up on. I can now put this to good use in operations! -- Carrot Top