# Posts by Seti

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• ## Why it doesn't work for 5 American Dollars

Re: Why it doesn't work for 5 American Dollars

I think that the problem is the way you have created the nested IFs. If the value in R11 is greater than 12, only the first IF is executed, so you would get R11-12, even if R11 > 36, it will not get to that portion of the nested IF statement. Once the first TRUE condition is encountered, it will not keep processing. Maybe try to change the order around to check for the greatest value first. Maybe the MOD function proposed does what you want. You could also make it look a lot more complicated with AND conditions like IF(AND(R11>12, R11<24),R-11...

• ## Vlookup needs revision

Re: Vlookup needs revision

OK, here are the formulas that should work:

In J6 put =VLOOKUP(I6,Budget!\$C\$2:\$EO\$7493,44,1) and copy down
In K6 put =VLOOKUP(C6,Budget!\$C\$2:\$G\$7494,5,1) and copy down
in M6 put =VLOOKUP(C6,Budget!\$C\$2:\$Z\$7494,24,1) and copy down

The problem with column J is that you were looking at column A on the Budget sheet rather than column C, so I changed this and shifted the lookup up column two columns back.

Also in all three formulas, you used relative references rather than absolute references. That is what the \$ does in the cell range. Hope this helps some.

• ## Macro (orother method) to identify duplicates considering 2 columns

Re: Macro (orother method) to identify duplicates considering 2 columns

You could probably do this with conditional formatting as well. Let's assume that you have the data in columns X and Y and that they start in row 3. Create a new rule for cell X3 using a formula and use this as the formula: =COUNTIFS(\$X\$3:X3,X3,\$Y\$3:Y3,Y3)>1. This solution assumes at least Excel 2007 (the version I have) and may not work on later versions as I can not test that. Please make sure that the \$'s are as I placed them. Then choose the fill color or font color of your choice. Then copy the formatting down the column. Good luck.

• ## Hyperlink Function: Cell To Be Top Left

Re: Hyperlink Function: Cell To Be Top Left

Could you just hyperlink to that one cell in the upper left corner?

• ## Need help with a threshold commission formula

Re: Need help with a threshold commission formula

Try this. It is not the best solution, but it will work. In Cells D1:D3 put 0, 2000, 5000, in Cells E1:E3 put 2,000 5,000 999,999, in cells F1:F3 put .35, .4 ,5 and in cell G2 put this formula: =F1*(E1-D1)+G1 and copy it down to cell G3. Put your 8,000 in A1 and this formula in B1: =VLOOKUP(A1,D1:G3,3)*(A1-VLOOKUP(A1,D1:G3,1))+VLOOKUP(A1,D1:G3,4)

• ## Track, average, and graph gas meter readings taken at random times

Re: Track, average, and graph gas meter readings taken at random times

Could you just divide the amt column by the difference in times in the Time column? Assuming your data started in A1, try this in E2: =D3/(B3-B2) and this in E3: =D4/(B4-B3)

• ## Fields That Will Update Each Other

Re: Fields That Will Update Each Other

You may need to adjust Bill's code and change the else to

Elseif Target.Column=2 then

So only columns A and B are affected.

• ## Creating If Statements

Re: Creating If Statements

I'm very sorry, but I can read your response in one of two ways. You could be trying to apply the IF statement to every value in column O, one cell at a time and putting the result in column P, cell by cell. In this case, if you were looking at cells O2:O10, you would get a result in each cell in P2:P10. If so, you would just create a formula in P2 and copy it down the rest of the column.

If instead, you just want one cell in column P to contain the sum of all cells in O where the value is between 5.01 and 150, you could use this in P2 maybe:

=SUMPRODUCT(--(O2:O10>5.01),--(O2:O10<150),3.5*O2:O10)

• ## Creating If Statements

Re: Creating If Statements

So do you need to find all of the values in column O between 5 and 150 and multiply them by 3.5 and then sum just those? Your post is not very clear on what you need.

• ## Rounding Up By A Factor

Re: Rounding Up By A Factor

• ## Summarizing Data With Vlookups Or Sumproduct Formulas

Re: Summarizing Data With Vlookups Or Sumproduct Formulas

Do you have Access available? These sound like simple queries and you could keep all of the data in one or more tables.

• ## Mailing Labels With Table Data

Re: Mailing Labels With Table Data

I still use Word for the mail merge, but point it to my data in a spreadsheet. You need to make sure you have a header row that defines each column.

• ## Sumproduct Error - Adding Column 3 When Criteria Is Met In Columns 1 And 2

Re: Sumproduct Error - Adding Column 3 When Criteria Is Met In Columns 1 And 2

I don't understand what cells you are trying to address. You have headers and various text entries all over the place. Column H looks like only a couple of cells can have data in them from the drop down boxes. Where does the 2 come from in column H?

• ## Minor Formula Error

Re: Minor Formula Error

You must have spaces or text in the cells rather than blanks, otherwise, ByTheCringe2's formula should work. To get around that you could try this modified version:

=IF(SUM(K12:N12)=0,"",(SUM(K12,M12)*I3)+(SUM(L12,N12)*J3))

• ## Color Of Row Headers

Is data filtering turned on, or grouping? Are any row numbers missing?

• ## Run Formula Inside Function

Re: Run Formula Inside Function

This is not my area of knowledge, but maybe look at the CALL and SHELL commands.

• ## Run Formula Inside Function

Re: Run Formula Inside Function

I'm not sure about this, but how about having the function make the StacServer call and return the result of that from the function?

• ## Run-time Error #1004 Method '~' Of Object '~' Failed

Re: Run-time Error #1004 Method '~' Of Object '~' Failed

As a first stab, try turning off the error handler and see what line of code is erroring. That will make debugging it easier.

• ## Run Formula Inside Function

Re: Run Formula Inside Function

I don't have a solution, but a function can not change a different cell, it can only return a value to the calling cell.

Why not have a macro to populate the 200 cells when you need to run it?

• ## Run Formula Inside Function

Re: Run Formula Inside Function

Just a quick observation. It looks like you are trying to use a function to change some cell other than the one the function is entered in. This won't work. Maybe try a macro instead.

Also, try formatting the numeric portion to get a string like this:

"=StacServer|" & name & "!'" & FORMAT(num + psDay,"00000") & "'"