Converting nested if to a macro??

Dell / Vostro 1500
March 11, 2009 at 03:48:06
Specs: Microsoft Windows Vista Home Premium, 1.601 GHz / 3069 MB
I am currently making a workbook that will be used for tracking HR numbers for a fairly large company. I already have a workbook that contains all the data i require. This workbook has multiple sheets that track all the HR numbers for each month of the year, aswell as quarterly results and year to date results. This sheet is in a table format that and my HR manager would like it in a better looking format to read the numbers. On the 'pretty' workbook i would like to setup a macro so that by entering the sheet number i want to reference to in a cell at the top of the page it automatically updates all the numbers with those from the appropriate sheet in the other workbook. I have succeeded in doing this already by using nested if statements, but i have 18 statements which makes for a very long formula. It is too time consuming to make the sheet this way and i am sure there is a more efficient way to do this. I hope my explanation is clear enough. Any help with this would be very much appreciated.
I am fairly good with excel but have never really used visual basic before. The workbooks have been created in excel 2007.

See More: Converting nested if to a macro??

Report •

March 11, 2009 at 09:53:21
It might help if we knew what you were doing with the Nested IF's.

That said, you can refer to a range in another workbook a number of ways. With a "2" is cell C1, each off these lines will put the same formula in A1:

Sub PullFromOtherWorkBook()
    Range("A1").FormulaR1C1 = "=[Book1.xls]Sheet2!R1C2"
'Using Value From Cell - Two Step Method
    ShtNum = Range("C1")
    Range("A1").FormulaR1C1 = "=[Book1.xls]Sheet" & ShtNum & "!R1C2"
'Using Value From Cell - One Step Method
    Range("A1").FormulaR1C1 = "=[Book1.xls]Sheet" & Range("C1") & "!R1C2"
End Sub

Report •

March 11, 2009 at 16:52:19
Sorry, I will clarify a bit better.
What i am currently using nested if's for is to select the sheet on the other workbook that i wish to reference to by entering the sheet name in a cell at the top of the page.
Here is a very shortened version of the formula I am using:
=IF(L1=1,[employee p+l.xlsl]Period1$C$3,(IF(L1=2,[employee p+l.xlsl]Period2$C$3,(IF(L1=3,[employee p+l.xlsl]Period3$C$3,0)))))
What i would like to do is get rid of this formula and have a macro so that when i enter my sheet name in L1 and press a button the macro makes all the formulas refer to that sheet. This would be much easier than filling and adjusting the above formula a couple of hundred times.

Report •

March 11, 2009 at 19:55:34
Have you considered using the INDIRECT function instead of a macro?

Here's one formula with a hard-coded sheet name and the "same" formula that can have the sheet name portion modified by entering the sheet name in C4.


=INDIRECT("[Book1.xls]" & C4 & "!$A$1")

i.e. Type Sheet1 in C4 and both formulae will return the same result.

Does that help?

Report •

Related Solutions

March 12, 2009 at 08:32:30
It's worth bearing in mind though that INDIRECT has to have the other workbook open to read any data, or it returns a #VALUE# error (IIRC).

Report •

March 12, 2009 at 09:58:37
Actually I believe it's a #REF error.

Report •

Ask Question