Using If formula or function to open and update a 2nd sheet

[Birilac Bumbleroot of Fair Downs]

Good afternoon
Firstly I am a newbie to excel .
I am trying to get excel to update a specific sheet "IF" the input is lets
say Pam or Tim .
i.e First sheet is my input sheet, 2nd ,3rd and upwards are my customers
(Pam, Tim, Fred etc)

As the orders come in I need to fill in the input sheet under columns
(headings) Name date , amount etc

Is it then possible for a function to check this input and say (do) ... If
name = Pam , then goto Pam's sheet and add same info to Pam's sheet but if
input name was Fred, then go to Fred sheet instead and add input here ?

IF this is possible would the function (formula) add the info to Fred's
sheet as a new row (or next blank row ) so that Fred's sheet reflects all
inputs for Fred from the first sheet (input sheet) over a time period ?

I would really appreciate any help or pointers to help. I have already
purchased 2 books , but they do not help with this .

Again my thanks

[Tom Ogilvy]

Formulas bring information from another location. If the information is
dynamic, then the formula's results are dynamic. So the concept you
describe it not very good for excel.

What you might do is have one master database. You can add information to
this database. Then if you want to see information pertinent to a specific
individual, you can use a filter or perhaps use a pivot table.

--
Regards,
Tom Ogilvy

"Birilac Bumbleroot of Fair Downs" <birilac bumbleroot@fair downs.com> wrote
in message news:[email protected]...
> Good afternoon
> Firstly I am a newbie to excel .
> I am trying to get excel to update a specific sheet "IF" the input is lets
> say Pam or Tim .
> i.e First sheet is my input sheet, 2nd ,3rd and upwards are my customers
> (Pam, Tim, Fred etc)
>
> As the orders come in I need to fill in the input sheet under columns
> (headings) Name date , amount etc
>
> Is it then possible for a function to check this input and say (do) ... If
> name = Pam , then goto Pam's sheet and add same info to Pam's sheet but
if
> input name was Fred, then go to Fred sheet instead and add input here ?
>
> IF this is possible would the function (formula) add the info to Fred's
> sheet as a new row (or next blank row ) so that Fred's sheet reflects all
> inputs for Fred from the first sheet (input sheet) over a time period ?
>
> I would really appreciate any help or pointers to help. I have already
> purchased 2 books , but they do not help with this .
>
> Again my thanks
>
>
>
>

[John A]

Yes you can do it using the following method, but you will need to
have 2 sheets for each person, setting up formulae on the first sheet
for each person:

X = data entry sheet
Y = Pam (formulae) sheet
Z = Pam (values) sheet
MAX = The maximum number of data entries in sheet X

X! row 1 = Column headers "Name","Date", "Amount"
X! rows 2 thru MAX+1 = data entry area

Y!A1 = "Pam"
Y! row 2 = Column headers "Date", "Amount"

Y!A3 contains the following formula (may appear in this message as two
lines:

=IF(ISNA(VLOOKUP($A$1,X!$A2:$C2,2,FALSE))=TRUE,"",VLOOKUP($A$1,X!$A2:$C2,2,FALSE))

Y!B3 contains the following formula (may appear in this message as two
lines):
=IF(ISNA(VLOOKUP($A$1,X!$A2:$C2,3,FALSE))=TRUE,"",VLOOKUP($A$1,X!$A2:$C2,3,FALSE))

Select Y!A3 and Y!B3, and down to row MAX+2, Edit / Fill / Down, to
copy the above formulae to all the rows

Now when you enter data on X!, only Pam data will appear in Y!, but
there will be blank lines throughout Y!

Now to remove the blank lines

Make a macro called Pam to do the following, so you can run it anytime
you want:

Select Y!A1:B[MAX+2], copy
Select Z!A1
Edit / Paste Special / Values
Select Z!A3:B[MAX+2]
Sort by date (or whatever order you want) to remove blank lines.

Format the columns to the date/$ etc formats that you want on all
three sheets and save before pasting data in sheet Z

John Allen

On Sat, 15 Jan 2005 17:05:54 +0200, "Birilac Bumbleroot of Fair Downs"
<birilac bumbleroot@fair downs.com> wrote:

>Good afternoon
>Firstly I am a newbie to excel .
>I am trying to get excel to update a specific sheet "IF" the input is lets
>say Pam or Tim .
>i.e First sheet is my input sheet, 2nd ,3rd and upwards are my customers
>(Pam, Tim, Fred etc)
>
>As the orders come in I need to fill in the input sheet under columns
>(headings) Name date , amount etc
>
>Is it then possible for a function to check this input and say (do) ... If
>name = Pam , then goto Pam's sheet and add same info to Pam's sheet but if
>input name was Fred, then go to Fred sheet instead and add input here ?
>
>IF this is possible would the function (formula) add the info to Fred's
>sheet as a new row (or next blank row ) so that Fred's sheet reflects all
>inputs for Fred from the first sheet (input sheet) over a time period ?
>
>I would really appreciate any help or pointers to help. I have already
>purchased 2 books , but they do not help with this .
>
>Again my thanks
>
>
>

[Tom Ogilvy]

Just for clarification, John is assuming the input sheet is the master
database I spoke of. My assumption was/is that your inut sheet was like a
blank form in which you entered data, then replaced that data with another
entry. Your desire being to archive each entry to sheets for specific
customers. That is why I said that is not possible with formulas. Since
you didn't describe the situation, assumptions are made.

--
Regards,
Tom Ogilvy

"John A" <[email protected]> wrote in message
news:[email protected]...
> Yes you can do it using the following method, but you will need to
> have 2 sheets for each person, setting up formulae on the first sheet
> for each person:
>
> X = data entry sheet
> Y = Pam (formulae) sheet
> Z = Pam (values) sheet
> MAX = The maximum number of data entries in sheet X
>
> X! row 1 = Column headers "Name","Date", "Amount"
> X! rows 2 thru MAX+1 = data entry area
>
> Y!A1 = "Pam"
> Y! row 2 = Column headers "Date", "Amount"
>
> Y!A3 contains the following formula (may appear in this message as two
> lines:
>
>
=IF(ISNA(VLOOKUP($A$1,X!$A2:$C2,2,FALSE))=TRUE,"",VLOOKUP($A$1,X!$A2:$C2,2,F
ALSE))
>
> Y!B3 contains the following formula (may appear in this message as two
> lines):
>
=IF(ISNA(VLOOKUP($A$1,X!$A2:$C2,3,FALSE))=TRUE,"",VLOOKUP($A$1,X!$A2:$C2,3,F
ALSE))
>
> Select Y!A3 and Y!B3, and down to row MAX+2, Edit / Fill / Down, to
> copy the above formulae to all the rows
>
> Now when you enter data on X!, only Pam data will appear in Y!, but
> there will be blank lines throughout Y!
>
> Now to remove the blank lines
>
> Make a macro called Pam to do the following, so you can run it anytime
> you want:
>
> Select Y!A1:B[MAX+2], copy
> Select Z!A1
> Edit / Paste Special / Values
> Select Z!A3:B[MAX+2]
> Sort by date (or whatever order you want) to remove blank lines.
>
> Format the columns to the date/$ etc formats that you want on all
> three sheets and save before pasting data in sheet Z
>
> John Allen
>
> On Sat, 15 Jan 2005 17:05:54 +0200, "Birilac Bumbleroot of Fair Downs"
> <birilac bumbleroot@fair downs.com> wrote:
>
> >Good afternoon
> >Firstly I am a newbie to excel .
> >I am trying to get excel to update a specific sheet "IF" the input is
lets
> >say Pam or Tim .
> >i.e First sheet is my input sheet, 2nd ,3rd and upwards are my
customers
> >(Pam, Tim, Fred etc)
> >
> >As the orders come in I need to fill in the input sheet under columns
> >(headings) Name date , amount etc
> >
> >Is it then possible for a function to check this input and say (do) ...
If
> >name = Pam , then goto Pam's sheet and add same info to Pam's sheet but
if
> >input name was Fred, then go to Fred sheet instead and add input here ?
> >
> >IF this is possible would the function (formula) add the info to Fred's
> >sheet as a new row (or next blank row ) so that Fred's sheet reflects
all
> >inputs for Fred from the first sheet (input sheet) over a time period ?
> >
> >I would really appreciate any help or pointers to help. I have already
> >purchased 2 books , but they do not help with this .
> >
> >Again my thanks
> >
> >
> >
>