How to get referenced cell to be recognized by macro

I'm not sure what I'm even using is a macro, but I got it to kind of work. If a cell N1 on the worksheet contains a variable number 1-50 (signifies number of people), it will figure out what cells to unhide (will print like 9 people per page). I got it working correctly, the problem is when the cell that is being referenced (the one with the variable number) pulls the number from another worksheet (e.g. ='DATA SHEET'!I54), it does not update like when I manually type a number into that cell.

Here's the formula I'm using to display the cells I want:

Please Login or Register  to view this content.

Can anyone tell me how to get this to work right?

The name of the sheet that contains the code and has the rows hidden is "IJ1"
The name of the sheet that contains the data is "DATA SHEET"

Cell in N1 on Sheet IJI pulls data from cell 'DATA SHEET'!I54

Reading this, it's a "change event" where my manual input does what I want it to do, but whenever the value of any cell on the worksheet is changed by the result of a formula calculation, it doesn't work.

So, hoping that someone has some kind of work around like select the cell, then hit enter whenever the tab is selected. (No data needs to be input or manipulated on this worksheet.

Thanks

Still doesn't seem to work. The variable number is not something manually input anywhere, it's a the result of a formula. The data sheet takes information from other sheets and compiles it onto that sheet. Then the IJ1 sheet (and IJ2 sheet), pull from that sheet. I haven't messed around with pivot tables much, but there was an auto-update of the pivot cache which would refresh that data when clicking on the tab. I read somewhere about adding a calculate function that may work, but couldn't find any examples. I wonder if having it calculate the the original number +0 would work?

It should be

Please Login or Register  to view this content.

Still not working. It only hides the rows when I manually change the numbers on the IJ1 page and tab out or hit enter.

It should work if you are changing the value in Sheets("DATA SHEET").Range("I54").

Is this cell containing a formula as well?

If so, what data, and in where, are you changing? Is the change manual or with a macro?

The solution lies in putting the code into where the source data is updated/changed.

I54 is a formula too. It's =COUNTIF($A$2:$A$51,I2)
A2:A51 is column that has Text - (A Person's Names). The names (Think Boss name, and the columns to the right are subordinate info) are pulled from 2 other sheets (Up to 25 Names Per Sheet - total of 50 rows). So the sum of the value of each name determines how many pages to show. If the Boss' Name has 45 people under him/her, I'd need like 5 or six pages to show.

I'm pretty sure I could do what I want with a Macro, but I was hoping to to just make it seamless. The only thing the end user should be typing in is the boss name, employee name and a few other details (Start time, Floor #, etc.). Another employee would go to the excel file, click on the IJ1 tab (or whatever I eventually name it) and see only the needed rows that contain data.

Try amending abousetta's code. This should fire the change event as the Boss' names are being entered.

Please Login or Register  to view this content.

Still not having much luck. I'll try to break it down and upload a sample.

Thanks all for trying.

Okay, here's the workbook.

Data is manually entered in sheets "List One" and "List Two."

The data is pulled onto the data sheet and sorted.

IJ1 or IJ2 (I'm really only accounting for 2 bosses even though the Data Sheet sorts up to 3) is then accessed by an employee and prints out the worksheet. Rows corresponding to page numbers should be hidden based on the the numbers that have already been listed. I had a change code on each sheet that would do what I wanted if I manually typed the number into cells N2 on IJ1 and IJ2, but that code has been removed and instead replaced with what was provided on the data sheet.

I'm thinking the code needs to be on Sheets List One, and List Two since that's where the info is manually being typed, just not sure how to modify it. Then, would say Sheet IJ2 need to look at a different cell reference to not be confused with the number on the other sheet?

Now that the sheet looks correct, is there a way to set the print area to correspond what is shown? Like if the number was 15 and it's only displaying 2 pages (Rows 1:92) (& Pages 3-6 are hidden), to only print the 2 pages if someone chose to select print?

Like: ElseIf Rng.Value <= 18 Then
.Rows("3:92").Hidden = False 'Page 2
ActiveSheet.PageSetup.PrintArea = "A1:J92"