experchange > excel.* > excel.main

Lisa Rudd (01-05-19, 05:23 AM)
Hi all. Lovely to meet you. I LOVE excel and although I'm pretty proficient, I'm moving into new territory lately with a large pricing calculator I've created for work.

I have a calculator that the guys fill out for each quote and I've now added a quoting sheet to the document that updates automatically based on what they enter in their sheet (if that makes sense).

What is happening though is I have blank spaces if they don't need particular items from the preset list. So the 'order form/quote' sheet ends up with big gaps (ie. I've used formulas to make it 'invisible' if quantity <1). Is there any way, I can 'move' the items up so I have no gaps?
I have a screen shot but not sure how to upload it here as an example.

Thanks so much everyone.
Lisa
GS (01-05-19, 03:42 PM)
> Hi all. Lovely to meet you. I LOVE excel and although I'm pretty
> proficient, I'm moving into new territory lately with a large pricing
> calculator I've created for work.
> I have a calculator that the guys fill out for each quote and I've now added
> a quoting sheet to the document that updates automatically based on what they
> enter in their sheet (if that makes sense).


Makes perfect sense to me!
> What is happening though is I have blank spaces if they don't need particular
> items from the preset list. So the 'order form/quote' sheet ends up with big
> gaps (ie. I've used formulas to make it 'invisible' if quantity <1). Is
> there any way, I can 'move' the items up so I have no gaps? I have a screen
> shot but not sure how to upload it here as an example.

Can you post a download link to your file with actual content so we can see
what you're trying to do?
Lisa Rudd (01-05-19, 05:55 PM)
Hi Garry

Thanks for replying.

Will this work for you?


Please ignore the mess of it...it's 1.53am here and I just really quickly deleted any incriminating evidence so to speak LOL.
What is left should give you an idea of the advice I'm after.
Claus Busch (01-05-19, 06:25 PM)
Hi Lisa,

Am Sat, 5 Jan 2019 07:55:12 -0800 (PST) schrieb Lisa Rudd:

> Will this work for you?
>


have a look:


Regards
Claus B.
GS (01-05-19, 08:06 PM)
> Hi Garry
> Thanks for replying.
> Will this work for you?
>
> Please ignore the mess of it...it's 1.53am here and I just really quickly
> deleted any incriminating evidence so to speak LOL. What is left should give
> you an idea of the advice I'm after.


Hmm! How about this approach...

Lisa Rudd (01-09-19, 02:25 PM)
On Sunday, January 6, 2019 at 2:25:05 AM UTC+10, Claus Busch wrote:
> Hi Lisa,
> Am Sat, 5 Jan 2019 07:55:12 -0800 (PST) schrieb Lisa Rudd:
> have a look:
>
> Regards
> Claus B.
> --
> Windows10
> Office 2016


Hi Claus,

Thanks, looks great. But how did you do that? I can't see any formulas.
Lisa
Claus Busch (01-09-19, 04:46 PM)
Hi Lisa,

Am Wed, 9 Jan 2019 04:25:29 -0800 (PST) schrieb Lisa Rudd:

> Thanks, looks great. But how did you do that? I can't see any formulas.


go to OneDrive and select Edit Workbook or download the workbook to see
the formulas.

Regards
Claus B.
Claus Busch (01-09-19, 04:50 PM)
Hi Lisa,

Am Wed, 9 Jan 2019 04:25:29 -0800 (PST) schrieb Lisa Rudd:

> Thanks, looks great. But how did you do that? I can't see any formulas.


in H22:
=IFERROR(INDEX(Calculator!$C$2:$C$39,SMALL(IF(Calc ulator!$D$2:$D$39>0,ROW($1:$38)),ROW(A1))),"")
Insert the formula with CTRL+Shift+Enter and then copy down.
In G22:
=IFERROR(VLOOKUP(H22,Calculator!$C$2:$D$39,2,0),"" )

Regards
Claus B.
Similar Threads