# Search:

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

Page 1 of 6 1

1. ## Thread: Lock progress chart to a specific cell

by ClaireS
Replies
3
Views
88

### Re: Lock progress chart to a specific cell

Happy to help :cool:
2. ## Thread: Lock progress chart to a specific cell

by ClaireS
Replies
3
Views
88

### Re: Lock progress chart to a specific cell

Select your chart, then go to the Chart Format menu. Click on the "Size and pproperties" icon (squareish in shape) and expand the Properties area. Select "Move but don't size with cells".
3. ## Thread: Formula Request: Sum across different sheet tabs with dynamic range, without INDIRECT

by ClaireS
Replies
3
Views
91

### Re: Formula Request: Sum across different sheet tabs with dynamic range, without INDIRECT

Use Data, Consolidate to create your summary sheet and set headings to top row and left column AND link to the source. Then if you add rows in the source sheets, the cell references on the...
4. ## Thread: Calculating % based on items filled in a column?

by ClaireS
Replies
3
Views
62

### Re: Calculating % based on items filled in a column?

If the dashes are actually 0s, then use =COUNTIF(A2:E2,">0")/5
5. ## Thread: Calculating % based on items filled in a column?

by ClaireS
Replies
3
Views
62

### Re: Calculating % based on items filled in a column?

In F2 (I'm presuming the headings are in F1 and the data actually starts in row 2) put =COUNT(A2:E2)/5. Format the answer as a percentage. Copy down.
6. ## Thread: IF function for returning month name with data that has zeros

by ClaireS
Replies
7
Views
101

### Re: IF function for returning month name with data that has zeros

Good point! :cool:
7. ## Thread: IF function for returning month name with data that has zeros

by ClaireS
Replies
7
Views
101

### Re: IF function for returning month name with data that has zeros

You could set the custom format of your date column to yyyy-mm-dd;; which will display dates as dates and zero values as 0. Then your date column will look the way you want while still being...
8. ## Thread: Conditional Formatting Formula

by ClaireS
Replies
8
Views
223

### Re: Conditional Formatting Formula

Can you supply any further information? Are the cells being calculated by a formula (and if so, what)? What sort of range of values should result? Abd I didn't check whether you want the CF once for...
9. ## Thread: Conditional Formatting Formula

by ClaireS
Replies
8
Views
223

### Re: Conditional Formatting Formula

Welcome to the forum!

Use the built-in "Bottom 10..." rule. This ignores blanks and text entries. Set it to Bottom 1 for the minimum value.
Or use this formula (assuming your range to format is...
10. ## Thread: VLOOKUP Not Showing Result ?

by ClaireS
Replies
4
Views
160

### Re: VLOOKUP Not Showing Result ?

If you know that you have an entry, but it could be text or number, then:
=IFERROR(VLOOKUP("?*",A2:A12,1,0),MAX(A2:A12))

by ClaireS
Replies
3
Views
125

### Re: Target dates

This would flag up jobs that were completed ate or that have not been completed yet, though the completion date is past:

by ClaireS
Replies
3
Views
125

### Re: Target dates

As a very basic answer, try something like this:

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

by ClaireS
Replies
2
Views
131

### Re: change data validation after alloweditranges password was entered

Why do you need the password? You could just unlock all the cells they could normally edit, leaving the special ranges locked, then Protect the sheet. They could then still edit the usual cells,...
14. ## Thread: Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

by ClaireS
Replies
10
Views
348

### 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.
15. ## Thread: Odd Pivot Table Behavior

by ClaireS
Replies
3
Views
194

### Re: Odd Pivot Table Behavior

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

by ClaireS
Replies
4
Views
296

### 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...
17. ## Thread: Odd Pivot Table Behavior

by ClaireS
Replies
3
Views
194

### 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...
18. ## Thread: one pivot table from two different excel table

by ClaireS
Replies
3
Views
143

### 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...
19. ## Thread: Using a Named table in Data Validation

by ClaireS
Replies
12
Views
414

### 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
334

### 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
334

### 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
334

### Re: Counting events

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

by ClaireS
Replies
9
Views
334

### 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
403

### Re: Conditional formatting

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

by ClaireS
Replies
10
Views
472

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

Thanks, glad I was able to help. :)
Results 1 to 25 of 129
Page 1 of 6 1