Question about this table

itsgrady

Board Regular
Joined
Sep 11, 2022
Messages
115
Office Version
  1. 2021
Platform
  1. Windows
  2. MacOS
Not sure if the formulas I used is best why to view the data, but I would like an easy way to update the information as well. The first two rows are used to review two rows of data at a time and it works well, but I have a few questions.

Three questions about the table below.
  1. In the Last column I would like to view only the information after the " / " to crate the link.
  2. When you type the Profile Number in the first cell it returns information from the bottom. I have around 10,000 rows of information. Is there a way to update the information and the table below will update--not sure it is possible. Maybe a fast way to navigate to the information to update it.
  3. Sometimes when I type the Profile Number in the first cell it does not return the information. I have to copy the number and paste it in the cell for it to return the information. Sometimes I have to paste it a few times to get it to update.
Thanks for your help.

Running List.xlsm
AGAHAIAJAKALAMAN
6Profile NumberTypeDescriptionInitialsUnitCodeCompanyLink
753707912thLives with FatherWaterAWINWalmartOpen Profile Profile
864077211thWill always WaterB123WalmartOpen Profile Profile
9
10Profile General Classification
11Profile NumberTypeDescriptionRinse AgentUnitCodeCompanyLink
1253707912thLives with FatherWaterAWINWalmartProfiles/537079.PDF
1364077211thWill always WaterB123WalmartProfiles/640772.PDF
14129045510thNeed a friend WaterC231WalmartProfiles/1290455.PDF
15155140-0005thBee stingsToluene D1W2WalmartProfiles/155140-000.PDF
161782264thMom is a teacherA2NMWalmartProfiles/178226.PDF
1713302213rdWalmaertWaterB124WalmartProfiles/1330221.PDF
18133022112thREACTIVETolueneCNNNWalmartProfiles/1330221.PDF
1914400289thJust moved hereWaterDN23WalmartProfiles/1440028.PDF
20127978th5th grade was toughWaterAKJ9WalmartProfiles/12797.PDF
211280210thNo friendsWaterB2PKWalmartProfiles/12802.PDF
2240355811th12th GradeC2O0WalmartProfiles/403558.PDF
23151596910thHappyWaterDGETWalmartProfiles/1515969.PDF
Sheet3
Cell Formulas
RangeFormula
AH7AH7=IFERROR(IF(VLOOKUP($AG$7,School12,2,FALSE)="","No Data",VLOOKUP($AG$7,School12,2,FALSE)),"")
AI7AI7=IFERROR(IF(VLOOKUP($AG$7,School12,3,FALSE)="","No Data",VLOOKUP($AG$7,School12,3,FALSE)),"")
AJ7AJ7=IFERROR(IF(VLOOKUP($AG$7,School12,4,FALSE)="","No Data",VLOOKUP($AG$7,School12,4,FALSE)),"")
AK7AK7=IFERROR(IF(VLOOKUP($AG$7,School12,5,FALSE)="","No Data",VLOOKUP($AG$7,School12,5,FALSE)),"")
AL7AL7=IFERROR(IF(VLOOKUP($AG$7,School12,6,FALSE)="","No Data",VLOOKUP($AG$7,School12,6,FALSE)),"")
AM7AM7=IFERROR(IF(VLOOKUP($AG$7,School12,7,FALSE)="","No Data",VLOOKUP($AG$7,School12,7,FALSE)),"")
AN7AN7=IFERROR(HYPERLINK(VLOOKUP(AG7,School12,8,FALSE),"Open " & LEFT(VLOOKUP(AG7,School12,8,FALSE),7) & " Profile"),"")
AH8AH8=IFERROR(IF(VLOOKUP($AG$8,School12,2,FALSE)="","No Data",VLOOKUP($AG$8,School12,2,FALSE)),"")
AI8AI8=IFERROR(IF(VLOOKUP($AG$8,School12,3,FALSE)="","No Data",VLOOKUP($AG$8,School12,3,FALSE)),"")
AJ8AJ8=IFERROR(IF(VLOOKUP($AG$8,School12,4,FALSE)="","No Data",VLOOKUP($AG$8,School12,4,FALSE)),"")
AK8AK8=IFERROR(IF(VLOOKUP($AG$8,School12,5,FALSE)="","No Data",VLOOKUP($AG$8,School12,5,FALSE)),"")
AL8AL8=IFERROR(IF(VLOOKUP($AG$8,School12,6,FALSE)="","No Data",VLOOKUP($AG$8,School12,6,FALSE)),"")
AM8AM8=IFERROR(IF(VLOOKUP($AG$8,School12,7,FALSE)="","No Data",VLOOKUP($AG$8,School12,7,FALSE)),"")
AN8AN8=IFERROR(HYPERLINK(VLOOKUP(AG8,ProfileTable,8,FALSE),"Open " & LEFT(VLOOKUP(AG8,ProfileTable,8,FALSE),7) & " Profile"),"")
AN12:AN23AN12="Profiles/"&AG12&".PDF"
Named Ranges
NameRefers ToCells
School12=Sheet3!$AG$12:$AN$23AN7, AH7:AM8, AN12
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Not sure if the formulas I used is best why to view the data, but I would like an easy way to update the information as well. The first two rows are used to review two rows of data at a time and it works well, but I have a few questions.

