# Posts by DNAgirl

• ## limit # of fields in text import

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.

• ## limit # of fields in text import

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

• ## conditional comment based on vlookup

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:

Code
=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

• ## [Solved] Formulas : multiple conditions for calculation

Thanks Derk,

That fixed it right up!

Cheers,
Jen

• ## [Solved] Formulas : multiple conditions for calculation

Hi,

I currently have a formula

Code
=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,

Code
=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

• ## [Solved] Formulas : multiple conditions for calculation

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

• ## Formulas : dynamic named range for chart

Thanks Tom. That helped a lot!

Cheers,

Jen

• ## Formulas : dynamic named range for chart

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-&gt;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

• ## VBA : for each row in range...next row

Thanks Derk!

the visible rows are scattered.

-Jen

• ## VBA : for each row in range...next row

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

• ## VBA : for each row in range...next row

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.

Any thoughts?

Thanks,

Jen

• ## Formulas : dynamic named range

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

• ## Formulas : dynamic named range

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

• ## [Solved] Charts : legend order

Thanks Tom
that works a treat!

Cheers,

Jen

• ## [Solved] Charts : legend order

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

• ## [Solved] Charts : legend order

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!

• ## [Solved] Formulas : sumif and named ranges

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,"&gt;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,"&gt;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

• ## [Solved] VBA : 2d arrays

Hi Derk,

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

Thanks!

Jen