# Posts by widgetwonka

Something like this (replace the ranges as needs and names as needed):

• ## INDEX Function within IF Formula

Re: INDEX Function within IF Formula

This will find if any of your conditions are true:

=IF(OR(ISERROR(VLOOKUP("*AAA*",\$A2,1,FALSE))=FALSE,ISERROR(VLOOKUP("*BBB*",\$A2,1,FALSE))=FALSE,ISERROR(VLOOKUP("*CCC*",\$A2,1,FALSE))=FALSE)=TRUE,\$B\$1,"No Match")

• ## Compare Two Columns to Receive a Specific Result

Re: Compare Two Columns to Receive a Specific Result

I can think of two ways to do this.

1. use IF(AND(C2="whatever",D2="whatever"),10,something else
you can nest 7 IFs that way

2. Concatenate the field, then use a lookup to return the value you want
so, Cell E2 formula is =C2&D2
and J2 = IF(ISERROR(VLOOKUP(E2,LOOKUP_RANGE,2,FALSE)),"New String",VLOOKUP(E2,LOOKUP_RANGE,2,FALSE))
Where LOOKUP_RANGE has the combinations you want

• ## Profiling Duration in months between 2 dates

Re: Profiling Duration in months between 2 dates

What is the fiscal year in this case? There are 23 months between the two dates specified, but only 20 in your formulas.

• ## Find row with closest value

Re: Find row with closest value

I found one way to do it. It involves sorting the row in descending order, then copying the row and sorting in ascending order.

My Desc row starts in D16. My Asc row starts in E16. My formula to find the row value is:

=MATCH(IF(ABS(G16-INDEX(E16:E33,MATCH(G16,E16:E33,1)))<ABS(G16-INDEX(D16:D33,MATCH(G16,D16:D33,-1))),INDEX(E16:E33,MATCH(G16,E16:E33,1)),INDEX(D16:D33,MATCH(G16,D16:D33,-1))),E16:E33,0)+15

It is an inelegant solution, so maybe someone else can get you closer...

• ## Auto Scaling & auto formating major/minor grid lines

Re: Auto Scaling &amp; auto formating major/minor grid lines

This is getting close to what you want I believe.

• ## Auto Scaling & auto formating major/minor grid lines

Re: Auto Scaling &amp; auto formating major/minor grid lines

Where does this get you:

replacing the [f1] obviously

• ## Deny save if cell empty

Re: Deny save if cell empty

You will need to put the worksheet name that you want to drive the action in place of Sheet1. You will have to put this in the Worksheet section of the VBE.

• ## Multiple users sending data from UserForms to a single sheet

Re: Multiple users sending data from UserForms to a single sheet

You should be using a database program. You probably could code around this in Excel, but it is going to be more work than it is worth.

• ## too many vlookups in a formula

Re: too many vlookups in a formula

Take a look: forum.ozgrid.com/index.php?attachment/37445/

I didn't change you vlookups...I just made it so you don't have to update anything. Just add the Rd# sheets and the sheet will auto populate.

The trick here is to use INDIRECT().

Also, you could easily add 6 to all your lookups. Don't do it in the cell, do it via a reference. That is what I did for your formulas in this workbook. I didn't have to, but I figured it would be illustrative..

• ## Auto Scaling & auto formating major/minor grid lines

Re: Auto Scaling &amp; auto formating major/minor grid lines

I am not terribly familiar with the chat object, but it seems your reference to [f1] needs to reference a value like range("F1").

• ## Formula to deduct value from one cell and add to a different one

Re: Formula to deduct value from one cell and add to a different one

B3
=IF(AND(Details!A2="Apples",Details!B2="A",Details!D2="ordered"),0,-Details!C2)

E3
=IF(AND(Details!A2="Apples",Details!B2="A",Details!D2="Ordered"),Details!C2,-B3)

You could substitute "apples for whatever you want, like a link a cell that has a dynamic value.

You can also add more tests to the AND().

• ## Return a cell value based on 3 text criterias

Re: Return a cell value based on 3 text criterias

maybe you should try the solution I posted to your previous thread:

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

• ## VBA - How to make Evaluate INDEX MATCH work with Variables as Criteria?

Re: VBA - How to make Evaluate INDEX MATCH work with Variables as Criteria?

break it into two parts:

Code
``````dim str1 as string
dim str2 as string
str1 = worksheetFormula("INDEX(PromoNumber&""2"",Promotions1&Promotions7,0)")

str2 = Application.Index(promotions8,str1,1)

EditPromo.ProdNo2.value = str2``````
• ## Copying New First/Last names from Roster and Inserting into New Rows in diff sheet

Re: Copying New First/Last names from Roster and Inserting into New Rows in diff shee

application.screenupdating = false
application.screenupdating = true

The 0s and 1s are for determining if a value is in the list. for the delete list, it determines if the value is in the imported data. for the addNew, it determines if the the entry is already in the caseload sheet.

The formatting is in reference to your desire to format the rows afterwards. You can record a macro to do it, and copy down any formulas that you have.

I used the offset function so that when you delete a row, you don't create a reference error in the mechanics sheet.

• ## Dynamic Chart based on drop down selection

Re: Dynamic Chart based on drop down selection

I see what you mean after looking at how the data is setup...I would advise changing it to be in a flat database format. It will be tremendously easier to work with.

Here is the solution:
forum.ozgrid.com/index.php?attachment/37421/

It uses INDEX(INDIRECT()) to get what you need.

• ## Checkbox activating sub function

Re: Checkbox activating sub function

Glad to help. I finally joined this site after years of coming here to get quick code. I am happy to see I can give back a little.

• ## Copying New First/Last names from Roster and Inserting into New Rows in diff sheet

Re: Logic Process

I added a sheet called mechanics that will do the sorting for you. I have written the code to compare the old and delete and then add the new:

So, just run compareAndContrast and it will do the rest.

I ran out of steam for the format...I'll let someone else tackle that

forum.ozgrid.com/index.php?attachment/37420/

I had to zip it...I added an arbitrary number of comparative rows, you can adjust if you want it bigger/smaller...

• ## Checkbox activating sub function

Re: Checkbox activating sub function

OK, I have changed your code somewhat. The problem was with how you were using the form. While segregating code is a good idea, skipping from the form to the modules, etc and using the same variable names defined several times was causing the form to think that one of your check boxes was always on, and the other was always off, because FEthernet was used for both boxes.

Also, I changed the directory structure so I could use it on my computer. You will need to change it back in your worksheet.

Here is the code:

Sheet12 private sub:

Code
``````Private Sub BtnCreate_Click()
Call Generate

End Sub``````

frmstart code:

principalmod code:

FastEthernetmod was redundant at this point, so I deleted it.

forum.ozgrid.com/index.php?attachment/37418/ -- here is the workbook as well. Don't forget to change the directories back.

In summary, when using a button on a form, drive the actions within the button code. Don't reference back to a module that also uses the same variable.

Hope this helped. Sorry if I inadvertently deleted some functionality. You can add it back, just do it in line with the form.

• ## Get a cell value based on multiple criteria

Re: Get a cell value based on multiple criteria

Steps:

1. Make a pivot table of the Source data. Include all the rows you want, just de-select blank in the drop down later.
2. Use GETPIVOTDATA for the rest:

Original formula:
=GETPIVOTDATA("Sum of Product",\$A\$3,"Company","Astrix"," Service"," Posting ")

Formula that is driven by Customer Name:

=GETPIVOTDATA("Sum of Product",\$A\$3,"Company",B3," Service"," Posting ")

The GETPIVOTDATA formula can be manipulated a number of ways. So, if you don't want posting, just change "posting" to the service reference: you are done. Go have a beer and tell the boss you worked on it all night.