Dan Wilson

Well-known Member
Joined
Feb 5, 2006
Messages
504
Office Version
  1. 365
Platform
  1. Windows
Good day. I am running Excel 2013 on Windows 10 Home. I have a workbook containing 7 worksheets. One of the worksheets uses LOOKUP to access a number from one of the other worksheets for each entry on the first worksheet. After entering new data in Row 95 of the worksheet, the LOOKUP function no longer works from that point forward. The formula is correct and the data on the other worksheet is there. Is there an issue with LOOKUP?
Apparently I am not allowed to add attachments to my posts. I have Print Screen images of the problem. Any help is appreciated.
Thank you,
Dan Wilson...
 
Last edited:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Good day Fluff. Thank you for responding. The formula in question is =IF(B96="",0,LookupB96,Riders!G$1:EA$2))
Column B on the Rides worksheet contains ride numbers from 1 to 100. The ride number is prefaced with the year ex: 19-1 thru 19-100. Column H on the Rides worksheet contains the formulas used to look at the Riders worksheet. Row 1 on the Riders worksheet contains the ride numbers in cells G1 thru EA1. Row 2 on the Riders worksheet contains the number of riders for each ride above it. All of the formulas in Column H on the Rides worksheet are identical except for the reference to the B column. All of the formulas from H2 thru H95 work. The formulas in cells H96 thru H100 do not work. There are no rides entered yet beyond number 100.
Thank you,
Dan Wilson
 
Upvote 0
That formula works for me down to row 108 so there shouldn't be a problem. Double check that the ride numbers on both sheets are the same.
If you put this in an empty cell what does it say?
=B96=Riders!CW1
assuming that B96 should be picking up the value from col CW
 
Upvote 0
Good day again Fluff. I copied the workbook to a test file for experimentation. I re-entered the ride numbers on both the Rides and the Riders worksheet with no change. I entered

=Riders!CW2

into cell H96 on the Rides worksheet and it worked. I verified that the formats of both worksheets were correct. I opened up the workbook for 2018 that has 109 rides entered and everything worked correctly with the same formulas. I noticed that the Page Orientation on the Rides worksheet was set to Landscape. I changed it to Portrait, but no difference. In Landscape mode, Row 96 is the last row on the first page. The formula that you suggested does work, but there are several Macros in the workbook that sort the two worksheets by different Columns to get totals and averages. Using a direct access will not work. I don't understand why the Lookup function works on one workbook and not on another.
Thank you for sticking with this one,
Dan Wilson...
 
Upvote 0
You need to put the formula
=B96=Riders!CW1
into a blank cell to check that the value in B96 is the same as the Value in Riders!CW1.
This assumes that the value in B96 is meant to be the same as the value in CW1, if not change CW1 to the cell that should match B96
 
Upvote 0
OK, I learned something today. I didn't know you could do that. When I first looked at the formula, I thought you had typed it wrong. I put the formula in an unused cell on the Rides worksheet and tried it on multiple ride numbers on the Riders worksheet. They all came back as True, above and below B96. To make sure the formula was working, I tried making the formula point to a different ride number than the reference and it came back False. I appreciate the extra effort on this.
Thank you,
Dan Wilson...
 
Upvote 0
Ok, If that formula comes back as true, then the values match, so not sure why it's not working.
Does this work?
=IF(B96="",0,HLOOKUP(B96,G$1:EA$2,2,FALSE))
 
Upvote 0
EXCELLENT! That works! I modified the formulas in the test file and all 100 rides worked. I think I understand the difference between LOOKUP and HLOOKUP, but it really doesn't explain why LOOKUP is not working beyond ride number 95. It doesn't matter at this point as it works. Row I on the Rides worksheet also uses the same formulas to access the number of bikes on a ride. The HLOOKUP works there as well. LOOKUP did not work on Row I beyond ride number 95. The differences between the workbook for 2018 and 2019 was an increase in the total number of rides, riders, and bikes. Why LOOKUP works beyond ride number 95 in the 2018 workbook is still a question, but again, it doesn't matter. 2018 is working and it's closed. I truly appreciate your patience and extra effort on this one. I learned a couple things as I always do from this forum.
Thank you,
Dan Wilson...
 
Upvote 0
Glad it's sorted & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top