G
Guest
I currently have a spreadsheet I use to manually enter in phone bill
information (tab input). The billing information is now available online and
I can down load in in text format. I am trying to copy the text and paste it
into a seperate tab (I call preload) in my worksheet. In turn the input tab
will be linked to the preload tab and the billing information will be
automatically brought into the input sheet saving me buckets of time.
However I have a challenge.....
The phone bill has various categories such as Features (like Call Forward)
and Long Distance, that are listed in column A. Within each category a
customer will have the discription (ex Call Forward or Domestic Long
Distance) in Column A and the corresponding charge in column B. In some
cases their will be a Corporate Discount for the Features and Long Distance
Categories. The Corporate Discount is listed in Column A with the discount
amount in Column B. There is no guarentee that the Corporate Discount will
allways be a set number of rows below the Feature or Long Distance
Categories. There is also no guarentee tha the Feature or Long Distance
categories are a set number of rows down from the first billing row. This is
were I run into trouble.
To get around this I have tried to develop a formula in column C runing from
row 1 to row 99. The pasted bill information will fit inside the 98 rows set
aside for it. Then in cell C100 I will sum the values from cell C1 to C99.
I was thinking that I could create a formula (=if cell Axx = "Corproate
Discount", Bxx,0) that would pick up the corporate discount amount associated
with the Feature categories in Column C and then do the same again for Long
Distance in Column D. Finally this would be linked to the input tab, thus
replacing manual data entry with a cut-n-paste operation. But what I find is
that having aCorporate Discount amount in both categories the amount is
being picked up for both categories and totalled in cell C100. So far I have
been unable to figure out a way to determine the seperate discounts.
I have banged my head for a while on this without success and would like to
see if anyone could possible help out
information (tab input). The billing information is now available online and
I can down load in in text format. I am trying to copy the text and paste it
into a seperate tab (I call preload) in my worksheet. In turn the input tab
will be linked to the preload tab and the billing information will be
automatically brought into the input sheet saving me buckets of time.
However I have a challenge.....
The phone bill has various categories such as Features (like Call Forward)
and Long Distance, that are listed in column A. Within each category a
customer will have the discription (ex Call Forward or Domestic Long
Distance) in Column A and the corresponding charge in column B. In some
cases their will be a Corporate Discount for the Features and Long Distance
Categories. The Corporate Discount is listed in Column A with the discount
amount in Column B. There is no guarentee that the Corporate Discount will
allways be a set number of rows below the Feature or Long Distance
Categories. There is also no guarentee tha the Feature or Long Distance
categories are a set number of rows down from the first billing row. This is
were I run into trouble.
To get around this I have tried to develop a formula in column C runing from
row 1 to row 99. The pasted bill information will fit inside the 98 rows set
aside for it. Then in cell C100 I will sum the values from cell C1 to C99.
I was thinking that I could create a formula (=if cell Axx = "Corproate
Discount", Bxx,0) that would pick up the corporate discount amount associated
with the Feature categories in Column C and then do the same again for Long
Distance in Column D. Finally this would be linked to the input tab, thus
replacing manual data entry with a cut-n-paste operation. But what I find is
that having aCorporate Discount amount in both categories the amount is
being picked up for both categories and totalled in cell C100. So far I have
been unable to figure out a way to determine the seperate discounts.
I have banged my head for a while on this without success and would like to
see if anyone could possible help out