Hi,

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.

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() 'Hardcoded 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

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.

Thanks

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.

=[Book1.xls]Sheet1!$A$1

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

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

Does that help?

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).

Actually I believe it's a #REF error.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History