Three questions about the table below.
  1. In the Last column I would like to view only the information after the " / " to crate the link.
  2. When you type the Profile Number in the first cell it returns information from the bottom. I have around 10,000 rows of information. Is there a way to update the information and the table below will update--not sure it is possible. Maybe a fast way to navigate to the information to update it.
  3. Sometimes when I type the Profile Number in the first cell it does not return the information. I have to copy the number and paste it in the cell for it to return the information. Sometimes I have to paste it a few times to get it to update.
Thanks for your help.

Running List.xlsm
AGAHAIAJAKALAMAN
6Profile NumberTypeDescriptionInitialsUnitCodeCompanyLink
753707912thLives with FatherWaterAWINWalmartOpen Profile Profile
864077211thWill always WaterB123WalmartOpen Profile Profile
9
10Profile General Classification
11Profile NumberTypeDescriptionRinse AgentUnitCodeCompanyLink
1253707912thLives with FatherWaterAWINWalmartProfiles/537079.PDF
1364077211thWill always WaterB123WalmartProfiles/640772.PDF
14129045510thNeed a friend WaterC231WalmartProfiles/1290455.PDF
15155140-0005thBee stingsToluene D1W2WalmartProfiles/155140-000.PDF
161782264thMom is a teacherA2NMWalmartProfiles/178226.PDF
1713302213rdWalmaertWaterB124WalmartProfiles/1330221.PDF
18133022112thREACTIVETolueneCNNNWalmartProfiles/1330221.PDF
1914400289thJust moved hereWaterDN23WalmartProfiles/1440028.PDF
20127978th5th grade was toughWaterAKJ9WalmartProfiles/12797.PDF
211280210thNo friendsWaterB2PKWalmartProfiles/12802.PDF
2240355811th12th GradeC2O0WalmartProfiles/403558.PDF
23151596910thHappyWaterDGETWalmartProfiles/1515969.PDF
Sheet3
Cell Formulas
RangeFormula
AH7AH7=IFERROR(IF(VLOOKUP($AG$7,School12,2,FALSE)="","No Data",VLOOKUP($AG$7,School12,2,FALSE)),"")
AI7AI7=IFERROR(IF(VLOOKUP($AG$7,School12,3,FALSE)="","No Data",VLOOKUP($AG$7,School12,3,FALSE)),"")
AJ7AJ7=IFERROR(IF(VLOOKUP($AG$7,School12,4,FALSE)="","No Data",VLOOKUP($AG$7,School12,4,FALSE)),"")
AK7AK7=IFERROR(IF(VLOOKUP($AG$7,School12,5,FALSE)="","No Data",VLOOKUP($AG$7,School12,5,FALSE)),"")
AL7AL7=IFERROR(IF(VLOOKUP($AG$7,School12,6,FALSE)="","No Data",VLOOKUP($AG$7,School12,6,FALSE)),"")
AM7AM7=IFERROR(IF(VLOOKUP($AG$7,School12,7,FALSE)="","No Data",VLOOKUP($AG$7,School12,7,FALSE)),"")
AN7AN7=IFERROR(HYPERLINK(VLOOKUP(AG7,School12,8,FALSE),"Open " & LEFT(VLOOKUP(AG7,School12,8,FALSE),7) & " Profile"),"")
AH8AH8=IFERROR(IF(VLOOKUP($AG$8,School12,2,FALSE)="","No Data",VLOOKUP($AG$8,School12,2,FALSE)),"")
AI8AI8=IFERROR(IF(VLOOKUP($AG$8,School12,3,FALSE)="","No Data",VLOOKUP($AG$8,School12,3,FALSE)),"")
AJ8AJ8=IFERROR(IF(VLOOKUP($AG$8,School12,4,FALSE)="","No Data",VLOOKUP($AG$8,School12,4,FALSE)),"")
AK8AK8=IFERROR(IF(VLOOKUP($AG$8,School12,5,FALSE)="","No Data",VLOOKUP($AG$8,School12,5,FALSE)),"")
AL8AL8=IFERROR(IF(VLOOKUP($AG$8,School12,6,FALSE)="","No Data",VLOOKUP($AG$8,School12,6,FALSE)),"")
AM8AM8=IFERROR(IF(VLOOKUP($AG$8,School12,7,FALSE)="","No Data",VLOOKUP($AG$8,School12,7,FALSE)),"")
AN8AN8=IFERROR(HYPERLINK(VLOOKUP(AG8,ProfileTable,8,FALSE),"Open " & LEFT(VLOOKUP(AG8,ProfileTable,8,FALSE),7) & " Profile"),"")
AN12:AN23AN12="Profiles/"&AG12&".PDF"
Named Ranges
NameRefers ToCells
School12=Sheet3!$AG$12:$AN$23AN7, AH7:AM8, AN12
Any reason for not using XLOOKUP instead? I don't remember, but as far as I know VLOOKUP should be returning the first match it encounters, but with XLOOKUP the 6th parameter [search mode] lets you specify 1 - Search First to Last, or -1 - Search Last to First.
I ask because your profile says you're using Excel 2021.
 
Upvote 0
I can use VLOOKUP to do the formula. That may resolve the issue of not showing up at times.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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