Author Topic: Excel help  (Read 5855 times)

0 Members and 1 Guest are viewing this topic.

Offline Belfast Mags

  • General Member
Re: Excel help
« Reply #25 on: Friday 19 July 2013, 12:20:53 am »
When you open a spreadsheet it logs the date and time that you "modify" it.
Is there anyway to either delete the last modification details or manually change that information?
Quote from: Mike
Am I really coming out of this thread the biggest asshole again?
:snod:

Offline OpenC

  • General Member
  • You might still see them in the desert
Re: Excel help
« Reply #26 on: Saturday 10 May 2014, 04:59:38 pm »
Since this was so well responded to, I thought I'd do one of my own :lol:

Cell Ex and Fx both contain dates (date due and date complete).  I want to do conditional formatting on cell Ex as a basic "you've missed your deadline" so it highlights the cell IF Ex is later than today() AND Fx="" (is empty, you know what I mean).  I can't work it out :blush:  Anybody?

Offline OpenC

  • General Member
  • You might still see them in the desert
Re: Excel help
« Reply #27 on: Saturday 10 May 2014, 05:16:16 pm »

Edit

Never mind, sorted

Cheers anyway :shifty:
« Last Edit: Saturday 10 May 2014, 06:32:08 pm by OpenC »

Offline Beren

  • Administrator
  • Administrator
  • Amazing shoes
Re: Excel help
« Reply #28 on: Saturday 10 May 2014, 07:11:39 pm »
:thup:

Surprised this thread isn't more frequently used.

Offline OpenC

  • General Member
  • You might still see them in the desert
Re: Excel help
« Reply #29 on: Saturday 10 May 2014, 07:16:23 pm »
Aye, I had assumed there would be thousands of them tbh :)

It was =and(Ex<today(),Fx="") in the end.  Hadn't realised that in conditional formula entry, the IF is assumed :)

Offline OpenC

  • General Member
  • You might still see them in the desert
Re: Excel help
« Reply #30 on: Monday 12 September 2016, 11:28:33 am »
Resurrection

I have a feeling I'm going to struggle to explain this, but here we go:

I have inherited an incomplete excel spreadsheet with two pages in it.  One of these pages is for data, the other is for stats.

The data page contains a big old list of straightforward data from which entire rows are routinely chopped and new ones added to the bottom before an alphabetical sort is performed.  So nowt unusual there.

The stats page contains a lot of =AND statements returning true and false on that data, which are then counted up.  Nice and simple.  The problem is that when rows are deleted from the data page, the corresponding rows on the stats page lose their referencing and start to return #REF instead.  Is there any way to keep the counters on the stats page absolute and tell it to just ignore any changes to the structure of the data page?  Basically to say "I want this cell always to perform a check on what's in data!C7 even if row C is deleted and new data goes there".

I'm aware there are other ways to do this counting (not least to move the counters to the data page), but it's not really my sheet and I don't want to overcomplicate things to the extent that anybody else looking at it in the future will be confounded.  Realistically it's not actually a bad way to perform the counting other than Excel trying to be helpful by trying to keep the stats page organised alongside the data.

Anybody..?

Offline OpenC

  • General Member
  • You might still see them in the desert
Re: Excel help
« Reply #31 on: Monday 12 September 2016, 11:40:23 am »

Hang on, they hadn't used $ for absolute referencing

That might sort it :)

Offline Beren

  • Administrator
  • Administrator
  • Amazing shoes
Re: Excel help
« Reply #32 on: Tuesday 13 September 2016, 09:56:58 am »
Yeah that should so do it :lol:

Offline OpenC

  • General Member
  • You might still see them in the desert
Re: Excel help
« Reply #33 on: Tuesday 13 September 2016, 01:28:59 pm »

It didn't.  Just copied the =and statements to the data page and have got the stats page reading the whole column.  Has sorted it and it'll be OK for a good few years I guess

I do think there should be a way to do it, though :)

Offline Cunning_Linguist

  • TFiK
  • General Member
  • AKA King Cunni
Re: Excel help
« Reply #34 on: Monday 26 September 2016, 02:47:03 pm »
I'd like to think of myself as a bit of an excel guru but I have absolutely no fuckin idea what you are going on about...
Peppa Pig is a c***.

This. She's an absolute little s*** and if she were my child/pig/whatever-she-is she'd be grounded for life.

Offline OpenC

  • General Member
  • You might still see them in the desert
Re: Excel help
« Reply #35 on: Wednesday 20 June 2018, 03:23:27 pm »
=COUNTIFS(CURRENT!J1:J10000,"=mackems.gif",CURRENT!G1:G10000,today())

This will return the number of times that two cells in the same row on sheet "current" have Jx containing "mackems.gif" and Gx containing today's date. 

How do I modify the second part to count any date in the last 14 days?  Had assumed just ...G1:G1000,>today()-14) but it doesn't like that.  I'd hoped that I could get the parser onside with judicious use of " " but that hasn't worked either.  Any ideas?
« Last Edit: Wednesday 20 June 2018, 03:34:05 pm by OpenC »

Offline Ginola

  • General Member
  • David... Arbeit!
Re: Excel help
« Reply #36 on: Thursday 21 June 2018, 10:37:44 am »
Try ">"&today()-14
« Last Edit: Thursday 21 June 2018, 11:36:14 am by Ginola »

Offline OpenC

  • General Member
  • You might still see them in the desert
Re: Excel help
« Reply #37 on: Friday 22 June 2018, 08:58:10 am »

:thup: will give it a go in a bit, cheers

Offline OpenC

  • General Member
  • You might still see them in the desert
Re: Excel help
« Reply #38 on: Friday 22 June 2018, 09:01:35 am »

Appears to work perfectly, cheers man :thup: