Author Topic: Excel help (Read 7454 times)

Belfast Mags · « **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?

OpenC · « **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

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

Anybody?

OpenC · « **Reply #27 on:** Saturday 10 May 2014, 05:16:16 PM »

Edit

Never mind, sorted

Cheers anyway

Beren · « **Reply #28 on:** Saturday 10 May 2014, 07:11:39 PM »

Surprised this thread isn't more frequently used.

OpenC · « **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

OpenC · « **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..?

OpenC · « **Reply #31 on:** Monday 12 September 2016, 11:40:23 AM »

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

That might sort it

Beren · « **Reply #32 on:** Tuesday 13 September 2016, 09:56:58 AM »

Yeah that should so do it

OpenC · « **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

Cunning_Linguist · Quote from: Shak on Friday 17 July 2015, 08:10:13 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...

OpenC · « **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?

Ginola · « **Reply #36 on:** Thursday 21 June 2018, 10:37:44 AM »

Try ">"&today()-14

OpenC · « **Reply #37 on:** Friday 22 June 2018, 08:58:10 AM »

will give it a go in a bit, cheers

OpenC · « **Reply #38 on:** Friday 22 June 2018, 09:01:35 AM »

Appears to work perfectly, cheers man

Sho'Time · « **Reply #39 on:** Thursday 27 September 2018, 09:20:02 AM »

So in the night my laptop shut down after I'd finished doing some Excel work and when I logged back in, the majority of my Excel files and everything else was reopened aside from this document.

Normally I'd go into Excel, select File > Recent then pull the latest draft from Excel and usually this works. However this time I cocked up, went into my document and opened the source document which is a blank template, so I've lost around 4 hours work which I put in out of hours last night.

I'm absolutely gutted, does anyone know a way I can go back and be able to recover from a particular time so I could get this work back? I realise it's my error 1. that I didn't save and 2. I opened the source doc, but I don't think I have it in me to do it again.

Ginola · « **Reply #40 on:** Thursday 27 September 2018, 10:21:59 AM »

Try looking on File > Open > Recover Unsaved Workbooks and see if it's in there

Sho'Time · « **Reply #41 on:** Thursday 27 September 2018, 11:20:20 AM »

No joy, I think I've f***ed it.

Kasper · « **Reply #42 on:** Friday 28 September 2018, 01:36:23 PM »

Right click the actual file in the folder it's saved in. "Restore previous version".

Sho'Time · « **Reply #43 on:** Monday 1 October 2018, 09:43:24 AM »

Cheers Kasper, as it was I have no f***ing idea how it happened but it miraculously rebooted itself later in the day.

Shay's Given Tim Flowers · « **Reply #44 on:** Sunday 4 November 2018, 01:07:30 PM »

I'm trying to do a simple expenses sheet.

Basically I have a column that says if a cell in column C says PS then 65.00 is automatically inputted into the corresponding cell in column G.
That is working fine but then when I go to do a SUM of column G it shows nil value and does not calculate the automatically inputted text?

Dave · « **Reply #45 on:** Sunday 4 November 2018, 02:52:39 PM »

Is it inserting 65 as text?

Shay's Given Tim Flowers · « **Reply #46 on:** Sunday 4 November 2018, 03:57:55 PM »

Yes and I want it as a number

Dave · « **Reply #47 on:** Sunday 4 November 2018, 04:55:27 PM »

Take the 65 out of speech marks.

Shay's Given Tim Flowers · « **Reply #48 on:** Sunday 4 November 2018, 05:42:24 PM »

Now I have nothing in the cell.

The formula is:

=IF(ISNUMBER(SEARCH("PS",C5)),65,"")

if that helps

Dave · « **Reply #49 on:** Sunday 4 November 2018, 06:16:14 PM »

You don't need all that search stuff, just do =if(C5="ps",65,"")