Ask Your Question

search across sheets and place value in another [closed]

asked 2018-11-30 09:53:33 +0100

Baffled gravatar image

updated 2020-07-22 14:01:19 +0100

Alex Kemp gravatar image

I have 12 sheets. Across these 12 I want to search through all sheets for a word in one column (car), take the value from another column (€100) and place the values found in a 13th sheet so I can sum them up.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-07-22 14:01:45.025189

2 Answers

Sort by » oldest newest most voted

answered 2018-11-30 11:26:51 +0100

JohnSUN gravatar image

updated 2018-11-30 12:13:12 +0100

Try Consolidate

Very few people know about this feature, because it is not used very often. But this function is designed specifically to solve your problem.

image description

edit flag offensive delete link more


aah, thanks jonSUN Wil certainly give this a go!

Baffled gravatar imageBaffled ( 2018-11-30 11:54:36 +0100 )edit

answered 2018-11-30 10:00:20 +0100

michaelof gravatar image

Not sure about your question. How is the relation between your several columns? How do you know which "car" column" belongs/refers (?) to a "money" column?

edit flag offensive delete link more


I mean, the 12 worksheets are months, in them I have filled expenses. One column lists the items name and another column lists the cost. So in one cell would be what it is, (in this instance car) and in another cell next to it would be the amount (for example €100) I want to find all cells with the word car, take its value from another cell and place it in a different sheet, the 13th where I can sum up all "car" for the whole year. I hope this helps

Baffled gravatar imageBaffled ( 2018-11-30 10:14:56 +0100 )edit

If this is your own seheet, what about trying to make it more simple? What about a single sheet, and filtering the expenses first by year, second by month. And maybe by expense type "car" on demand, or maybe for car in a simple pivot table?

But afaik, maybe I'm wrong, pivoting across many different sheet would hard if not impossible

michaelof gravatar imagemichaelof ( 2018-11-30 10:39:06 +0100 )edit

Thanks, Michaelof, but I can't. I really want on the 13th sheet a list of expenses summed up. This way I can compare car with boat and aeroplane.

Baffled gravatar imageBaffled ( 2018-11-30 11:08:59 +0100 )edit

Question Tools

1 follower


Asked: 2018-11-30 09:53:33 +0100

Seen: 270 times

Last updated: Nov 30 '18