Excel/Google Sheets Discussion
- Thread starter doc ron
- Start date
I am sure one of the experts here will have the exact formula to use, or a much better one, but I think one option is the ISBLANK formula along with an IF statement to get this to work (i.e. if(isblank(CELL) = true, do this, do this if false). I don't have my laptop with Excel on it here to test unfortunately.
- Joined
- 22 Oct 2014
- Messages
- 8,232
- Likes
- 43,625
- AFL Club
- North Melb.
If the “another column” is column A then the following formula will give you what you want if I interpret your requirement correctly.
=R2/SUMIF(A:A,A2,R:R)
Put this formula in cell P2 and copy it down the whole column.
The SUMIF part will give you the totals you are generating in column Q but in every row. The R2 divided by part generates the percentage for the number in each row.
=R2/SUMIF(A:A,A2,R:R)
Put this formula in cell P2 and copy it down the whole column.
The SUMIF part will give you the totals you are generating in column Q but in every row. The R2 divided by part generates the percentage for the number in each row.
Last edited:
If you have tried all possible methods to repair your Excel file but none of them work, then maybe your Excel file is not corrupted, but you just don’t have permission to open it on your system. If you don’t have permission to open the Excel file, it will display the error "Excel cannot open the file because the file format or file extension is not valid." I have tested this on my Windows 10. You can use the following steps to grant everyone on this computer permission to open the Excel file.
If that doesn't work there are a few other suggestions here.
https://techcommunity.microsoft.com...le-filename-xlsx-because-the-file/m-p/1504346
- Right-click the Excel file that cannot be opened and select Properties.
- After the file’s Properties dialog opens, select the Security tab, and then click the Edit button.
- After the file’s Permissions dialog opens, click the Add button.
- When the Select Users or Groups dialog opens, click the Advanced button. When the next dialog opens, click Find Now so all users and groups will show up in the search results list at the bottom of the dialog. Select the Everyone group from the list, and then click OK. Click OK again.
- When you return to the Permissions dialog, you will see the Everyone group has been added to the group or user list. Select the Everyone group, check all checkboxes under Allow, and then click Apply.
If that doesn't work there are a few other suggestions here.
https://techcommunity.microsoft.com...le-filename-xlsx-because-the-file/m-p/1504346
Last edited:
Thanks, Ken.
I think I must have a weird/old version of XL. Most of the fixes I've googled with click this, open this etc. just aren't things that appear on my screen. A common one is to change the extension by clicking on Export, but Export is "shaded", and can't be selected, until I open the file, and I can't open the file. I'm sure it's me being dumb.
I tried right clicking to get to properties, but when I right click, the only options I get are:
I will follow the links you have the links you have given me, and see if they help.
I think I must have a weird/old version of XL. Most of the fixes I've googled with click this, open this etc. just aren't things that appear on my screen. A common one is to change the extension by clicking on Export, but Export is "shaded", and can't be selected, until I open the file, and I can't open the file. I'm sure it's me being dumb.
I tried right clicking to get to properties, but when I right click, the only options I get are:
I will follow the links you have the links you have given me, and see if they help.
Do you get the option if you right click in File Explorer, rather than in Excel itself?
If that doesn't work, try turning it off and back on again.
If that doesn't work, try turning it off and back on again.
One of the weird things is, the file is sitting there in "recently opened", but when I search in One Drive or File Explorer, I can't find it.
Is it possible it's been deleted somehow?
- Joined
- 22 Oct 2014
- Messages
- 8,232
- Likes
- 43,625
- AFL Club
- North Melb.
The issue will likely be that you are trying to open a file that was created in a newer version of excel than you have. The fact this file has an .xlsx extension means it is newish and if you are using an older version of excel it won’t be compatible. There are converters than can convert it back to an older version or you can email it to me and I can save it as an older version and email it back to you.
One of my attempts to get around this was to email it to my other email address.
When I search for the file, to attach it to the email, it's not there to be found.
It has always been in my One Drive file,but it's just not there.
Another of my failed attempts to get it back, was to rename it. The renamed file appears in the My PC section, but it is only 414 bytes, so that's way too small.
When I search for the file, to attach it to the email, it's not there to be found.
It has always been in my One Drive file,but it's just not there.
Another of my failed attempts to get it back, was to rename it. The renamed file appears in the My PC section, but it is only 414 bytes, so that's way too small.
This file should be about 80KB.
I can't find it anywhere on my PC, just the renamed version, which is less than 1KB, and won't open either.
I'm in deep doodoo if I can't find it, as it is the file I use to keep track of who owes who and how much for the 2 syndicates I manage.
A new version was started in March, so I can rebuild part of it from March to now, with a LOT of work.
The second part I can't rebuild.
I'm really in a jam here. Any suggestion as to where to look, or how I might find this file, would be greatly appreciated!!!!
It was open 16 hours ago, and working fine, but now is nowhere to be found!
I can't find it anywhere on my PC, just the renamed version, which is less than 1KB, and won't open either.
I'm in deep doodoo if I can't find it, as it is the file I use to keep track of who owes who and how much for the 2 syndicates I manage.
A new version was started in March, so I can rebuild part of it from March to now, with a LOT of work.
The second part I can't rebuild.
I'm really in a jam here. Any suggestion as to where to look, or how I might find this file, would be greatly appreciated!!!!
It was open 16 hours ago, and working fine, but now is nowhere to be found!
Have you tried number 3 on the link.
https://www.isumsoft.com/office/excel-cannot-open-the-file-because-extension-is-not-valid.html#way2
https://www.isumsoft.com/office/excel-cannot-open-the-file-because-extension-is-not-valid.html#way2