Hi I have a report I built that has 4 shifts and depending on which shift I have chosen is which EE populate. How do I do a VLookup that looks up reporting depending on shift entered. I currently useHTML Code:
I tried
But thats an epic fail.HTML Code:
Hi Superchew,
This sounds like a Choose() function to me.
The first argument in Choose is the shift number from 1 to 4.
The next 4 arguments are the formulas you want to use for each shift.
Like:
=Choose(shift Number, Formula for Shift1, Formula for Shift2, Formula for Shift3, Formula for shift4)
When you put a 1 to 4 in "Shift Number" the correct formula will be used.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
So I choose shift number from drom down so could I use?HTML Code:
If Excel 'Shift Report'!$c$6' is a number from 1 to 4 then you would use:
=Choose(Shift Report'!$c$6, First Formula, Second Formula, Third Formula, Forth Formula)
You can attach your sample workbook using "Go Advanced" and then click on the Paper Clip Icon.
The shifts are letters ABCD
Heres how I tried to incorporate
Says too many agruments.HTML Code:
if the drop down is a letter then you can use =CODE('shift report'!$C$6)-64 to convert A to 1, B to 2
its case dependent so =CODE(UPPER('shift report'!$C$6))-64 would be safer
also
=CHOOSE('Shift Report'!C6,VLOOKUP($A4,'A-SHIFT REPORTING'!4:35,B$1,FALSE), Formula for Shift2, Formula for Shift3, Formula for shift4)
red ) is missing
Last edited by martindwilson; 12-25-2014 at 12:17 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
OKAY i AM SUPER STUPID, how would I write that formula this is my current Lookup for 1 shift
HTML Code:
Shift Report Zipped..zip
I have attached workbook
Shift Report Zipped..zipHere is the workbook
it would look like this
=IF($A29=0,"",CHOOSE(CODE('shift report'!$C$6)-64,VLOOKUP($A3,'A-SHIFT REPORTING'!29:60,C$1,FALSE),"vlookup 2","vlookup 3","vlookup 4"))
AMAZING!!!!!!!!!!! Thanks for the patience and all the help. Happy Holidays.
SOooo I thought I had it but it turns out when I change the shift the report doestn't change on the shift calculations tab doesnt change.
I have attached if you maybe check. Also getting a circular reference alert when I change shifts.
Shift Report.zip
part of the formula in shift calculations b3 refers to itself
eg
VLOOKUP($A3,'A-SHIFT REPORTING'!$3:$35,B$3,FALSE)
what is cell and on what sheet is B$3 supposed to be?
Last edited by martindwilson; 12-25-2014 at 02:16 PM.
B3 is on all the shift reporting tabs, I was using when charting to goals, but its not needed.
well you need something in place of it it needs to be the column number you want to return from the vlookup range
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks