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

  • Thread starter Thread starter Birilac Bumbleroot of Fair Downs
  • Start date Start date
B

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
 
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.
 
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
 
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 said:
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

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
Click to expand...
Click to expand...
 
Back
Top