Re: quarterly shift preference spreadsheet
Sounds like a big job to me
Re: quarterly shift preference spreadsheet
Sounds like a big job to me
Re: Macro to transfer cell values from a column to last row of a table
Hi,
Just trying to guess what you want to do, You fill in data from Sheet 1 B2 to B5, then automatically copy paste to next available empty row on sheet 2.
EOR1 = sheets("sheet2").cells(rows.count,1).end(xlup).row
sheets("sheet1").range("B2:B5").copy sheets("sheet2").range("A" & EOR1 + 1)
and the reason your macro is jumping to sheet 5. due to this code below
Cheers
Lex
Re: Sum Function based on changing value
Hi,
you can use this code
Note: C3:C9 = Sum Range
B3:B9 = Date range 1
">="&EOMONTH("1/1/2015",-1)+1 = Criteria one - in this case. it means greater than !st of January
B3:B9 = Date range 2
"<="&EOMONTH("1/6/2015",0) = Criteria tow, in this case, it means less than equal to 30/06/2015
EOMONTH function will give you the last of particular month.
Cheers
Lex
Re: Array Formula is not working
hi Azumi,
Thanks so much, it works perfectly. Thanks for the tips!
Regards,
Albert
Hi,
I am currently trying to use Array function to lookup my piano chord progression, However I am unable to return the value that i want, it keeps returning FALSE, instead of the note.
and secondly, when there are no value, Excel will return 0, how do i remove 0 ?
Any help would be appreciated.
Cheers
Albert
Hi,
I have trouble using Vlookup where I have to write Vlookup formula manually.
Below is my current Vlookup formula and i need a formula where i dont have to change the word Jul/Aug/Sep/Oct and so on manually.
=IFERROR(VLOOKUP(A5,'[Chemicals Budget 15-16 Monthly Data.xlsx]ATBJul'!$C:$E,3,FALSE),0)
=IFERROR(VLOOKUP(A5,'[Chemicals Budget 15-16 Monthly Data.xlsx]ATBAug'!$C:$J,3,FALSE),0)
=IFERROR(VLOOKUP(A5,'[Chemicals Budget 15-16 Monthly Data.xlsx]ATBSep'!$C:$J,3,FALSE),0)
=IFERROR(VLOOKUP(A5,'[Chemicals Budget 15-16 Monthly Data.xlsx]ATBOct'!$C:$J,3,FALSE),0)
and so on
Cell H4 contains 1-Jul-14
Cell I4 contains 1-Aug-14
Cell J4 contains 1-Sep-14
and so on
I have tried using combination of
=IFERROR(VLOOKUP(A5,'[Chemicals Budget 15-16 Monthly Data.xlsx]ATB&text(F4,"mmm")&'!$C:$E,3,FALSE),0)
is not working.
Any help would be appreciated.
Thanks
Albert
Re: Adding IF statement to a Lookup
Hi,
Can it be replace by Vlookup instead of Lookup?
Havent tested the code but this should work.
=IF(C2="",VLOOKUP(A2,$F$1:$G$4,2,FALSE),"Revenue")
or
=IF(C2<>"","Revenue",VLOOKUP(A2,$F$1:$G$4,2,FALSE))
or
=IF(C2>0,"Revenue",VLOOKUP(A2,$F$1:$G$4,2,FALSE))
Cheers
Lex
Re: Array formula to auto populate Name and sector
Hi,
I would love to use Pivot table, must the users aren't excel proficient, asking them to refresh pivot table can be a nightmare.
My current version is attached. I have to resort to column helper, in which i added in col W in sheet summary.
And Based on Column W, i can populate the Summary by managers Listing.
Cheers
Lex
Hi,
I am trying to create array function to auto populate Managers name and Sector from Summary tab to Summary by Managers.
Can this be done through Array formula? I want to avoid using VBA as this will be use by lots of people.
The criteria, it does not need to be sorted in any order.
Any help would be appreciated.
See attached sample file.
Regards,
Lex