Calling experienced excel users

Things Help
281

You canā€™t call H5 from another sheet just using H5. Your sheet already has a H5.

You need to call the sheet you want and then the H5.

=SheetName!H5

https://support.microsoft.com/en-us/office/create-or-change-a-cell-reference-c7b8b95d-c594-4488-947e-c835903cebaa#:~:text=Create%20a%20cell%20reference%20to%20another%20worksheet&text=Click%20the%20cell%20in%20which%20you%20want%20to%20enter%20the%20formula.,-In%20the%20formula&text=%2C%20type%20%3D%20(equal%20sign)%20and%20the%20formula%20you%20want,of%20cells%20to%20be%20referenced.

2 Likes
282

@Horsebox, the link there probably explains it, but with Excel you can usually click on the cell on the other sheet you want to reference and it will auto-populate the sheet & cell-reference for you, rather than having to type it out.

283

Yeah I was clicking on H5 on the other sheet. That link isnā€™t the answer for me. I want to strike through and give a red fill to cells C10 to C20 on sheet 2 if the cell H5 on sheet 1 contains the word No.

284

Whatā€™s the formula looking like? If you have an IF statement in there remove it, literally try = Sheet1!$H$5="Noā€œ

1 Like
285

That worked. Thank you very much, kind sir.

1 Like
286

Donā€™t you have some minion to do things like that for you?

1 Like
287

Iā€™m creating something amazing here. I want to be at the coal face.

2 Likes
288

I have a csv report.
Itā€™s a list of folders along with their sub folders and files.
Iā€™m looking to filter it so I only have the top level folders. Any ideas.

Current Format example

All Files/John/
All Files/John/January
All Files/John/March
All Files/John/March/report34034.xls
All Files/Mary/
All Files/Mary/Accounts
All Files/Mary/Reports
etc etc etc

Iā€™d like to be able to filter that to just
All Files/John/
All Files/Mary/

289

Add a column and do a TEXT("current column, x number of characters if John and Mary are the same number of characters and filter by that new column. If not use a wildcard for All Files/John* and All Files/Mary* and do the same.

Or apply a text filter using ā€œContainsā€

290

Thereā€™s about 200 different top level folders of varying length file names and formats

291

There is probably a neater way but you can do a formula to extract all characters before the nth specific character. The specific character in this case the second forward slash. Probably makes more sense in VBA

292

Ok, thereā€™s a marker I can use, because each top level folder will be followed by /
so hereā€™s my pseudo code:

if no character after 4th / delete row

Somebody do stuff with curly brackets, colons and semi colons to make the above work

293

I think I have it, stand by

294

Think you can also delete the characters in the string after the fourth forward slash to get the same output

295

I really should have put the solution somewhere

3 Likes
296

Iā€™d say you need to use CHARINDEX (or whatever the Excel version of that is) to break up the string by the ā€˜/ā€™ s

Edit - FIND seems to be the one.

297

sed -n ā€˜s:^(/[^/]{1,}/[^/]{1,}).*:\1:pā€™

sort -u myfile.csv -o myfile.csv

298

This was it:

=LEFT(A3,FIND("#",SUBSTITUTE(A3,"/","#",4))-1)

That deletes everything after the 4th instance of /
Then I had to run a delete duplicates to tidy it up.

1 Like
299

Why the fourth instance of / - you wanted only the top 2 levels?

300

That was just an example. Exact requirements differed across reports.