P
paulz_place
Hi all
First post, here goes. I have googled this to no avail, perhaps you
can help?
I have a problem that I am trying to solve, it's hard to explain so I
have produced a video of the problem and put it on YouTube, or perhaps
you are able to follow my text below without the video?
If you search for 'paulonthetube' on YouTube, you will find my video.
I have four tabs in a spreadsheet.
The first tab is a consolidation tab. More on that one later.
Tabs 2-4 are my quotes (1-3) and include items, codes, quantity, unit
cost, total unit cost, gross price and total gross price. Normally I
would also have profit and profit margin as a percentage but have left
these off to keep it simple. Formulas are used to work out totals,
etc.
The consolidation tab features all three of the quotes on tabs 2-4 and
which are 'equalled' or 'linked' (not sure what the correct term is)
to each of the tabs so that changes to the quote 1 tab for example are
repeated on the consolidation tab. Ditto for quote 2 and quote 3.
Trouble is, if I add another row(s) into one of the quote tabs and
copy down the formulas so that I can add more items and costs, this
isn't replicated on the consolidation tab and I have to manually add
in rows and copy down on this tab also.
Is there a way that this can be completely automated so that any rows
I add automatically add themselves in the consolidation tab?
Also when adding a row(s) into a quote tab to add new equipment, etc,
is there a way of having the formulas to the right copy down or appear
automatically?
The reason I need the consolidation tab is so that I can sort all the
items into order to make procurement easier. If you have seen the
video you will see that I sort by column A to put everything in order,
although if I could get each repeated line of equipment to condense
into one line and the quantity adjust automatically, that would be
useful!
If anyone has any ideas on how they can help I'd be much obliged.
Perhaps there is a spreadsheet template knocking around that can
already do this? A spreadsheet that can consolidate multiple tabs of
equipment and pricing into one sheet with all the equipment
quantified. Am I going about this completely the wrong way?
Thanks for any assistance.
Paul
First post, here goes. I have googled this to no avail, perhaps you
can help?
I have a problem that I am trying to solve, it's hard to explain so I
have produced a video of the problem and put it on YouTube, or perhaps
you are able to follow my text below without the video?
If you search for 'paulonthetube' on YouTube, you will find my video.
I have four tabs in a spreadsheet.
The first tab is a consolidation tab. More on that one later.
Tabs 2-4 are my quotes (1-3) and include items, codes, quantity, unit
cost, total unit cost, gross price and total gross price. Normally I
would also have profit and profit margin as a percentage but have left
these off to keep it simple. Formulas are used to work out totals,
etc.
The consolidation tab features all three of the quotes on tabs 2-4 and
which are 'equalled' or 'linked' (not sure what the correct term is)
to each of the tabs so that changes to the quote 1 tab for example are
repeated on the consolidation tab. Ditto for quote 2 and quote 3.
Trouble is, if I add another row(s) into one of the quote tabs and
copy down the formulas so that I can add more items and costs, this
isn't replicated on the consolidation tab and I have to manually add
in rows and copy down on this tab also.
Is there a way that this can be completely automated so that any rows
I add automatically add themselves in the consolidation tab?
Also when adding a row(s) into a quote tab to add new equipment, etc,
is there a way of having the formulas to the right copy down or appear
automatically?
The reason I need the consolidation tab is so that I can sort all the
items into order to make procurement easier. If you have seen the
video you will see that I sort by column A to put everything in order,
although if I could get each repeated line of equipment to condense
into one line and the quantity adjust automatically, that would be
useful!
If anyone has any ideas on how they can help I'd be much obliged.
Perhaps there is a spreadsheet template knocking around that can
already do this? A spreadsheet that can consolidate multiple tabs of
equipment and pricing into one sheet with all the equipment
quantified. Am I going about this completely the wrong way?
Thanks for any assistance.
Paul