Pull data to another sheet when specific words are used

NICOLETTE1 · Sep 29, 2018

Hi, I am trying to set up ledgers for my business and I would like to pull amounts to the respective sheet if I use particular words. For eg. if I record CASH on the SALES LEDGER it pulls the data to the CASH LEDGER and if I record DEBTORS on the

SALES LEDGER it pulls to the DEBTORS LEDGER.

No_Need_For_Macros · Sep 30, 2018

Nicollette1, I'm guessing you have 3 Tabs in your workbook: "Sales", "Cash", "Debt". Below is an example and formulas that will display info based on data you input on the "Sales" Tab.

"Sales" Tab (info you enter):

	Column A	Column B	Column C	Column D	Column E
Row 1	Entry#	Name	Item	Price	Payment
Row 2	1	John	Soda	4.50	Cash
Row 3	2	Jimmy	Chips	3.25	Debt
Row 4	3	Jane	Ice Cream	5.00	Debt
Row 5	4	Jimmy	Candy	2.50	Cash

"Cash" Tab:

Column A, Row 2: =IF(Sales!$E2="Cash",Sales!A2,""). Copy this Down Column A for however many Entry Numbers you have.
Column B, Row 2: =SMALL(A$2:A$5,ROW(A2)-1). Again, copy this down Column B
Column C, Row 2: =IF(ISNUMBER($B2),VLOOKUP($B2,Sales!$A$2:$F$5,COLUMN(A1),FALSE),""). Copy this Across Columns C, D, E, F, and G; then down all the necessary Rows.

**Hide Columns A and B on the "Cash" Tab" so that only the data you want is present.**

"Debt" Tab: Do the same thing as the Cash Tab.

This method works well if you're only ENTERING data on a single sheet (in this case the "Sales" Sheet). The other two sheets have data automatically populated.

deedles · Sep 30, 2018

NICOLETTE1 said:
Hi, I am trying to set up ledgers for my business and I would like to pull amounts to the respective sheet if I use particular words. For eg. if I record CASH on the SALES LEDGER it pulls the data to the CASH LEDGER and if I record DEBTORS on the
SALES LEDGER it pulls to the DEBTORS LEDGER.


<tbody>
</tbody>

The way I would go about this is so:

Using the data from “No_Need_For_Macros” but with the extra column “E” in your Sales Journal.

In Column E, I would enter “cash” or “debtors” as the case may be, beside each entry.

First just do the “Cash” sheet,
For below formula to work, your sheets should be named “Sales” and “Cash”. (“Debtors” comes later)

Now go to your “Cash” Sheet and in A2 put this formula:
=IFERROR(INDEX(Sales!$A$2:$A$5;MATCH(0;INDEX(COUNTIF(CASH!A$1:$A1;Sales!$A$2:$A$5)+(Sales!$E$2:$E$5<>"CASH");0);0));"")

Or if you use a comma as separator then:

=IFERROR(INDEX(Sales!$A$2:$A$5,MATCH(0,INDEX(COUNTIF(CASH!A$1:$A1,Sales!$A$2:$A$5)+(Sales!$E$2:$E$5<>"CASH"),0),0)),"").

Then :
In B2:
=IFERROR(INDEX(Sales!$B$2:$B$5,MATCH(CASH!$A2,Sales!$A$2:$A$5,0)),"")

In C2:
=IFERROR(INDEX(Sales!$C$2:$C$5,MATCH(CASH!$A2,Sales!$A$2:$A$5,0)),"")
In D2:
=IFERROR(INDEX(Sales!$D$2:$D$5,MATCH(CASH!$A2,Sales!$A$2:$A$5,0)),0)

Or if you really just want amounts then:

in B2:
=IFERROR(INDEX(Sales!$D$2:$D$5,MATCH(CASH!$A2,Sales!$A$2:$A$5,0)),0)

Now you can copy the “CASH” sheet and call it “DEBTORS” and replace “CASH” with “DEBTORS” in the formulas.

Pull data to another sheet when specific words are used

NICOLETTE1

New Member

Excel Facts

No_Need_For_Macros

New Member

deedles

New Member

Similar threads

Forum statistics

Share this page

Pull data to another sheet when specific words are used

NICOLETTE1

New Member

Excel Facts

No_Need_For_Macros

New Member

deedles

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock