formulas refer to columns that are deleted

  • Thread starter Thread starter Sharon
  • Start date Start date
S

Sharon

I have a workbook with a worksheet that gets refreshed
from a .prn file. Every 4 to 6 years there will be an
additional 2 columns for a week and then they will be
deleted.

I have formulas on another worksheet that refer to these
columns.

Anyone have any ideas on how to deal with this?
Of course once the column is gone I get an error in my
formula.

The .prn file is created by another program. I tried to
get the folks using that program to add those columns and
leave them there permanently (showing zeros when not used)
but apparently that is not possible. So I'm looking for
other ideas.

Thank you -
Sharon
 
The first thing that comes to mind is that you could start with
something like this:

Do
x = x + 1
Loop Until Cells(1, x).Value = ""

This will run until it comes to a blank column. Then you can run the
code one way or another based on that number

If x = 8 Then
blah blah blah
Else If x = 10
blah blah blah
End If

That is just one example of how you can use the information. Let
me know if you need further help. - Pikus
:D
 
Thank you for your reply.
So what is the loop doing? Is it counting the columns?
If so, that will be helpful for knowing which formula to
use, but I'm sorry - I think I left out a bit of important
information. One of the columns gets added in the middle
and one gets added at the end. I think it is this column
in the middle that is the difficulty.
I have to refer to the column either with the column
letter or a range name I think. In my experiments I am
counting the columns to choose the right formula to use,
but when the column is gone I get an error in the formula.

And another thing - the source table is refreshed from
a .prn which means the data just kind of appears in the
column as it was in the .prn file so I'm having a hard
time getting the formula to adjust like it would if you
did an "Insert, Column".

My latest idea is to create another worksheet for the .prn
to go to if there is another 2 columns.
Thanks,
Sharon
 
What you have can wour without the need for another sheet I'm sure. Yes
it is counting the columns. Dealing with the column in the middle is a
bit tricky, but it shouldn't be too bad. Simply because there are so
many ways of handling this I'd say, if you feel comfortable doing so,
post your code here or email it to me. A discription of what it is
doing would help too. - Pikus:D
 
:eek: I'm sorry. I just realized you were talking about formulas and
not VBA. If you tell me what the formulas are doing I may be able to
help. - Pikus
 
Is it possible that it would work to ALWAYS have those extra columns
present but hidden unless they are used. You wouldn't have to
add/delete them. Just hide/unhide them. That seems like a bit less
trouble. - Pikus
 
Ok - Well there's not really much code. But I'll try to
better describe what I've got.

Sheet GSCPurch
I typed in column headings like these:
Store Department Wk1Cost Wk2Cost Wk3Cost Wk4Cost
Wk1Retail Wk2Retail Wk3Retail Wk4Retail

Those columns get refreshed with the GSCPurch.prn file and
this is the code for that:
Sheets("GSPURCH").Select
Range("A2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

The GSCPurch sheet is the one that will get the extra
columns which are: Wk5Cost (that one goes in the middle)
and Wk5Retail at the end.

Then I have the sheet (Sheet1) with the formulas. Here is
one of them (it is an array):
=SUM((GSPURCH!$E$2:$E$1000=Sheet1!$B8)*(GSPURCH!
$F$2:$F$1000=Sheet1!$M$4)*(GSPURCH!$L$2:$L$1000)) + (H8)

E is Store and F is Department and L is Wk1Retail

There is a formula like that for Wk2Retail and Wk1Cost and
so on. So I will need one for Wk5Retail and Wk5Cost for
when those columns show up.

Do you need more info?
I'll be here another 30 minutes but will be checking back.
Thanks for your suggestions!
Sharon
(e-mail address removed)
 
That's ok.
I didn't describe what that formula does -

It looks at the prn file (GSCPurch) for the store that is
stored in B8, and it looks at the prn file (GSCPurch) for
the department that is stored in M4 and when those two
jive it adds up all the items in col L (Wk1Retail) that it
finds (then it adds an input number in H8).

Hope that helps.
Sharon
 
It seemed the easiest solution would be to have the users
who run the program that creates the .prn file add the two
extra columns and just leave them there all the time.
They tried and it didn't work so they either don't know
how or it is not possible.
Today I asked her even if she can't create them so they
stay all the time, maybe when it happens she can create
them at the end - so there would be two columns appended
and none added in the middle. She's going to try next
week. So meanwhile I thought I'd ask around and see if
someone else had any ideas. I'm kind of a programmer
lightweight so I wouldn't be surprised if there was a
solution I had no clue of.
Thanks for looking at it.
Sharon
 
Do you know how I could import a .prn file and get it's
columns to go into the Excel columns I want? If I could
do that then I could go ahead and create the columns as
you suggest.
Thanks -
 
Back
Top