# Search:

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

Page 1 of 5 1

1. ## Thread: Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

by ClaireS
Replies
10
Views
160

### Re: Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

Or just =LEFT(A1,8)&MID(A1,13,2)&"/"&MID(A1,9,3) where A1 is the cell containing your source string.
2. ## Thread: Odd Pivot Table Behavior

by ClaireS
Replies
3
Views
99

### Re: Odd Pivot Table Behavior

You obviously have very well structured data as a rule ;).
Glad I was able to help.
3. ## Thread: Insert New Worksheet

by ClaireS
Replies
4
Views
172

### Re: Insert New Worksheet

Try using the INDIRECT function to look at the sheet before the current one. Find a cell in all sheets (say \$A\$1) that you can reference to give you the sheet number of this sheet, then use something...
4. ## Thread: Odd Pivot Table Behavior

by ClaireS
Replies
3
Views
99

### Re: Odd Pivot Table Behavior

You get Count Of rather than Sum Of if there are ANY blank cells or text values in the column. Excel the treats the whole column as a text field, so you are seeing Field Settings rather than Value...
5. ## Thread: one pivot table from two different excel table

by ClaireS
Replies
3
Views
83

### Re: one pivot table from two different excel table

Consolidate the sales data into a single table using Power Query or Data, Consolidate. Then start a Pivot Table based on this, and Add this data to the Data Model when you are creating the Pivot...
6. ## Thread: Using a Named table in Data Validation

by ClaireS
Replies
12
Views
202

### Re: Using a Named table in Data Validation

Glad you managed to sort it out, Gary, and thanks for letting us know!

by ClaireS
Replies
9
Views
189

### Re: Counting events

Lovin' the signature, #Gerald , and totally agree! Pleased to say I knew what it would do before I ran it.

by ClaireS
Replies
9
Views
189

### Re: Counting events

Wow Gerald, that maths is way out of my league! but sticking with my formula, counting events in a table, try this:
...

by ClaireS
Replies
9
Views
189

### Re: Counting events

I will give it some thought, but it'll have to wait till the morning now!

by ClaireS
Replies
9
Views
189

### Re: Counting events

If you can make a column of the times for your events, you could use the formula shown here:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px...

by ClaireS
Replies
7
Views
244

### Re: Conditional formatting

Put a \$ before the G in your conditional format formula.
12. ## Thread: Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

by ClaireS
Replies
10
Views
267

### Re: Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

Thanks, glad I was able to help. :)
13. ## Thread: Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

by ClaireS
Replies
10
Views
267

### Re: Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

If the formula only returns the #NA error value then =IFERROR(
INDEX(Data[GC],MATCH([Product Key],Data[Product Code],0)),"") returns the value, or a null text string "" if the result is an error....
14. ## Thread: Data Validation List With Offset Formula Allows Free Text Entry

by ClaireS
Replies
3
Views
109

### Re: Data Validation List With Offset Formula Allows Free Text Entry

I am intrigued - as I use this forum, I see many uses of the OFFSET function to produce dynamic lists, as presumably you are doing here. But why not just use Tables? You cannot directly use a Table...
15. ## Thread: Conditional Formatting: everything but "X", "Y", or "Z"

by ClaireS
Replies
11
Views
392

### Re: Conditional Formatting: everything but "X", "Y", or "Z"

Me too, I am in awe of so many of the contributors on this board, and I find it a great learning resource!
16. ## Thread: Using a Named table in Data Validation

by ClaireS
Replies
12
Views
202

### Re: Using a Named table in Data Validation

I always use Tables as sources for Data Validation - the advantages of Tables far outweigh the faff required to create the Data Validation.

The solution is simple - just apply a traditional range...
17. ## Thread: Conditional Formatting: everything but "X", "Y", or "Z"

by ClaireS
Replies
11
Views
392

### Re: Conditional Formatting: everything but "X", "Y", or "Z"

I agree with Shane. I would make a Data Validation List - then you could put all valid values into the list and limit entries to those. Then you don't need to use very complex - or rather general -...
18. ## Thread: IF due date cell is < today = "" unless completed cell contains a date

by ClaireS
Replies
18
Views
618

### Re: IF due date cell is < today = "" unless completed cell contains a date

Good call! I put it in because I was editing from the formula in #13 which used different logic. Not necessary in mine. :rolleyes:

Also, weird how the quote changed the formula, adding a spurious...
19. ## Thread: Conditional Formatting: everything but "X", "Y", or "Z"

by ClaireS
Replies
11
Views
392

### Re: Conditional Formatting: everything but "X", "Y", or "Z"

Yes it will: if you add Data Validation to existing data, you can Circle Invalid Data to highlight records that do not meet the condition.
20. ## Thread: IF due date cell is < today = "" unless completed cell contains a date

by ClaireS
Replies
18
Views
618

### Re: IF due date cell is < today = "" unless completed cell contains a date

You're very welcome - happy to help.
:cool:
21. ## Thread: IF due date cell is < today = "" unless completed cell contains a date

by ClaireS
Replies
18
Views
618

### Re: IF due date cell is < today = "" unless completed cell contains a date

Here you go :)

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col...
22. ## Thread: Conditional Formatting Not Working

by ClaireS
Replies
10
Views
191

### Re: Conditional Formatting Not Working

Happy to help :)
23. ## Thread: Conditional Formatting Not Working

by ClaireS
Replies
10
Views
191

### Re: Conditional Formatting Not Working

That could be the problem. Set up your conditional formatting to look for values less/greater than 0.0525. The percent format multiplies the number by 100 and sticks a % sign on the end - but the...
24. ## Thread: Conditional Formatting Not Working

by ClaireS
Replies
10
Views
191

### Re: Conditional Formatting Not Working

Could you post some sample data and your formula to show the problem, also your Conditional Format settings.
25. ## Thread: Up and Down arrow (and =) indicator when the value is more or less than the previous cell

by ClaireS
Replies
4
Views
124

### Re: Up and Down arrow (and =) indicator when the value is more or less than the previous cell

Not that I can see: you cannot use cell references in icon set conditional formats. I would put it in the next column, base the formatting on a formula that subtracts the number from the one above,...
Results 1 to 25 of 116
Page 1 of 5 1