Search:

Type: Posts; User: Bravo2003; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.02 seconds.

  1. Replies
    3
    Views
    60

    Re: Last date search in range

    That gives a #NUM error.
    Doesn't matter about year, as i will set the range criteria to just cover each years dates range - e.g. C2:C24 then C5:C55, etc..
  2. Replies
    3
    Views
    60

    Last date search in range

    Not sure if this is possible.. but then again anything is possible in Excel I find from you lot...

    I have a column of dates in Column C - 28/12/2018, 26/01/2019, 27/02/2019, 29/03/2019, etc...
    ...
  3. Replies
    1
    Views
    119

    Re: Weekly look up match

    Managed to fix the links to samples:
    https://i1164.photobucket.com/albums/q569/TwennyP/Sheet2_zpsbwlobbzk.jpg



    https://i1164.photobucket.com/albums/q569/TwennyP/Sheet1_zpsqhpzejhm.jpg
  4. Replies
    1
    Views
    119

    Weekly look up match

    Hi All,
    Struggling to add a sample on this one, but hopefully my description will help with what I need:

    Sheet1 (results required)

    Sheet2 (Data base information - the look up data)

    Column...
  5. Replies
    3
    Views
    96

    Re: PostCode Extraction data

    Excellent, Thanks
    Knew it would be a simple one.
  6. Replies
    3
    Views
    96

    PostCode Extraction data

    Hi All,
    This should be a simple one?

    I'm currently using - =IF(D3="EXC","",LEFT(D3,2))

    Column D being a list of postcodes.
    works fine for most Postcodes, but the single letter ones - i.e...
  7. Replies
    2
    Views
    125

    Re: Wild Card in SUMPRODUCT formula search

    Excellent, Thanks
  8. Replies
    2
    Views
    125

    Wild Card in SUMPRODUCT formula search

    =SUMPRODUCT(--('2019 Sales'!$CR$1:$CR$1055<>"IND")*--('2019 Sales'!$D$1:$D$1055=$A$2)*--('2019 Sales'!$B$16:$BY$16=BX$1)*--('2019 Sales'!$B$17:$BY$17=$A3),'2019 Sales'!$B$1:$BY$1055)

    Using the...
  9. Replies
    5
    Views
    169

    Re: SUM/Lookup database

    Yes, i think it's the imported data January 2018 is set as text. unfortunately I can't amend the format for this as it's imported and updated at least once a day and I don't want to have to keep...
  10. Replies
    5
    Views
    169

    Re: SUM/Lookup database

    Thanks, Ive got this to work using this adjusted formula

    =SUMPRODUCT(--('2018 Sales'!$B1:$B600="Overall Result")*--('2018 Sales'!$B$16:$BY$16="January 2018")*--('2018...
  11. Replies
    5
    Views
    169

    SUM/Lookup database

    This is probably very simple, but I can't think of the right solution at present.

    Spreadsheethttps://i1164.photobucket.com/albums/q569/TwennyP/Mr%20Excel_zpsxultlw3p.jpg


    On a seperate Sheet,...
  12. Re: Quickest or Best way to change Column reference

    8 years x 12 months x 4 different choices

    I know may be just as quick to use Replace!
  13. Re: Quickest or Best way to change Column reference

    Not sure it will work from headers as they are differentformats
    i.e. master data shows as January 2014 / but the resultsheader is Jan-14 (cell data = 01/01/2014)
    Due to layout and formatting it may...
  14. Quickest or Best way to change Column reference

    =SUMIF('2014 Sales'!$CR$1:INDEX('2014Sales'!$CR:$CR,A15_2014_Sales),$AR594&"*",'2014Sales'!$F$1:INDEX('2014 Sales'!$F:$F,A15_2014_Sales))

    In the above formula the Reference to Column F is a...
  15. Re: Size of Excel file getting unmanagable...

    Thanks Rory, the INDEX option has worked well so far,
    I’ve managed to re-write about half my volatile INDIRECT formulasand I’ve had less instability from the file now… Just a lot more to go toensure...
  16. Re: Size of Excel file getting unmanagable...

    =SUMIFS(INDIRECT("'2018Sales'!$BZ$1:$BZ"&A15_2018_Sales),INDIRECT("'2018 Sales'!$B$1:$B"&A15_2018_Sales),$C3,INDIRECT("'2018Sales'!$D$1:$D"&A15_2018_Sales),"Result")

    The data in column BZ (example...
  17. Re: Size of Excel file getting unmanagable...

    Same problems in Safe Mode - slow response time, notresponding, crashes Excel.

    I know I have volatile (INDIRECT) formulas in use, but thedata is so variable each update it avoids having to...
  18. Re: Size of Excel file getting unmanagable...

    Yeah, my original master file is at 5.5MB and is a nightmare, if I have other files open it just freezes and crashes. (even without other files open!!)

    It's way to big to email out and if I change...
  19. Re: Size of Excel file getting unmanagable...

    I have a Sheet Tab for each Year 2013-2019 that has sales numbers and figures. average 600 rows
    columns headed for each month (Jan - Dec) with 6 columns per month for Cost/Value/Qty/etc.. then a...
  20. Size of Excel file getting unmanagable...

    I've just tried to rebuild from scratch my Master File that I use for sales reporting..
    The original was getting over 6MB and is so slow to process even the simplest of changes, and can crash my...
  21. Replies
    11
    Views
    427

    Re: SUM OFFSET help?

    Peter, Thank you so much for helping.

    afraid I made another mistake... the formula in B4 needs copying across, not down..
    so B4 / C4 / D4 etc..

    what adjustment to your formula should I make?
  22. Replies
    11
    Views
    427

    Re: SUM OFFSET help?

    I have worked out I would probably only use probably 365-500 rows max - so that should help to start with as N1:N365
  23. Replies
    11
    Views
    427

    Re: SUM OFFSET help?

    That's an option.. I might now the max row count the sheet will grow to soon, so could adjust it then

    On a similar need, can I use this with COUNTIF?
    so SheetX B4 need to =COUNTIF(Sheet1!N:N,5) ...
  24. Replies
    11
    Views
    427

    Re: SUM OFFSET help?

    Thanks, that works, but as Sheet1 will be an expanding file, it might not be the best solution needing to change the range references every time ?

    But you are right bout 10 columns not 9... this...
  25. Replies
    11
    Views
    427

    Re: SUM OFFSET help?

    Right, I've managed to work out how to sum the different N column requirements

    =SUM(OFFSET('SHEET2'!$N$8,0+I$1,0,6,1)) - row1 from I is 0+7 auto populated along

    now I nee to get the column...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4