Cell value based on hyperlink

ChkLane

New Member
Joined
Nov 26, 2016
Messages
6
So, not sure if I titled my question correctly but here is what I am trying to do.

I want cell D3 from sheet 1 to look for cell E3 from another sheet. However, I want it to look for what is in E3 on the sheet that I create a hyperlink to in cell A3 of sheet 1.

So basically, cell D3 in sheet 1 knows it wants to look at cell E3 but does not know which sheet to look at until I enter the hyperlink.

Hopefully I have explained it well enough for someone much smarter than me to figure it out.

Thanks!!
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hi, welcome to the board :)

I think you lost me.

If you want to return data from a sheet that will change, based on your input, or from another cell, you will probably need to use INDIRECT(). Something like...

=INDIRECT("'"&J10&"'!E3")

where J10 would be the cell containing the sheet name you want to use and E3 is the cell you want to return the data from - adjust those as needed
 
Upvote 0
I tried to see if this would work for what I am trying to do and, not saying it wont, I just can't figure it out. Let me try to explain again. (As you can see, I know enough to figure out I don't know enough)

I will have one master sheet that will pull information from multiple (identical) sub sheets. So E3, E4, E5, etc will always look for I25 and F3, F4, F5, etc will always look for K14 on the sub sheet that is connected to the name in B1. B1 will be the name of the employee and it will be connected to that employees respective sub sheet.

I want to be able to add names without having to go through each cell along the row and link it to the sheet.

Hopefully this makes more sense.
 
Upvote 0
The way INDIRECT works, is it takes text - that excel would otherwise not be able to use in a formula - and converts it into something it can use.

So, below is an example from a dummy file...
A​
B​
C​
D​
1​
Sheet nameCell RefRegular formulaINDIRECT formula
2​
sheet 2A1AmtAmt
3​
sheet 3A1SKUSKU
4​
sheet 4A1ann edwards sarahann edwards sarah
5​
sheet 5A1DATADATA
6​
sheet 6A1CompanyCompany
C2='Sheet 2'!A1
C3='Sheet 3'!A1
C4='Sheet 4'!A1
etc
This shows what the answer would be using a regular formula.

D2=INDIRECT("'"&A2&"'!A1")
copied down.
This shows the exact same answers, but using the sheet name as a reference, rather than hard-coding it.

Can you show me what your version of this looks like?
 
Upvote 0
You Sir, are a genius!! Not exactly how I was trying to do it but the end result is the same.

Thank you!!!!!
 
Upvote 0
Im happy to help and that we got you where you needed to be, and thanks for the kind words :)

I was waiting to make sure we had the base formula sorted out before asking...
do you also need to adjust the cell reference as well (like the A1 part)?
 
Upvote 0
I don't think so. I am still working on finalizing the "sub sheets" but so far everything appears to be working exactly like I want. Aside from one thing.....

Lets see if you can work this problem out for me. You seem smart enough to read the formula I have and work out what I am trying to do. How would I change the last part {IF(E15="N/A",0,0)} make the cell remain empty if "N/A" is selected. Since the other 3 are in date format I can not seem to get the "N/A" to appear in anything other than Jan-00.

=IF(E15="Early",EOMONTH(D15,25),IF(E15="Base",EOMONTH(D15,24),IF(E15="late",EOMONTH(D15,23),IF(E15="N/A",0,0))))
 
Upvote 0
Well what do you know.......I worked it out on my own!!


IF(E15="N/A"," "," ")

Sorry for the delay.

1st, where is the N/A coming from - entry/DD or formula
2nd, that formula looks like it will return " " no matter what is in E15?
IF(E15="N/A"," "," ")
that reads...if E15 contains "N/A", return " ", else return " "

Perhaps...
IF(E15="N/A","",E15)
Also, I find it a good habit to not add a space between "", otherwise you don't really have a "blank" cell, you have a cell with a space in it.
 
Upvote 0
No worries.

I wasn't sure if it would work without the space but I see what your saying. I am done working on it for now but I will change it as you suggested.

Thanks again for all your help. I knew there was a way to do what I wanted, just could not figure it out on my own!!
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

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