for an ad-free Fetcheveryone experience!

SpreadEveryone: The Fetchland Excel wire

102 watchers

GregP Dec 2018 12:28pm, 24 Dec 2018 109,903 posts	Quote Pin GregP Yep - it worked - brilliant.
GregP Jan 2019 12:56pm, 4 Jan 2019 110,013 posts	Quote Pin GregP There's got to be a tidier way to do this? =IF(INDEX('Risk Register'!$1:$1048576,MATCH('Risk Spotlight'!B$1,'Risk Register'!A:A,FALSE),12)=0,"N/A",(INDEX('Risk Register'!$1:$1048576,MATCH('Risk Spotlight'!B$1,'Risk Register'!A:A,FALSE),12)))
GlennR Jan 2019 12:59pm, 4 Jan 2019 40,099 posts	Quote Pin GlennR Not sure. What is the 12 doing in that formula?
iRicey Jan 2019 1:22pm, 4 Jan 2019 323 posts	Quote Pin iRicey The 12 is the column number in the INDEX formula if my glasses are on right.
GlennR Jan 2019 1:35pm, 4 Jan 2019 40,102 posts	Quote Pin GlennR So Greppers is hard coding index numbers now, is he?
Sam Jelfs Jan 2019 2:04pm, 4 Jan 2019 140 posts	Quote Pin Sam Jelfs Going back to the comment by Um regarding dates in excel. They are stored as numbers (not integers ) as days since 1 Jan 1900 00:00:00, but be a little bit careful if you play with them outside of excel, as what is considered 0 by the system varies by OS. Unix assumes 0 is 1 Jan 1970, on Apple it's 1 Jan 2001, and Microsoft its 1 Jan 1601...
GregP Jan 2019 2:30pm, 4 Jan 2019 110,014 posts	Quote Pin GregP Actual LOL at Glenn. I inherited this from someone further up the food chain.
run free Jan 2019 10:32pm, 4 Jan 2019 3,318 posts	Quote Pin run free Grego - KYFS Separate the formula. Put the MATCH formula in one cell (say D1); the 12 in another (presume you will calc) (say E1), INDEX in another (say F1). Hence =INDEX(RiskRegBlah, D1, E1) Then say in G1 =IF(F1=0, "N/A", F1)
GregP Jan 2019 8:53am, 5 Jan 2019 110,017 posts	Quote Pin GregP Jugs? Yes, helper cells seem to be the sensible way to do it - thanks.
run free Jan 2019 12:16pm, 5 Jan 2019 3,319 posts	Quote Pin run free Archie?