LinkBack URL
About LinkBacks
Hello, I can't seem to figure out the syntax of this formula:
=LOOKUP(2,1/INDIRECT("'"&A2&"'!"&B1&":"&B1&"<>"""),INDIRECT("'"&A2&"'!"&B1&":"&B1))
where A2 has the tab name, B1 has the column letter. Can someone please help?
thanks!
Hello, I can't seem to figure out the syntax of this formula:
=LOOKUP(2,1/INDIRECT("'"&A2&"'!"&B1&":"&B1&"<>"""),INDIRECT("'"&A2&"'!"&B1&":"&B1))
where A2 has the tab name, B1 has the column letter. Can someone please help?
thanks!
There would seem to be a missing quote at the end:
=LOOKUP(2,1/INDIRECT("'"&A2&"'!"&B1&":"&B1&"<>"""),INDIRECT("'"&A2&"'!"&B1&":"&B1"))
Which version of Excel do you have?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi AliGW, I have excel 2016. I don't think it is missing a "" at the end (if I add it I won't even be able to enter it), as it is I got #REF error, I think the problematic part is probably the <>"" part. thanks for your help.
OK - please update your forum profile first.
Then see if this works:
=LOOKUP(2,1/INDIRECT("'"&A2&"'!"&B1&":"&B1&"<>""""),INDIRECT("'"&A2&"'!"&B1&":"&B1))
What do you want the formula to do? The syntax looks entirely incorrect for any possible application. So, to fix it I need to know what you WANT it to do. Preferably, attach a small sample sheet (5 rows) with manually calculated answer... no need for a formula.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
I'm trying to directly pull the last value of a column on a different sheet. on Sheet2, the formula without indirect function looks like this:
=LOOKUP(2,1/(Sheet1!D:D<>""),Sheet1!D:D) and it works
where now I have "Sheet1" in cell A2 on Sheet2, "D" in cell B1 on Sheet2
I could have the formula on each sheet, but just want to see how the syntax should be.
thanks!
Last edited by lynnsong986; 07-15-2021 at 01:00 PM.
attach the updated workbook with the working formula beside the wrong formula
=LOOKUP(2,1/(INDIRECT("'"&A2&"'!"&B1&":"&B1)<>""),INDIRECT("'"&A2&"'!"&B1&":"&B1))
It is a bad idea to use a volatile function (INDIRECT) with whole column references. Rvery time anything changes it recalculates all 1,000,000 rows.
Use this instead:
=LOOKUP(2,1/(INDIRECT("'"&A2&"'!"&B1&"1:"&B1&"100")<>""),INDIRECT("'"&A2&"'!"&B1&"1:"&B1&"100"))
which runs from D1 to D100.
thank you sooooooooooo much Glenn, this is awesome!!
You're welcome & thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks