Posts by DNAgirl

Re: limit # of fields in text import

I'm just using the import wizard with the delimited option. There are no quotes around the 3rd column, and all field lengths are variable. So the 3rd column gets chopped on each of its spaces.

Hi,

I have a space delimited text file with 3 columns. The problem is that the 3rd column is a string which may or may not have spaces in it.

How do I import the text file so that I end up with 3 columns in Excel and still have the entire 3rd string from each line?

Thanks,
Jen

Hi All,

Does anyone know a way of conditionally and dynamically inserting a comment?

I have applied the following conditional formatting to a set of data:

=IF(VLOOKUP(J2,$D$1:$F$3073,2,0)+VLOOKUP(J2,$D$1:$F$3073,3,0)=2,TRUE,FALSE)

If the conditional formatting evaluates to TRUE, I would like the cell values found by the 2 vlookup's to display as a comment. If the formatting returns FALSE, there should be no comment.

Any thoughts?

Thanks,
Jen

Thanks Derk,

That fixed it right up!

Cheers,
Jen

Hi,

I currently have a formula

=SUMIF(OFFSET(Database,0,6),A2,OFFSET(Database,0,14))

that uses offset to get my arrays. I was hoping to do something similar for my current problem. My idea is:
=sumproduct(offset(Database,0,6)=a2,offset(database,0,25)="test", ...

I'm having 2 problems with the above approach. First,

=SUMPRODUCT(OFFSET(Database,0,6)=A2,OFFSET(Database,0,35)="test")

is always = to 0. If I substitute a * for a , then the formula produces a VALUE error. Secondly, I'm not sure what to put in the ... so that the division that I want will happen.

I looked at the example file and it is a very clear description of how to do SUMPRODUCT but I don't seem to be able to generalize. Sorry.

-Jen

Hi All,

I have a dynamic range on Sheet1 called database. I want to put a formula on Sheet2 that does the following:

lookup all the rows in database where database column 7 matches the value of Sheet2!a2,

find the subset of these rows where column 26 = "test"

(sum column 12 / sum column for this subset

I hope this is clear. Thanks for your help!

Cheers,
Jen

Thanks Tom. That helped a lot!

Cheers,

Jen

Check out the below link.

Cheers,

Jen

http://support.microsoft.com/d…2/1/29.asp&NoWebContent=1

Hi All,

I keep calculation on manual for a large workbook. In this workbook I have a dynamic range called dailydata:

=daily!$E$1:$G$1,OFFSET(daily!$E$1,MATCH(TODAY()-29,daily!F:F),0,MATCH(TODAY(),daily!F:F)-MATCH(TODAY()-29,daily!F:F),3)

I use this range to populate a chart. Mostly this works.

Each morning when I update data, I recalculate the workbook and save. Then, for reasons unknown to me, I have to set the chart source =dailydata again because it has simplified itself to the actual range co-ordinates. Sometimes when I do this it throws an error "Reference is not valid." Usually I can get it to work again by re-entering the dynamic range in the "insert->name" dialogue and then saving. Sometimes I have to close and reopen the workbook. This is a lot of hassle for something that was supposed to make life simple.

Is there any way of forcing the chart source data to remain =dailydata?

What is the cause of the invalid reference error when the range doesn't appear to have any problems?

Thanks,

Jen

Thanks Derk!

the visible rows are scattered.

-Jen

ok just figured it out. I need to use
for each r in visRange.rows

I still wish this code could be faster. Any thoughts?

-Jen

Hi All,

I have a range called visRange that is all the visible cells on a sheet. I want to go through this range 1 row at a time and extract data from various cells. The code below will do it but it goes through the range horizontally which means I check the same row many times. I'd either like to go through the range vertically, which would allow me to exit for when the bottom of the 1st column was reached or I would like a way of going through a dynamic range 1 row at a time.

j = 1
For Each r In visRange
    i = r.Row
    If i > 1 Then
        j = j + 1
        newbk.ActiveSheet.Range("a" & j) = mysheet.Range("a" & i)
        newbk.ActiveSheet.Range("b" & j) = mysheet.Range("l" & i) / mysheet.Range("k" & i)
        newbk.ActiveSheet.Range("c" & j) = mysheet.Range("k" & i)
        newbk.ActiveSheet.Range("d" & j) = 0 'dummy data to allow 2nd x-axis
        newbk.ActiveSheet.Range("e" & j) = mysheet.Range("l" & i)
        newbk.ActiveSheet.Range("f" & j) = mysheet.Range("o" & i)
    End If
    If i = eor Then Exit For 'eor is row number of last row in range
Next r

Any thoughts?

Thanks,

Jen

Thanks Guys!

You've put me on the right road. What I ended up with is the following:

=daily!$E$1:$G$1,OFFSET(daily!$E$1,MATCH(TODAY()-29,daily!E:E),0,COUNTA(daily!$E:$E)-MATCH(TODAY()-29,daily!E:E),3)

This gives me the top row (for my series names), then the rows starting at the date closest to 28 days before today up to the most recent entry. I decided against going for complete weeks in case I ever need to edit the formula. It's hard enough to parse as is.

Thanks again!

Jen

Hi All,

I have a dynamic named range called 'dailydata'. It refers to =OFFSET(daily!$E$1,0,0,COUNTA(daily!$G:$G),3) where column E has dates and column F and G have series1 and 2 data points. I use dailydata to generate a chart with the dates in column E as the x-axis. My chart is starting to have too many data points on it.

I would like to change dailydata to be dynamic at both ends. The top of the range should start at the date that is closest to 3 complete weeks plus the current week before the last date. The dates in column E are discontinuous (days with zero data points are left out) so I can't just take the weekday of the final date, add 21 and subtract from the final date.

Any thoughts?

Thanks,

Jen

Thanks Tom
that works a treat!

Cheers,

Jen

Hi Derk,

It's not that the data in the series is out of order, it is that the words in the legend are in a different order than the other 2 charts.

I've been playing some more with chart 3 and I realized that the order of the legend appears to correlate to the chart type. Charts1 and 2 are bar graphs while chart 3 is a stacked bar graph. If I change chart 3 to a regular bar graph, the legend corrects itself. Unfortunately, I need the original chart types.

Any thoughts?

Thanks,

Jen

Hi All,

I have created 3 charts. All 3 are based on datasets that share column and row headings. The 1st 2 have legends in the order A,B,C,D. The 3rd has a legend with the order D,C,B,A. Other than the data, the datasets are organized identically.

How can I make dataset 3 have a legend in the A,B,C,D order?

Thanks,

Jen

Thanks Andy!

Hi All,

I'm having a problem that could just be due to brain blockage but here goes:

There are 2 named ranges, data and cutoff. data has 384 numbers in it. cutoff has 1 number. I want to sum all the numbers in data that are greater than cutoff.

I expect the formula should be the following:

=sumif(data,">cutoff")

Unfortunately, the result of this formula is always zero no matter what value I put in cutoff. The same happens if I put a cell address in for cutoff. However, if I put a static value that is the same as the current value of cutoff in the formula I get a reasonable answer.

=sumif(data,">50") gives 251703 which is correct.

I should also note that I have tried removing all formats from cutoff and I have tried formatting cutoff as a number. Can someone tell me what is going on?

Thanks,

Jen

Hi Derk,

That so elegant! Much easier than how I was thinking.

Thanks!

Jen